Business Solutions: Access: Putting Search Criteria On a Form
      By James D. Duncan, CPA  September 2006

Last month we created a query that we could search for items in a file. That is only a quick answer. The real answer is to build a form for the user to use to search for what they want. To keep the user corralled in a controlled environment. The only seemly hard part is how to get the search information from the form to the query.
First, let’s build the form. We will put a text box on the form for the user to input the text string to search the field in the file for. See the screen below and I have a form with a text box named txtInput. The caption on the label is “Please Enter Text to Search For:”



Ok, we have a form named frmTextSearch with a text box named txtInput. Now, open the query we build last month. It was named qryNameWildCardSearch. The one below is that same query just renamed qryNameWildCardSearchOrg. The reason was so I would have the original to look back at if I messed up. If you have something that works, keep it and work with a copy.

All we are going to do is replace the “[Characters you want]” in the query with the field name on the form we are calling the query from. Just key in the proper syntax and you are finished. I never find it that easy. Always a little something that does not work.

The replacement string is “[form]![frmTxtSearch].[txtInput]”. Thus the criteria looks like Like "*" & [Forms]![FrmTextSearch]![txtInput] & "*". Half the time I cannot remember that it is forms with an s. I always want to use the word form. Well, don’t worry, Access will build the string for you. If you have anything in the criteria cell clear it and we will use the build (wand with stars under it) icon to let Access help us. Click on the build icon and we get this screen.

Click on Like, double click on Forms, double click on Loaded Forms, double click on FrmTextSearch and single click on txtInput. If you double click on txtInput it will load the expression into the expression builder window. Your screen should resemble the next screen.

Now click on paste and the expression builder will put what you have built into the window.
 

You can click on the OK button and it will put that code into the query criteria. Now add the “*” & after the Like and the & “*” to the end for the criteria that now reads Like "*" & [Forms]![FrmTextSearch]![txtInput] & "*". We could have put that in the expression builder as we went along but we know we wanted that just needed the syntax for the text field on the form. Save the query and we will put a command button on the form to call this query and display the results.

Back on the form, use the tools and add a command button. From the tool box paint a command button. Then select Miscellaneous, Run Query and press the Next button.

Then select the query name and click on Next.

Then I usually select Text and leave the default “Run Query” text. You can enter what you wish the command button to say. And then click on the Next button.



On this last screen I give the command a proper name. I entered “cmdRunQuery”



Now we are back on the form. Save it, enter something in the text box and press the Run Query button.
 

With a little luck you will get results from the query in the form of a list in datasheet view.
 

And that is all there is to calling a query from a form. Maybe next time we will use the query and the form to call a report. If anybody cares, please let me know.

Now for the commercial, folks it is just the right thing to do! It is United Way fund drive time. Participate in helping the community in some way. Give some of your precious money or time or effort to help those who struggle every day. I know you will not see what your efforts do sometimes. But, trust me, your efforts are noticed somewhere. The only real reason is the pay it forward principle. If you seen the movie or read the book you know of what I speak. Me and Yoda believe!


 

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James