Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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 boxeswhere the list
in the second combo box changes based on the selection in the
firstcan 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:

1. Create the necessary stored procedures for your combo boxes and forms.

2. 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.

3. 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

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.