ACC2000: How to Create Synchronized Combo Boxes (209576)

MORE INFORMATION

Steps to Reproduce the Behavior

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.