ACC2000: How to Create Synchronized Combo Boxes

This article shows you how to create a combo box that is filtered to list only those items that are related to an item that you selected in a previous combo box.

Steps to Reproduce the Behavior

In the following example, the first combo box lists the category names from the Categories table, and the second combo box lists the product names from the Products table. When you select a category name in the first combo box, the second combo box is filtered to list only the product names for that category.

1. Open the sample database Northwind.mdb.
2. Create the following query based on the Categories table, and then save the query as qryCategoriesList.
   
   

   Field:	CategoryID	CategoryName
   Table:	Categories	Categories
   Sort:	�	Ascending
   Show:	<checked>	<checked>
   Criteria:	�	�
   Or:	�	�

3. Create the following query based on the Products table, and then save the query as qryProductsList.
   
   

   Field:	ProductID	ProductName	CategoryID
   Table:	Products	Products	Products
   Sort:	�	Ascending	Ascending
   Show:	<checked>	<checked>	<checked>
   Criteria:	�	�	IIF(IsNull([Forms]![frmSelector]![cboCategorySelect]), [CategoryID],[Forms]![frmSelector]![cboCategorySelect])
   Or:	�	�	�

   
   NOTE: The IIF() function within the criteria tests to see if the first combo box is null. If the combo box is null, the query uses all the CategoryIDs within the Products table as criteria, then returns all Products in the second combo box. If the control is not null, the CategoryID that appears on the form becomes the criteria for the query, and then returns the related Products.
4. Create a new form in Design view that is not based on any table nor any query.
5. From the View menu click Properties.
6. In the Property sheet, click the Event tab, and then click in the On Current check box.
7. Click Build next to the check box, click Code Builder, and then click OK.
8. Enter the following code:

   Me!cboProductSelect.Requery

9. Add two combo boxes as follows and then save the form as frmSelector:

      Combo Box 1:
      -------------------------------
      Name: cboCategorySelect
      RowSourceType: Table/Query
      RowSource: qryCategoriesList
      ColumnCount: 2
      ColumnWidths: 0";1"
      BoundColumn: 1
      
      Combo Box 2:
      -------------------------------
      Name: cboProductSelect
      RowSourceType: Table/Query
      RowSource: qryProductsList
      ColumnCount: 3
      ColumnWidths: 0";1";0"
      BoundColumn: 1
   					

10. Right-click the first combo box that is named cboCategorySelect, and then click Properties.
11. In the Property sheet, click the Event tab, and then click in the After Update box.
12. Click the Build button next to the check box, click Code Builder, and then click OK.
13. Enter the following code:

    Me!cboProductSelect.Requery
    Me!cboProductSelect.SetFocus
    					

14. Open the form in Form view, and then select a category from the first combo box. Note that the second combo box lists only the products that are related to the specific category.NOTE: Each time that you select a different category from the first combo box, the second combo box resets and then lists the appropriate products for the category that you choose.

For more information about combo boxes, click Microsoft Access Help on the Help menu, type combo boxes: what they are and how they work in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about how to requery controls on a form, click Microsoft Access Help on the Help menu, type requery action in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For additional information about how to apply this concept to Access 2000 projects, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about how to apply this concept to earlier versions of Access, click the article number below to view the article in the Microsoft Knowledge Base: