Events launching querries (1 Viewer)

[Bruce L]

I am working with Microsoft Access and I would like to launch a Query from a Swithchboard that will take text selected from a drop-down menu (Combo Box) and search 3 fields in a table for data entries matching the selection from the drop-down list. I am aware that List Boxes and Combo Boxes are NOT an option when building a query, but I believe it may be possible to build an “Event” on the form that occurs upon selecting an item from the drop-down list, which in turn copies the text selected and launches the Query that looks for data in the three fields in the given table. So far, I have constructed the Query to seach the three fields. I have also constructed the look-up table and Combo Box that uses the table to make a drop down menu. I have very limited SQL and VB experience and hope to be able to use Access expression builder (or whatever the view is where you see column headers for “Event”, “Action” and “Comment”) or some other easy to use tool for this task. The way I have it working now is awkward in that I have to make a selection from the drop-down list, copy the selection, choose “run querry”, Paste the contents from the clipboad into the text box, and hit “run.” Is what I am attempting possible? Should a macro be used? Any assistance in this area will be greatly appreciated.

 

[arickards]

In your query, use the expression builder. Go to forms, select the form and then the field on the form that will contain the criteria. For each field you will need to do this, also decide if the search criteria needs to be 'And' or 'Or' This will get the text you want to search in the query. Next, create a macro to action openform or report, which ever you are using to display your results. Then for each field use the setvalue action, in the Item section build an expression go to forms , select the field from the form that will hold the text. In the expression section type Null. This will clear the field for the next search.
Then create a command button, assign it to the macro. Remember to set the control in your display form or report to the query.
I know this seems complicated but it works great once you get it done.