Skip to Main Content

 Indiana University


Access: Running a Query Using a Filter Dialog Box in a Form

Parameter queries are quite handy to use when you know what to type when the prompt appears. However, have you thought how great it would be if you could create a parameter query in Access with a drop-down  list  – with  options from which to choose?  A simple form with a drop-down list, such as the one shown here, allows the user to run a query to view equipment records based on the Manufacturer criteria selected from this drop-down list:

Image of Filter Dialog Box
The best way to create a drop-down list  is to create a simple form with a combo box that can be referenced by the query. The form and the query work together to provide the desired dataset.

There are three basic steps that are necessary:

  • Create the form with the combo box(es) that will supply the parameters for the query.
  • Create the query with an expression that will reference the form.
  • Add Command Buttons to the form to run the query and to close the form.

Adding a Combo Box to the Form

Suppose you are tracking equipment inventory, and you would like to view a list of manufacturers and the corresponding equipment along with purchase prices. In a blank form, first you must create a combo box.  It may be easier to use the Combo Box Wizard.

  1. Create and save a new blank form in Design view. (You will have more flexibility in Design view).
  2. In the Toolbox, click Combo Box and click in the form where you want it to appear. (Make sure that the Wizard tool is enabled).
  3. On the Combo Box Wizard dialog box, Click “I want the combo box to look up the values in a table or query.”
  4. Click Next.
  5. Choose the primary table on the “one” side of the relationship that contains the unique field values, which you want to filter. Continue through the Combo Box Wizard choosing the appropriate fields, sort options, width of columns, etc.
  6. When finished, view the form. Make size or label name adjustments as necessary.
  7. You may want to change the following Form properties In the Form property sheet:
      Pop Up = “Yes”
      Border Style = “Dialog”
      Control Box = “No”
      Close Button = “No”
  8. Give the combo box control a meaningful name, which will be used in the query.
  9. Create additional combo boxes for additional filtering, if desired.
  10. Close the form. (In Form view, you can right-click on the form to close it).

Creating the Query to Reference the Form Control

Next you must create a query since the query name will have to be referenced within the form. This query is very similar to creating a parameter query, but you will reference the form control instead.

  1. Make sure that the one-to-many relationships have been defined between tables. In this example, the Manufacturers and Equipment tables are joined by the Manufacturer field.
  2. Create a query using the fields from appropriate tables that will display the required data.
  3. In Design view, you will need to reference the field that has the drop-down list in the form (in this case, it would be the Manufacturer field). However, instead of using bracketed parameters as you would in a parameter query, you will include the name of the form and the name of the combo box control within brackets as follows:

[forms]![FormName]![ComboBoxName]

The query Criteria field in Design view could be structured something like this:

Query referencing form

Depending upon the version of Access you are using, Access may prompt you with the correct names for the form references, which is a new feature in Access 2010.  Also, if you wish to use wildcard characters when selecting criteria in the form, so that you may enter a partial text string with an asterisk, then the line should read:

Like [forms]![FormName]![ComboBoxName] & “*”

Save and close the query.

Adding the  Command Button to Run the Query

The query should now work when you choose an item from the drop-down list in the form; however, the query will have to be opened manually after you choose an item in the drop-down list in the form. Therefore, you will want to add a command button to the form so that the query will run automatically.

  1. Open the form in Design view.
  2. Click the Command Button tool on the Ribbon; then click in the form where you want the command  button to appear.
  3. When the Command Button Wizard opens, choose the Miscellaneous category and the “Run Query” action.
  4. Click Next; choose the query that you created to run when the command button is selected.
  5. Click Next; choose the text or picture for the button.
  6. Click Finish.
  7. Save the form.
  8. Switch to Form view and test the command button.

Note: If you leave the query open when changing options in the drop-down box in the form, you will have to keep refreshing the query to see the updated results.  The keyboard shortcut is F5 to refresh.

Adding the Command Button to Close the Form

