Search for record and goto record

[singing4phun]

I need a way to search through a database to find a specific record where the
search criteria is a field in a table (form). I know how to use the macros,
find next, previous, new, etc., but this is not sufficient for my needs.

First I want to know if the record exists by searching the databsae for a
data such as purchase order number (text).
Then if the PO is in the databse, I want the record to be displayed on the
form.

Do I use the "GoToRecord" option and if so, how do I build the expression?
If not, then what should I use?

 

[John W. Vinson]

First I want to know if the record exists by searching the databsae for a
data such as purchase order number (text).
Then if the PO is in the databse, I want the record to be displayed on the
form.

Do I use the "GoToRecord" option and if so, how do I build the expression?
If not, then what should I use?

No code is needed at all... well, you don't need to write any code.

Use the form design toolbox; turn on the "magic wand" icon; and use the Combo
Box tool to add a new combo box to the form. Choose the option "Use this combo
box to find a record".

If the PO doesn't exist it won't be shown in the combo; if it does, it'll be
found.

 

[singing4phun]

Thank you. However, I do need it to be a button on my form that does the
search adn then brings up the entire record on the form.
The form is not in a sheet view and the "combo box" displays as a drop down
and then does not bring up the entire record populating all other fields.

The user needsd to be able to "type" in the PO they are looking for withint
the database, not select it from a drop down.

Is this possible?

 

[John W. Vinson]

Thank you. However, I do need it to be a button on my form that does the
search adn then brings up the entire record on the form.
The form is not in a sheet view and the "combo box" displays as a drop down
and then does not bring up the entire record populating all other fields.

The user needsd to be able to "type" in the PO they are looking for withint
the database, not select it from a drop down.

Is this possible?

Sure. No button is required, unless you want one!

Let's say you have an unbound textbox named txtFindPO, and that the Form is
based on a query returning all PO's. You could have code like the following in
either txtPO's AfterUpdate event, or in the Click event of a Find button:

Me.Filter = "[PONumber] = '" & Me!txtFindPO & "'"
Me.FilterOn = True

 

[singing4phun]

Ok, this is more what I'm being asked to create. however, shouldn't the code
request a pup-up where you can enter the PO number?

When i created the button and embedded the code below, it did nothing.
What's missing in the code to make it ask for the criteria to search for?

Thank you. However, I do need it to be a button on my form that does the
search adn then brings up the entire record on the form.
The form is not in a sheet view and the "combo box" displays as a drop down
and then does not bring up the entire record populating all other fields.

The user needsd to be able to "type" in the PO they are looking for withint
the database, not select it from a drop down.

Is this possible?

Sure. No button is required, unless you want one!

Let's say you have an unbound textbox named txtFindPO, and that the Form is
based on a query returning all PO's. You could have code like the following in
either txtPO's AfterUpdate event, or in the Click event of a Find button:

Me.Filter = "[PONumber] = '" & Me!txtFindPO & "'"
Me.FilterOn = True

 

[David W. Fenton]

Let's say you have an unbound textbox named txtFindPO, and that
the Form is based on a query returning all PO's. You could have
code like the following in either txtPO's AfterUpdate event, or in
the Click event of a Find button:

Me.Filter = "[PONumber] = '" & Me!txtFindPO & "'"
Me.FilterOn = True

One thing that I do to trick people who haven't figure out to hit
ENTER to fire the lookup is to put a command button with a FIND
caption and put no event behind it. They type in the text box and
hit the command button, which causes the textbox to update, and it's
code to fire. They never know the difference.

 

[John W. Vinson]

Ok, this is more what I'm being asked to create. however, shouldn't the code
request a pup-up where you can enter the PO number?

No, it's reading the PO number from an existing textbox on an existing form -
which is what you asked for.

When i created the button and embedded the code below, it did nothing.
What's missing in the code to make it ask for the criteria to search for?

I don't know. What's the Record Source of the form? Does it have an unbound
textbox named txtPONumber? Could you post the complete code for the button?

 

[John W. Vinson]

One thing that I do to trick people who haven't figure out to hit
ENTER to fire the lookup is to put a command button with a FIND
caption and put no event behind it. They type in the text box and
hit the command button, which causes the textbox to update, and it's
code to fire. They never know the difference.

<SNORK!>

Very clever... so the filter or find code is just in the textbox's afterupdate
event. Nice.

 

[David W. Fenton]

<SNORK!>

Very clever... so the filter or find code is just in the textbox's
afterupdate event. Nice.

The alternative would be to put the code in the command button's
OnClick event, but that would require checking the textbox for
content, or using the TextBox's AfterUpdate event to enable/disable
the command button. With my approach, it's all much simpler, and the
user gets a UI that is discoverable -- they never need to know
they've been tricked into clicking a button that does absolutely
nothing.

The only situation that would be problematic would be if there was
text in the textbox and they'd already left it, and then came back
and clicked the command button. But I always clear the find textbox
in the AfterUpdate event so that couldn't possibly happen.