Create cascading combo boxes and list boxes in Access

Use cascading controls when the value selected in one combo box or list box determines the choices in another. For example, selecting a product category can limit a product list to products in that category.

The Link Master Fields and Link Child Fields properties let Access filter a dependent combo box or list box automatically. You don't need to create an After Update event procedure or macro just to requery the dependent control.

Understand master and child fields

Set the linking properties on the dependent combo box or list box.

Property	Description
Link Master Fields	The field or control on the form that supplies the value used to filter the dependent control.
Link Child Fields	The field in the dependent control's row source that Access compares with the master value.

When the master value changes, Access refreshes the dependent control and displays only rows with matching child-field values.

Before you create the link, make sure that:

• The master control returns the value that the child field contains. For example, a category combo box should return CategoryID when the child field is also CategoryID.
• The child field is included in the dependent control's Row Source. The child field can be hidden in the list, but it must be part of the row source.
• The master and child fields use compatible data types.

For more information about creating list controls and setting their row sources, see Add a list box or combo box.

Filter products by category

Suppose an order form contains these combo boxes:

• cboCategory, where the user selects a product category. Its value is the category's CategoryID.
• cboProduct, where the user selects a product.

To filter the products by the selected category:

1. Open the form in Design View.

2. Select cboProduct, and then press F4 to open the property sheet.

3. On the Data tab, set Row Source Type to Table/Query.

4. Set Row Source to a query or SQL statement that includes the field used to link the controls. For example:

   SELECT ProductID, ProductName, CategoryID
   FROM Products
   ORDER BY ProductName;
   

5. For this example, set Column Count to 3, Bound Column to 1, and Column Widths to 0";2";0". These settings make the combo box return ProductID, display ProductName, and hide CategoryID.

6. Set the linking properties on cboProduct.

   Property	Value
   Link Master Fields	cboCategory
   Link Child Fields	CategoryID

7. Save the form, and then switch to Form View.

8. Select a category and open cboProduct. The product list shows only products whose CategoryID matches the value of cboCategory.

Access can use the hidden CategoryID field to filter the rows because the field remains in the row source.

Use cascading list boxes

The Link Master Fields and Link Child Fields properties work the same way with list boxes. Set the properties on the dependent list box and make sure its row source includes the child field.

You can use linked combo boxes and list boxes for scenarios such as:

• Departments, teams, and employees
• Customers and orders
• Projects and tasks
• Product categories and products

Link more than one field

If records are related by more than one field, enter comma-separated names in both linking properties. Access matches the fields by their position in each list.

For example:

Property	Value
Link Master Fields	CompanyID, LocationID
Link Child Fields	CompanyID, LocationID

Both lists must contain the same number of entries. Each master field must have a compatible data type with the child field in the corresponding position.

Use cascading controls on continuous forms

Linked combo boxes and list boxes also work on continuous forms. Access filters the dependent control for each visible record by using that record's master value. You don't need event code to requery each row independently.

Troubleshoot cascading controls

Issue	What to check
The linking properties don't appear on the Data tab.	Make sure Access is updated to Version 2609 (Build 20430.20092) or later.
The dependent control is empty.	Confirm that the master control has a value, matching records exist, the child field is included in the row source, and the master and child values use compatible data types.
The dependent control shows unexpected choices.	Confirm that the linking properties are set on the dependent control and that the master control returns the key value stored in the child field.
A multi-field link doesn't work.	Make sure both properties contain the same number of fields, in matching order, with compatible data types.

Existing cascading controls that use VBA or macros continue to work. Use the linking properties as a declarative alternative when you only need to filter a control from values available on the form.

Update Access

To check for a newer Current Channel build, select File > Account > Update Options > Update Now. After you install the latest build, the feature might still take additional time to appear as the rollout expands.