ACC: How to Create Synchronized Combo Boxes (98660)



The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97

This article was previously published under Q98660
Novice: Requires knowledge of the user interface on single-user computers.

SUMMARY

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.

Modification Type:MajorLast Reviewed:5/9/2003
Keywords:kbhowto KB98660