Next you will want to add a command button to the form to close the form.

  1. Switch to Design view.
  2. Click the Command Button tool on the Ribbon; then click in the form where you want the command button to appear.
  3. When the Command Button Wizard opens, choose the Form Operations category and the “Close Form” action.
  4. Click Next; choose the text or picture for the button.
  5. Click Finish.
  6. Save the form.
  7. Switch to Form view and test the command button.

13 Responses to "Access: Running a Query Using a Filter Dialog Box in a Form" to “Access: Running a Query Using a Filter Dialog Box in a Form”

  1. JLap Says:

    Thanks for the great tip! Is there a way to keep the resulting query on the same form?

  2. water damage Los Angeles Says:

    Hi,
    This article is very good article because it has many valuable side.

  3. LWenger Says:

    This piece is exactly what I needed. Thanks!
    Like [forms]![FormName]![ComboBoxName] & “*”

  4. Mahinda Says:

    Thank for your advice.It is great

  5. jallison Says:

    Thank you for this great step by step. This helped me so much, much more than the access help topics! Thank you!!

  6. jallison Says:

    Hi there, I am actually having a problem with this now that I can’t seem to find the answer to anywhere. It works fine right after I do it. I save everything. But then when I open the database again, it does not work anymore. I get a box that pops up asking me to enter the parameter value for [forms]![Home]![combobox]. I try renaming the combobox and changing the parameter in my query and it works again, but then when I open my database it starts all over again.

  7. Susan Says:

    Jallison,
    First make sure the name of the combo box matches that in the query. You might also try deleting and re-entering the entire line of criteria in the query and saving. You will get the pop-up box asking for a parameter value unless you open the form first and select an option in the drop-down. Once something has been selected from the drop-down within the form, the query can be opened and should keep displaying the results correctly, based on the drop-down selection in the form.

  8. Bu Xu Says:

    Hi

    I think this is great. [forms]![FormName]![ComboBoxName] works perfectly for me.

    But I have a question what if I want to drag that form into a navigation form. How should I write the reference field.

    Like the above example: [forms]![frmviewEquip]![Cbomanuf] , If I want to drag the “frmviewEquip” form in the navigation form (named frmMain, for example) under navigation control (named equipment), what should I write for the reference field. I have a hard time figuring out this. Any suggestions would be great.

  9. Susan Says:

    Bu Xu,
    This is a good question. I can only get this to work by dragging in the form to the Detail area in Design view which caused the form to be placed into the main control area. Therefore, I had to first remove the navigation subform and the navigation buttons or the form would show on top of any other Access objects under the other buttons. Therefore, I don’t think this would be very helpful. This is something I can keep experimenting with.

    This reference in the query did work, however: [forms]![frmMain]![frmviewEquip]!![Cbomanuf] -assuming that frmMain is the name of the navigation form.

  10. uacar Says:

    Hi, me being quite a newbie in access, would you mind explain me if there’s a way to make query results automatically refresh (instead of hitting f5) subsequent to the changes in user form? (without closing the query). any help is much appreciated. regards.

  11. Mickey Says:

    Hello, I have entered this reference in my query and it works great,,,,,1 time. When I go to choose a different , customer, in my case, nothing is returned on my query. And there are records with the customer’s name that I have choosen. If I completly delete the reference line in the query and re-add it, save, and then close, when I go to my form, enter a customer name from the drop down, and then hit run query, I will get my results. And then I’m left with deleting the reference line again to make it work a second time. Can anyone help??? I have searched and tried everything I know to try…

  12. dilasha Says:

    Hi,
    Thank you so much for this excellent guide but my query doesn’t update each time I select a different item from the combo box. Is there a way I could set up a macro which would update the query and pull it up according to the selection?

  13. Susan Says:

    Uacar & Dilasha,

    One alternative might be to press & drag the query into the form. The query inside the form will update automatically every time you change the form combo box to different criteria. A command button can still be applied to open the query, but I still had to activate the query and press F5 in order to display the different results in the query itself. If you have a source for working with VBA, that might be worth investigating further.

Leave a Reply

 

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.