Creating A Search Button

[connordrew]

Hi,

I would like to have a search field on a form that looks for data within a table.

For example, I have field A and field b within the table. When someone searches for data within field a, both field a and field b data etc would be displayed.

Any help would be greatly appreciated.

 

[RichO]

First you need to design a query based on the table. In the criteria for the first field, enter:

Like "*" & [Forms]![MyForm]![txtSearchCriteria] & "*"

Replace "MyForm" with whatever the name of your form is.
Then in the next field, under the Or criteria enter the same thing.

Save the query (MyQuery... see below)

In your form, add a text box control, and give it the name txtSearchCriteria.

Then in the text box After Update event:

DoCmd.OpenQuery "MyQuery"

This will display all records containing the text you enter - it looks at both fields. If you want it to look at only field A for search criteria, remove the Or criteria in field B of the query.

 

[jxd914psu]

With this code. How do you make the data open in a pre-made form?

 

[rhett7660]

Could you base the form off the query you are using to do the search in.

 

[RichO]

Yes, just use the above-mentioned query as the record source for your pre-made form and then open the form when the search button is clicked.

 

[jxd914psu]

Thank you all!

 

[jxd914psu]

I set the form source to the query mentioned above. Works great. However, I have two different search boxes for two separate criteria. (I will probably have to use two different queries) Is there a way to get around this.

Thanks Again

 

[Bob Miller]

I set the form source to the query mentioned above. Works great. However, I have two different search boxes for two separate criteria. (I will probably have to use two different queries) Is there a way to get around this.

Thanks Again

No need for two queries. Put similar code in the other field that you want to search. The *'s will take care of you not entering anything in one or the other search field.

 

[RichO]

Just make sure you step down to the next row when adding criteria for an additional field (the one labeled Or:)

That way it will return the query results if field 1 matches criteria 1 OR field 2 matches criteria 2. If you put them on the same row, both criteria would have to be met for the query to return any records.