Solved: Search box

[David627]

Hi all:

Looking to make a form that will act as a search box. What my user needs is the ability to enter any one of 5 criteria and come back with the correct record/info.

I picture this being a form with 5 unbound text boxes (for the 5 criteria) and an unbound list box for the results along with a SEARCH and CLEAR button.

How would the code look? Is there any sample code anyone can provde?

Thanks in advance.

David

[OBP]

David, see this as an Example of a fairly complex Search Form and Query.
http://forums.techguy.org/business-a...ntities-3.html

see post #33

[CreganTur]

You can also use the DLookup function to search a table for specified information via the WHERE condition. It only pulls a single record, so it's best to either have a complex WHERE condition to pull records based on multiple criteria, or search on the primary key, which your Users may not know.

There are a lot of different ways to do this, but this should get you started.

[David627]

Randy:

I only want one record so I will give it a try... thanks

I really am looking to keep all the info to a form as it won't need to be printed as ODPs example shows.

David

[David627]

I tried to find code samples/examples for a DLookup function with multiple WHERE conditions.

Can anyone assist with syntax?

Thanks,
David

[David627]

Another thought - could I add a combo box that the user could choose what criteria they will be entering into the unbound textbox? Then the DLookup will search what is choosen... like:

=DLookup("unboundtextcriteria","TableName","comboboxchoice")

[OBP]

I am not sure that Dlookup is the best choice for what you are trying to do.
If you want everything contained on one form then a Main/Subform setup where you set the Filter of the Subform is far more effiecient.
You can also use the VBA Find to do something very similar, but it is less reliable.
The attached database by Allen Browne is a very good Form Filter method.

[OBP]

This is an example of using the VBA Find function to do the searching, but as I sadi it is less reliable. I may have another version somewhere, that is slightly better.

[David627]

I like the Search 2000 version - how can I make the details section a list that comes up ONLY with search results? The way tis person has it I will potentially see thousands of records...

Thanks
David

[OBP]

If you look at the VBA code for the Command button with the Filter sign on it you will see that each of the "filter" fields on the top section is added to the overall Form's filter. If you only have one filter field/combo then that makes the VBA cod equite simple.