Creating a Drop-Down Parameter in Access 2010

Posted by Cindi Kline  /  April 15, 2015  /  Access, Microsoft 365   —   45 Comments ↓

Parameter queries in Access 2010 are a great way to choose what you wish to see as your query result each time you run the query. One of the downsides to parameters is not being able to easily create a drop-down list from which users may choose what they wish to see. However, there is a way to accomplish this, with a little additional work. 

First, you will want to create a table or a query that has a list of the values you want to use in the drop-down list. If the field you wish to use is already in a table (i.e. the Company Name may be in the Customer table), you will want to create a query that only has the Company Name. In our case, we have a Company Name field in our Customers table, so we will create a query to extract that field.

1. On the Create tab, click Query Design in the Queries group.

Query Design




2. From the Show Table dialog box, add the table that has the field you wish to use as your lookup field.

Show Table View









3. From the table field list, double-click the field you wish to use in your drop-down list to add the field to your query grid.

4. Sort the field in Ascending order.

5. If there are duplicates in the field, click the Totals button on the Query Tools/Design tab to group the field. This will eliminate any duplicates.





6. The resulting query design should look something like this:

Query Design View










7. Save and close the query.

Start a Free Trial - 30 days, free, no risk

The next step is to create a new form. This will be the parameter form that will have the drop-down list. The goal of this form will be to have users choose a value from the drop-down list (i.e. Company Name) which will automatically run a query with the record results (i.e. that company’s orders).

1. On the Create tab, click Form Design in the Forms group.

Form Design




2. In the form design, go to the Controls group on the design ribbon and click off the Control Wizard. We’ll be creating our drop-down list manually.

Control Wizards





3. Create a title for your form by using the Label tool in the Controls group.

4. Next, in the Controls group, click on the Combo Box control, and click under your form title.

Combo Box




5. If you wish, you can type a label for the drop-down. Size and/or move the controls as desired.

Form Design View







6. Open the Property task pane for the Unbound control (right-click on the unbound control and choose Properties from the short-cut menu).

7. Go to the All tab and give the control a name (i.e. CompanyName, or cboCompany if you are using a naming standard).






8. Click the Data property tab and click the drop-down for the Row Source. Choose the table or the query you created that has the list you will be using in your drop-down. You may also want to set the Limit to List to Yes.

Data Row Source






9. On the Property task pane, choose Form from the top drop-down list at the top to adjust the form properties and turn this into a dialog box. Go to the All tab.

a. Type a descriptive title for the Caption. This will show up on the form’s tab in Form View.
b. Set Auto Center to Yes.
c. Set Record Selectors to No.
d. Set Navigation Buttons to No.
e. Set Dividing Lines to No.
f. Set Scrollbars to Neither.
g. Set Border Style to Dialog.
h. Set Control Box to No.

10. Save the form (i.e. frmCompanyOrders).

11. Close the form for the time being.

You might also like...

Excel training log template Advanced Excel training Interactive Access Form
Did You Know That Excel Has a Built-In Training Log Template? Advanced Microsoft Excel Training That Fits Your Busy Schedule Create an Interactive Access Form to Filter a Query by Date Range

The next step will be to create the query that has the parameter criteria.

1. Create a query in Design View, using whatever fields you wish to show in the query result. Save the query (i.e. qryCompanyParameter). The fields can come from multiple tables, as long as the tables are related. For example, you may choose Company Name, Contact, and State from the Customer table and you may choose Order Date and Product Name from the Orders/Products table(s). You must include the lookup field so that you can insert the necessary parameter on the criteria line in the QBE grid.








2. Right click on the criteria line of the field you will be using in the lookup (i.e. Company Name).

3. Choose Build to open the Expression Builder.

4. In the lower part of the expression builder, in the first column, expand the database, then expand Forms, and then expand All Forms.

Expression Elements






5. You should see the form you created for the lookup (i.e. frmCompanyOrders). Click once on that form in the first column.

6. You should see the controls you created, including the lookup field (i.e. CompanyName or cboCompany), in the middle column.

Expression Categories




7. Double-click the lookup field to insert it into the Expression box at the top.

Expression Builder

8. Click OK.

9. Save and close the query.

The final step will be to attach an event procedure to the parameter form so that when a user chooses from the drop-down list, the query results will appear and the parameter form will close.

1. Go into the design of the form created above (i.e. frmCompanyOrders).
2. Right click the drop-down unbound control and choose Properties.
3. On the Event tab, click next to After Update. Choose the Build button […] and click Code Builder. This will open the Visual Basic Editor.

4. Some code is already in place. Keep the existing code and add the following (using your own lookup field, parameter query, and parameter form names). In the example, I’ve bolded the names which you should replace with your own names. Your final code should look something like:

Private Sub cboCompany_AfterUpdate()

DoCmd.OpenQuery “qryCompanyParameter”, acViewNormal, acEdit

DoCmd.Close acForm, “frmCompanyOrders

End Sub

5. Close Visual Basic with the X in the upper-right corner (it will save automatically).

6. Save the parameter form.

Choosing Virtual Training That Meets Your Needs - Get FREE EBOOK

Now it’s time to test the Parameter form.

1. Go into Form View for the parameter form.

2. Click the drop-down arrow and choose a value from the list.

Company Orders









3. This should show you the query datasheet with the results.

We hope this tutorial for creating a drop-down parameter in Microsoft Access was helpful. KnowledgeWave offers online classes, on-demand learning content and coaching or consulting for your specific Access or other Microsoft Office needs. Contact us today at 800-831-8449 or learn more here.

Topics: Access, Microsoft 365