Microsoft Office Tutorials and References
In Depth Information
Selecting and Filtering with Cascading Combo Boxes
This is because the query used for the second combo box looks like the
SELECT tblName.LastName, Count(tblName.ID) AS CountOfID
FROM tblName GROUP BY tblName.LastName, tblName.FirstName
The variable Forms!frmNameSelect!cmbFirstname1 is a reference to the
cmbFirstName1 from the form frmNameSelect, so a rename destroys the
reference. Because the field is not found, Access thinks it is a parameter and
asks for its value.
Construction of the first set of combo boxes is done by creating the queries in
the Row Source property as follows:
1. After placing the combo boxes on the form, open the properties and
change the Name under the Other tab into cmbFirstName.
2. Now, go to the Data tab and set the cursor in the field after the Row
Source property to get the […] button for designing the query. The
cmbFirstName1 query simply gets the FirstName from tblName.
3. To ensure that no duplicates are returned, add the DISTINCT option.
Do this manually after saving the query or press the Properties button
in the query editor and set the Unique values property to Yes. The
result will look like this:
SELECT DISTINCT tblName.FirstName FROM tblName;
The cmbLastName looks similar, but the reference to the combo box on the
form must be added to filter only the selected value form cmbFirstName.
4. To do this, add the FirstName field and uncheck the checkbox in the
Show row for this field. Now, right-click the criteria cell and select the
5. A form appears. Navigate to the open form and select the
cmbFirstName1 field from the list by double-clicking it. This result in
something like Figure 87 :