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?
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.
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:
Create the necessary stored procedures for your combo boxes and forms.
For the first combo box based on a stored procedure that is not parameterized, simply assign the name of the stored procedure to the row source.
In the OnEnter or the OnGotFocus event of the second combo box, pick up the value from the first combo box and concatenate it to execute the stored procedure on which the second combo box is based:
Me.cboCustomer.RowSource = "EXEC MyProc " & Me.FirstComboBox
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.
The code in the AfterUpdate event of the first combo box is simply to requery the second combo box:
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.