Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

ACC: How to Synchronize Two Combo Boxes on a Form


View products that this article applies to.

Summary

This article shows you how to synchronize two combo boxes so that when you make a selection in the first combo box, the selection limits the choices in the second combo box.

NOTE: This article explains a technique demonstrated in the sample files, FrmSampl.exe (for Microsoft Access for Windows 95 version 7.0) and FrmSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

150895 ACC95: Microsoft Access Sample Forms Available in Download Center

175066 ACC97: Microsoft Access 97 Sample Forms Available in Download Center

↑ Back to the top


More information

The following example uses the sample database Northwind.mdb (NWIND.MDB in earlier versions). The first combo box lists the available product categories, and the second combo box lists the available products for the category selected in the first combo box:
  1. Open the sample database Northwind.mdb (or NWIND.MDB in 1.x and 2.0).
  2. Create the following new query based on the Products table, and then save the query as Category Combo Query:
          Query: Category Combo Query
          ---------------------------------------------------------
          Table: Products
          Type: Select Query
          Field: ProductID (or Product ID in 1.x and 2.0)
             Sort: Ascending
             Show: Yes
          Field: ProductName (or Product Name in 1.x and 2.0)
             Table: Products
             Show: Yes
          Field: CategoryID (or Category ID in 1.x and 2.0)
             Show: No
             Criteria: Forms![frmCategoriesProducts]![Categories]
    						
  3. Create a new form not based on any table or query with the following combo boxes, and save the form as frmCategoriesProducts.
          Combo Box 1
          -------------------------------
          Name:          Categories
          RowSourceType: Table/Query
          RowSource:     Categories
          ColumnCount:   2
          ColumnWidths:  0;1
          BoundColumn:   1
          AfterUpdate:  Refresh Products
    
          <B>NOTE</B>: The Name property is called the ControlName property in
          Microsoft Access 1.x.
    
          Combo Box 2
          -----------------------------------
          Name:          Products
          RowSourceType: Table/Query
          RowSource:     Category Combo Query
          ColumnCount:   2
          ColumnWidth:   0;1
          BoundColumn:   1
    						

    NOTE: The BoundColumn property of the first combo box should not be set to the field named in the Criteria row of the above query; otherwise, the second combo box displays only the first record.
  4. Create the following new macro and save it as Refresh Products:
          Macro Name          Actions
          ---------------------------
          Refresh Products    Requery
    
          Action Arguments
          ----------------
          Control Name: Products
    						
  5. View the frmCategoriesProducts form in Form view. When you select a category in the first combo box, the second combo box is updated to list only the available products for the selected category.

Notes

In the above example, the second combo box is filled with the results of the Category Combo Query query. This query finds all the products that have a CategoryID that matches the category selected in the first combo box.

Whenever a category is selected in the first combo box, the AfterUpdate property runs the Refresh Products macro, which forces the second combo box to run the Category Combo Query query again. This refreshes the list of available products in the second combo box. Without this macro, you would have to force the second combo box to refresh itself by pressing the F9 key.

Important: If the first combo box is bound, you should also assign the Refresh Products Macro to the On Current event of the form.

↑ Back to the top


References

For more information about synchronizing combo boxes, search for "synchronize," and then "synchronizing combo boxes" using the Microsoft Access Help Index.

↑ Back to the top


Properties

Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

↑ Back to the top


Keywords: kbhowto, KB97624

↑ Back to the top

Article Info
Article ID : 97624
Revision : 3
Created on : 1/18/2007
Published on : 1/18/2007
Exists online : False
Views : 267