ACC: How to Create Synchronized Combo Boxes

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

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

MORE INFORMATION

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 (NWIND.MDB in version 2.0 and 1.x)

2. Create the following new query based on the Categories table, and then save it as qryCategoriesList:

         Query: qryCategoriesList
         -------------------------------------------------------------------
         Field name: CategoryID (Type a space in Category ID in 2.0 and 1.x)
            Show: Yes
         Field name: CategoryName (Type a space in Category Name in 2.0 and
                                   1.x)
            Show: Yes
            Sort: Ascending
   
   

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

   NOTE: In the following sample query, an underscore (_) is used at the end of a line as a line-continuation character. Remove the underscore from the end of the line when re-creating this query.

   In Microsoft Access 7.0 and 97:

         Query: qryProductsList
         -----------------------------------------------
         Field name: ProductID
            Show: Yes
         Field name: ProductName
            Show: Yes
            Sort: Ascending
         Field name: CategoryID
            Show: Yes
            Sort: Ascending
            Criteria: IIF(IsNull([Forms]![frmSelector] _
             ![cboCategorySelect]),[CategoryID],[Forms] _
             ![frmSelector]![cboCategorySelect])
   
      In Microsoft Access 1.x and 2.0:
   
         Query: qryProductsList
         -----------------------------------------------
         Field name: Product ID
            Show: Yes
         Field name: Product Name
            Show: Yes
            Sort: Ascending
         Field name: Category ID
            Show: Yes
            Sort: Ascending
            Criteria: IIF(IsNull([Forms]![frmSelector] _
             ![cboCategorySelect]),[Category ID],[Forms] _
             ![frmSelector]![cboCategorySelect])
   
      Note that using the IIf() and IsNull() functions in this query enables
      you to see a list of all the products if no category is selected.
   
   

4. Create the following new macro, and then save it as ResetProductSelect:

         Macro Name                 Action
         --------------------------------------
         ResetProductSelect         Requery
                                    GoToControl
   
         ResetProductSelect Actions
         ---------------------------------
         Requery
            Control Name: cboProductSelect
         GoToControl:
            Control Name: cboProductSelect
   
   

5. Create the following new form with the combo boxes shown, and then save the form as frmSelector:

         Form: frmSelector
         -----------------------------------
         Combo Box:
            Name: cboCategorySelect
            RowSourceType: Table/Query
            RowSource: qryCategoriesList
            ColumnCount: 2
            ColumnWidths: 0 in;1 in
            BoundColumn: 1
            After Update: ResetProductSelect
         Combo Box:
            Name: cboProductSelect
            RowSourceType: Table/Query
            RowSource: qryProductsList
            ColumnCount: 3
            ColumnWidths: 0 in;1 in; 0 in
            BoundColumn: 1
   
      NOTE: The Name property is the ControlName property in Microsoft Access
      version 1.x.
   
   

6. View the form in Form view. In the cboCategorySelect box, select a category and note that the cboProductSelect box lists only the products for that category.

7. In the cboCategorySelect box, select a different category. Note that the cboProductSelect box is reset and lists the appropriate products for the new category.

REFERENCES

For more information, search the Help Index for "synchronizing combo boxes," or ask the Office 97 Assistant

For more information, search the Help Index for "Requery Action," or ask the Office 97 Assistant.