selecting a single record from a form

[Mike M.]

I want to use a form to display a single record from an underlying query
using input
specified by the user. I thought there must be some simple way to pass the
user's input to the query as criteria to select the record to be displayed
in the form, but I can't seem to figure it out. I have some basic coding
skills (enough to be dangerous).

Is there a simple solution? I also want to be able to go to another
(different) record after the first one has been displayed, etc.

Mike

 

[Guest]

You do not say how you are getting the user input. You also do not indicate
what that data is or what type it is. I can only speculate about these things.

I hope that I can assume that the item being provided by the user is data
that has a field on your form. Assuming that the recordset being returned by
the query has the record that contains the value being provided by the user,
you simply need to set focus to the field (control on your form) that
contains the value in the record you want to display. Then find that value in
that field.

The code would be something lilke:

'set the focus
Me.NameOfControl.SetFocus
DoCmd.FindRecord ValueFromUser

This is a very short version of all of the code that you would really need
to complete the entire process of moving to the record, but this is the
essential part.

 

[Mike M.]

Here's more info Mr. B. -

My form is bound to "ArtId_to_TagNo" query which returns fields Title,
TagNo, Price, Artist_LastName, etc. I want to view a specific record from
this query based on the Tag no that the user inputs into a textbox. I want
to insure also that the user cannot change the data in any of underlying
tables through one of the bound controls.

In your earlier response, you mentioned setting the focus. Would the focus
be the textbox control where the user inputs the tagno?
Where would this code go? I assume that I would need a find button to
execute the "DoCmd.FindRecord ValueFromUser" part of your response. And
ValueFromUser comes from where?

Thanks again - Mike

 

[Guest]

Mike,

You need an unbound text box for the user to type in their "TagNo" value.

You can then place code in the "AfterUpdate" event of your form that will
perform the Find to locate the actual record.

Just as a suggestion, you might consider using a combo box that would
display the valid list of TabNo's. This way you would know that the value
selected would be a valid value in your table.

In that case the code would go in the "AfterUpdate" event of the combo box.

You did not indicate what type of field the "TagNo" field is . Is it text or
a number?

Mr B

 

[Mike M.]

Mr. B. -

Yes, I have the textbox on the form for input - figured that out.
Also, I just found that the "Locked" property will protect my tables.

The combo box would be hard to use I think because there could be thousands
of items in the query, and the users might have to scroll clear to the end
to find a tag with a high number. So if I don't use the combo box as you
suggest, and instead use the text box, what event will trigger the find?

TagNo is a number field.

Mike

 

[Guest]

The "AfterUpdate" event of the textbox where the user is to enter the TagNo
value.

In design mode of your form, select the Textbox that is to be used to enter
the value you what to locate.

Right click over this control to bring up the Properties dialog box.

Click on the Event tab and locate the "After Update" event row.

Click the down arrow at the right end of the line and select "[Event
Proceedure]".

Then click the button to the right of the down arrow, the one with the three
dots. This will cause the VBA code window to be displayed. You should be in
the "After Update" event of your control. You should be seeing something
like:

Private Sub TheNameOfYourControl_AfterUpdate()

End Sub

(Where "TheNameOfYourControl" is you would see the actual name of your
control.)

In the Blank space between these two lines is where the code will go.

Paste the following code:

'*****Start of Code

Dim strTagNo as String

strTagNo = me.txtNameOfYourTextbox
Me.txtNameOfTagNoControl.SetFocus
DoCmd.FindRecord strTagNo
me.txtNameOfYourTextbox.Value = Null

'*****End of Code

Where the "txtNameOfYourTextbox" is found above, you will substitute the
actual name of your text box where the user is to enter the TagNo to be
located

Where the "txtNameOfTagNoControl" is found above, you will substitute the
actual name of your text box where the control source is the TagNo field.

Try your form by entering a valid TagNo in the "Find" text box and press the
Enter key or the Tab key.

The code should place the cursor in the field where the TagNo field is and
should fine the record for that TagNo.

 

[Mike M.]

My form works great - thank you for all your help. Now I have to figure out
the main form on which this will be a subform.

Mike

The "AfterUpdate" event of the textbox where the user is to enter the
TagNo
value.

In design mode of your form, select the Textbox that is to be used to
enter
the value you what to locate.

Right click over this control to bring up the Properties dialog box.

Click on the Event tab and locate the "After Update" event row.

Click the down arrow at the right end of the line and select "[Event
Proceedure]".

Then click the button to the right of the down arrow, the one with the
three
dots. This will cause the VBA code window to be displayed. You should be
in
the "After Update" event of your control. You should be seeing something
like:

Private Sub TheNameOfYourControl_AfterUpdate()

End Sub

(Where "TheNameOfYourControl" is you would see the actual name of your
control.)

In the Blank space between these two lines is where the code will go.

Paste the following code:

'*****Start of Code

Dim strTagNo as String

strTagNo = me.txtNameOfYourTextbox
Me.txtNameOfTagNoControl.SetFocus
DoCmd.FindRecord strTagNo
me.txtNameOfYourTextbox.Value = Null

'*****End of Code

Where the "txtNameOfYourTextbox" is found above, you will substitute the
actual name of your text box where the user is to enter the TagNo to be
located

Where the "txtNameOfTagNoControl" is found above, you will substitute the
actual name of your text box where the control source is the TagNo field.

Try your form by entering a valid TagNo in the "Find" text box and press
the
Enter key or the Tab key.

The code should place the cursor in the field where the TagNo field is and
should fine the record for that TagNo.

--
HTH

Mr B

Mr. B. -

Yes, I have the textbox on the form for input - figured that out.
Also, I just found that the "Locked" property will protect my tables.

The combo box would be hard to use I think because there could be
thousands
of items in the query, and the users might have to scroll clear to the
end
to find a tag with a high number. So if I don't use the combo box as you
suggest, and instead use the text box, what event will trigger the find?

TagNo is a number field.

Mike

 

[Guest]

Mike,

If the form that you have been trying to get to move to a specific record is
going to be a subform, you do not need to locate a specific record in it.
You will simply link it to the primary form by one or more fields and it will
display the record(s) relative to the current record in the primary form.

--
HTH

Mr B

My form works great - thank you for all your help. Now I have to figure out
the main form on which this will be a subform.

Mike

The "AfterUpdate" event of the textbox where the user is to enter the
TagNo
value.

In design mode of your form, select the Textbox that is to be used to
enter
the value you what to locate.

Right click over this control to bring up the Properties dialog box.

Click on the Event tab and locate the "After Update" event row.

Click the down arrow at the right end of the line and select "[Event
Proceedure]".

Then click the button to the right of the down arrow, the one with the
three
dots. This will cause the VBA code window to be displayed. You should be
in
the "After Update" event of your control. You should be seeing something
like:

Private Sub TheNameOfYourControl_AfterUpdate()

End Sub

(Where "TheNameOfYourControl" is you would see the actual name of your
control.)

In the Blank space between these two lines is where the code will go.

Paste the following code:

'*****Start of Code

Dim strTagNo as String

strTagNo = me.txtNameOfYourTextbox
Me.txtNameOfTagNoControl.SetFocus
DoCmd.FindRecord strTagNo
me.txtNameOfYourTextbox.Value = Null

'*****End of Code

Where the "txtNameOfYourTextbox" is found above, you will substitute the
actual name of your text box where the user is to enter the TagNo to be
located

Where the "txtNameOfTagNoControl" is found above, you will substitute the
actual name of your text box where the control source is the TagNo field.

Try your form by entering a valid TagNo in the "Find" text box and press
the
Enter key or the Tab key.

The code should place the cursor in the field where the TagNo field is and
should fine the record for that TagNo.

--
HTH

Mr B

Mr. B. -

Yes, I have the textbox on the form for input - figured that out.
Also, I just found that the "Locked" property will protect my tables.

