Search using "LIKE" and choosing one record from multiple matches

[NKK]

I would like to create a search form to allow users to enter search criteria
on first and last names (perhaps some other fields later) using a "LIKE" type
of criteria. I am envisioning that this search will eventually bring back
multiple rows. I would like to set up the form so that the user can select a
particular row from the matches and then open up a "detail" form with the
data from that particular record. I am thinking that my main form will be
unbound and then pass the search parameters to a query and have a subform to
display the results. So how do I choose a record from the results and open
another form with that record displayed? Thanks in advance.

 

[Jeff Boyce]

One approach might be to have a listbox display the "multiple rows" found
from the LIKE search.

Then you could use the Click event in the listbox (click on the one to get
details for) to indicate a specific record. You wouldn't even need to leave
the form ... you could set the recordsource for the form itself to the
selected listbox item.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[NKK]

But wouldn't i be limited to the fields that were in the listbox in the "new"
form?

 

[Jeff Boyce]

Nope!

Here's the generic explanation...

Build a form and base it on a query.

In that query, have it look in the listbox on the form to get the correct
record. Make sure to include all the fields in this query that you'll want
to see on the form.

On the form, build your SearchFor/Like routine and controls, and add the
listbox to display those found.

When the form first opens, nothing is in the listbox, and nothing is
selected in the listbox, so the query that feeds the form brings up the
matching (i.e., none) record.

After you do the search, fill the listbox, and select the listbox record you
want to see (?double-click?), the double-click event for the listbox
requeries the form (i.e., Me.Requery). Now the query has a record to find
when it runs, so the form "loads up" with the selected record.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP