Access Cookbook

Limit the Items in One Combo Box Based on the Selected Item in Another

Problem

Sometimes in a form-based parameter query it would be nice to limit the values in one combo box based on the value selected in another combo box. For example, if a form has two combo boxes, one for the type of music and the other for artists, when you select the type of music in the first combo box, you’d like the list of artists in the second combo box to be limited to artists of the selected music type. But no matter which type of music you select, you always see all the artists in the second combo box. Is there any way to link the two combo boxes so you can filter the second combo box based on the selected item in the first?

Solution

When you place two combo boxes on a form, Access by default doesn’t link them together. But you can link them by basing the second combo box on a parameter query whose criteria point to the value of the first combo box. This solution demonstrates how you can use a parameter query tied to one combo box on a form as the row source for a second combo box to limit the second combo box’s drop-down list to items appropriate to the user’s selection in the first combo box.

Follow these steps to create linked combo boxes:

1. Create a form bound to a table or query. Make it a continuous form by setting the DefaultView property of the form to Continuous Forms. This will be used as a subform, like fsubAlbumBrowse in the frmAlbumBrowse example.

2. Create a second form with two unbound combo ...