Indiana University: IT Training Tips Blog

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:

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:

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.

This entry was posted on Wednesday, December 15th, 2010 at 1:47 pm and is filed under Access, Business & Productivity, Databases, Microsoft. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.