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

14.7.1 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?

14.7.2 Solution

You can easily use a stored procedure as the row source for a combo box in Access 2002 or later, 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-07.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 customer to edit. The code for the procCustomersByCountry stored procedure is:

CREATE PROC procCustomersByCountry
  @Country nvarchar(15) 
AS
SELECT CustomerID, CompanyName 
FROM Customers
WHERE Country = @Country
ORDER BY CompanyName

The Select Customer combo box does not get its RowSource property assigned unless a user selects a country first. In the AfterUpdate event of the Country combo box, a SQL string is constructed that executes the stored procedure with the selected parameter:

Private Sub Country_AfterUpdate( )
    Dim strCountry As String
    Dim strSQL As String
    
    strCountry = Me.Country & ""
    strSQL = "EXEC procCustomersByCountry " & strCountry
    If Len(strCountry) > 0 Then
        Me.cboCustomer.RowSource = strSQL
    End If
End Sub

In the AfterUpdate event of the Customer combo box, the form's RecordSource property is then set:

Private Sub cboCustomer_AfterUpdate( )
    Dim strSQL As String
    strSQL = "EXEC procCustomerSelect " & Me.cboCustomer
    Me.RecordSource = strSQL
    If Not Me.Detail.Visible Then
        Me.Detail.Visible = True
        DoCmd.RunCommand acCmdSizeToFitForm
    End If
End Sub

Here is the stored procedure being used for the record source:

CREATE PROC procCustomerSelect
  @CustomerID nchar(5)
AS
SELECT * 
FROM Customers
WHERE CustomerID = @CustomerID
ORDER BY CompanyName

Here's how you can implement this functionality in your forms:

14.7.3 Discussion

Not assigning a row source at design time allows you to dynamically execute a parameterized stored procedure by concatenating the parameter value to an EXECUTE statement. Every time the parameter value changes, you create a new row source for the dependent combo box.

If this seems like a lot of work, there is an easier way that isn't documented in the Access help file. This technique is illustrated in frmSimple in 14-07.adp. You can name the first combo box with the same name as the parameter (without the @ sign). Base the second combo box on the first combo box by using a query with a parameter that has the same name as the first combo box, and requery the second combo box in the AfterUpdate event of the first combo box. Figure 14-17 shows the properties in the second combo box.

Figure 14-17. Setting the properties of the second combo box

The code in the AfterUpdate event of the first combo box is simply to requery the second combo box:

Me.SecondCombo.Requery

In the example shown in 14-07.adp, the form itself is not a bound form. In other words, its record source is assigned at runtime in the AfterUpdate event of the combo box, which selects an individual customer. If you were using this example on a bound form to filter records, you would need to call the code in the AfterUpdate event in the OnCurrent event as well.