The combo box would be hard to use I think because there could be
thousands
of items in the query, and the users might have to scroll clear to the
end
to find a tag with a high number. So if I don't use the combo box as you
suggest, and instead use the text box, what event will trigger the find?

TagNo is a number field.

Mike


Mike,

You need an unbound text box for the user to type in their "TagNo"
value.

You can then place code in the "AfterUpdate" event of your form that
will
perform the Find to locate the actual record.

Just as a suggestion, you might consider using a combo box that would
display the valid list of TabNo's. This way you would know that the
value
selected would be a valid value in your table.

In that case the code would go in the "AfterUpdate" event of the combo
box.

You did not indicate what type of field the "TagNo" field is . Is it
text
or
a number?

Mr B


:

Here's more info Mr. B. -

My form is bound to "ArtId_to_TagNo" query which returns fields
Title,
TagNo, Price, Artist_LastName, etc. I want to view a specific
record
from
this query based on the Tag no that the user inputs into a textbox. I
want
to insure also that the user cannot change the data in any of
underlying
tables through one of the bound controls.

In your earlier response, you mentioned setting the focus. Would the
focus
be the textbox control where the user inputs the tagno?
Where would this code go? I assume that I would need a find button
to
execute the "DoCmd.FindRecord ValueFromUser" part of your response.
And
ValueFromUser comes from where?

Thanks again - Mike

You do not say how you are getting the user input. You also do not
indicate
what that data is or what type it is. I can only speculate about
these
things.

I hope that I can assume that the item being provided by the user is
data
that has a field on your form. Assuming that the recordset being
returned
by
the query has the record that contains the value being provided by
the
user,
you simply need to set focus to the field (control on your form)
that
contains the value in the record you want to display. Then find that
value
in
that field.

The code would be something lilke:

'set the focus
Me.NameOfControl.SetFocus
DoCmd.FindRecord ValueFromUser

This is a very short version of all of the code that you would
really
need
to complete the entire process of moving to the record, but this is
the
essential part.

--
HTH

Mr B


:

I want to use a form to display a single record from an underlying
query
using input
specified by the user. I thought there must be some simple way to
pass
the
user's input to the query as criteria to select the record to be
displayed
in the form, but I can't seem to figure it out. I have some basic
coding
skills (enough to be dangerous).

Is there a simple solution? I also want to be able to go to
another
(different) record after the first one has been displayed, etc.

Mike

 

[Mike M.]

Mr. B -

Maybe I should explain what I'm trying to do.
Here's what I have:

Tables:

Member - MemberId (key), LastName, FirstName, Address, Email, etc.

Works - ArtId (key), Title, Medium, etc

Shows - ShowId (key), ShowName, StartDate, EndDate, ShowTheme, etc.

WorksShows - join table ArtId (key), ShowId (key), Price, TagNo

Sales - SaleId (key), Date, Check? Cash?, Charge?, ReceiptNo, BuyerName,
BuyerAddress, etc.

SalesDetail - join table SaleId (key), ArtId (key), Price

Media - MediaId (key), MediaName, Mediatype, etc.

MemberMedia - join table MemberId (key), MediaId (key)



I want to build a sales form that allows the user to bring up an item by
TagNo and verify the description, Price etc. and then to add that item to
the sale if desired. After adding the item, I want the form or subform to
display the subtotal of the sale thus far and ask if another item will be
added. If "no" I want the form to display the sale total and close out the
sale by populating the "buyers" info into the sales table and the details
into the SalesDetail table.

Thank You - Mike

Mike,

If the form that you have been trying to get to move to a specific record
is
going to be a subform, you do not need to locate a specific record in it.
You will simply link it to the primary form by one or more fields and it
will
display the record(s) relative to the current record in the primary form.

--
HTH

Mr B

My form works great - thank you for all your help. Now I have to figure
out
the main form on which this will be a subform.

Mike

The "AfterUpdate" event of the textbox where the user is to enter the
TagNo
value.

