Access Cookbook

Use Controls as Parameters for the Row Source of Combo and List Boxes in an ADP

Problem

Cascading combo boxes—where the list in the second combo box changes based on the selection in the first—can provide an effective way to limit the number of records returned from SQL Server. You have a series of cascading combo boxes that are based on stored procedures that have parameters. The value that the user selects in the first combo box should determine the contents of the list in the second combo box. How do you pass the parameter values from one combo box to another?

Solution

You can easily use a stored procedure as the row source for a combo box in Access 2002, as long as the stored procedure doesn’t have a parameter. Figure 14-16 shows the properties sheet for the Country combo box on frmCustomer in 14-08.adp that lets a user select from a list of countries.

Figure 14-16. A combo box based on a stored procedure with no parameter

The stored procedure definition simply selects a distinct list of countries from the Customers table in the Northwind database:

CREATE PROC procCountryList
AS
SELECT DISTINCT Country
FROM Customers
ORDER BY Country

However, the Select Customer combo box is based on the procCustomersByCountry stored procedure, which has an input parameter called @Country. It’s designed to filter customers by country, so that a user can pick a country before selecting a single ...