In design mode of your form, select the Textbox that is to be used to
enter
the value you what to locate.

Right click over this control to bring up the Properties dialog box.

Click on the Event tab and locate the "After Update" event row.

Click the down arrow at the right end of the line and select "[Event
Proceedure]".

Then click the button to the right of the down arrow, the one with the
three
dots. This will cause the VBA code window to be displayed. You should
be
in
the "After Update" event of your control. You should be seeing
something
like:

Private Sub TheNameOfYourControl_AfterUpdate()

End Sub

(Where "TheNameOfYourControl" is you would see the actual name of your
control.)

In the Blank space between these two lines is where the code will go.

Paste the following code:

'*****Start of Code

Dim strTagNo as String

strTagNo = me.txtNameOfYourTextbox
Me.txtNameOfTagNoControl.SetFocus
DoCmd.FindRecord strTagNo
me.txtNameOfYourTextbox.Value = Null

'*****End of Code

Where the "txtNameOfYourTextbox" is found above, you will substitute
the
actual name of your text box where the user is to enter the TagNo to be
located

Where the "txtNameOfTagNoControl" is found above, you will substitute
the
actual name of your text box where the control source is the TagNo
field.

Try your form by entering a valid TagNo in the "Find" text box and
press
the
Enter key or the Tab key.

The code should place the cursor in the field where the TagNo field is
and
should fine the record for that TagNo.

--
HTH

Mr B


:

Mr. B. -

Yes, I have the textbox on the form for input - figured that out.
Also, I just found that the "Locked" property will protect my tables.

The combo box would be hard to use I think because there could be
thousands
of items in the query, and the users might have to scroll clear to the
end
to find a tag with a high number. So if I don't use the combo box as
you
suggest, and instead use the text box, what event will trigger the
find?

TagNo is a number field.

Mike


Mike,

You need an unbound text box for the user to type in their "TagNo"
value.

You can then place code in the "AfterUpdate" event of your form that
will
perform the Find to locate the actual record.

Just as a suggestion, you might consider using a combo box that
would
display the valid list of TabNo's. This way you would know that the
value
selected would be a valid value in your table.

In that case the code would go in the "AfterUpdate" event of the
combo
box.

You did not indicate what type of field the "TagNo" field is . Is it
text
or
a number?

Mr B


:

Here's more info Mr. B. -

My form is bound to "ArtId_to_TagNo" query which returns fields
Title,
TagNo, Price, Artist_LastName, etc. I want to view a specific
record
from
this query based on the Tag no that the user inputs into a textbox.
I
want
to insure also that the user cannot change the data in any of
underlying
tables through one of the bound controls.

In your earlier response, you mentioned setting the focus. Would
the
focus
be the textbox control where the user inputs the tagno?
Where would this code go? I assume that I would need a find
button
to
execute the "DoCmd.FindRecord ValueFromUser" part of your response.
And
ValueFromUser comes from where?

Thanks again - Mike

You do not say how you are getting the user input. You also do
not
indicate
what that data is or what type it is. I can only speculate about
these
things.

I hope that I can assume that the item being provided by the user
is
data
that has a field on your form. Assuming that the recordset being
returned
by
the query has the record that contains the value being provided
by
the
user,
you simply need to set focus to the field (control on your form)
that
contains the value in the record you want to display. Then find
that
value
in
that field.

The code would be something lilke:

'set the focus
Me.NameOfControl.SetFocus
DoCmd.FindRecord ValueFromUser

This is a very short version of all of the code that you would
really
need
to complete the entire process of moving to the record, but this
is
the
essential part.

--
HTH

Mr B


:

I want to use a form to display a single record from an
underlying
query
using input
specified by the user. I thought there must be some simple way
to
pass
the
user's input to the query as criteria to select the record to be
displayed
in the form, but I can't seem to figure it out. I have some
basic
coding
skills (enough to be dangerous).

Is there a simple solution? I also want to be able to go to
another
(different) record after the first one has been displayed, etc.

Mike