How to use parameterized combo boxes in an Access project (281870)

MORE INFORMATION

Creating the Combo Box

This section shows you how to first create a combo box on the Customers form that displays a list of the current customer's orders.

1. Open the Customers form in Design View.
2. In the toolbox, click Control Wizards, and then add a combo box to the form.
3. On the first page of the wizard, select to look up the values in a table or query, and then click Next.
4. On the second page of the wizard, click the Orders table, and then click Next.
5. On the third page of the wizard, add OrderID, CustomerID, and OrderDate as fields in the combo box, and then click Next.
6. On the fourth page of the wizard, click to clear the Hide Key Column, and then shrink the CustomerID column so that it is not visible. Click Next.
7. On the fifth page of the wizard, click Next again to accept OrderID as the field to store or use in your database.
8. On the sixth page of the wizard, select to remember the value for later use, and then click Next.
9. On the last page of the wizard, label the combo box Select Order, and then click Finish.
10. Open the property sheet for the combo box, and then change the name of the combo box to SelectOrderCombo.
11. Set the OnClick property of the combo box to the following event procedure:

    Private Sub SelectOrderCombo_Click()
    On Error GoTo Err_SelectOrderCombo_Click
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "Orders"
    
        stLinkCriteria = "[OrderID]=" & Me![SelectOrderCombo]
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_SelectOrderCombo_Click:
        Exit Sub
    
    Err_SelectOrderCombo_Click:
        MsgBox Err.Description
        Resume Exit_SelectOrderCombo_Click
    
    End Sub
    					

Adding the Code to Refresh the Combo Box

This section shows you the methods that you can use to refresh the combo box. Select one of the following four methods, and then add the appropriate code to the OnEnter event of the combo box.

For Methods 1 and 2, each time that the combo box is selected, the OnClick event resets the row source for the combo box to display only those orders that match the current customer.

NOTE: Because the CustomerID field is a Text field, single quotation marks (') must be concatenated around the Forms reference. For example the WHERE clause generated is converted from:

WHERE CustomerID = '" & Forms![Customers]![CustomerID] & "'"
				

to:

WHERE CustomerID = 'ALFKI'
				

Method 1: Using Ad Hoc Select

Set the OnEnter property of the combo box to the following event procedure:

Me.SelectOrderCombo.RowSource = "SELECT TOP 100 PERCENT OrderID, CustomerID, OrderDate FROM Orders WHERE " _
     & "CustomerID = '" & Forms![Customers]![CustomerID] & "' ORDER BY OrderDate DESC"

				

Method 2: Using a SQL Server View

1. Create a new view in View Designer, and then select the OrderID, the CustomerID, and the OrderDate fields from the Orders table. To sort the list of orders from newest to oldest, set the Sort Type for the OrderDate field to Descending.
2. Save the view as vwCustomerOrders.
3. Set the OnEnter property of the combo box to the following event procedure:

   Me.SelectOrderCombo.RowSource = "SELECT * FROM vwCustomerOrders WHERE CustomerId = '" & Forms![Customers]![CustomerID] & "'"
   					

For Methods 3 and 4, the combo box Requery method is called on the OnEnter event to re-execute a stored procedure or SQL function to return the matching records. In these examples, the Parameter is defined as a control on the form using @ControlName in the criteria. Access automatically evaluates the criteria and requests the matching records.

In the following examples, the combo box returns a list of all orders for the current customer. Because the CustomerID text box contains the current customer ID, you can directly reference the CustomerID text box in the stored procedure or SQL function criteria.

Method 3: Using a SQL Server Stored Procedure

1. Create a new procedure in the graphical designer.
2. Select the Orders table, and then add the OrderID, the CustomerID, and the OrderDate fields to the stored procedure.
3. Enter @CustomerID as the criteria for the CustomerID field, and sort the OrderDate field descending.
4. Save the stored procedure as spCustomerOrders, and then close it.
5. Set the spCustomerOrders stored procedure as the row source for the combo box.
   
   NOTE: The following SQL statement is created and saved by the Stored Procedure Designer:

   CREATE PROCEDURE dbo.spCustomerOrders
     (@CustomerID varchar(5))
     AS SELECT OrderID, CustomerID, OrderDate
     FROM   dbo.Orders
     WHERE  (CustomerID = @CustomerID)
     ORDER BY OrderDate DESC
   					

6. Set the OnEnter property of the combo box to the following event procedure:

   Me.SelectOrderCombo.Requery
   					

Method 4: Using a SQL Server 2000 Function

You can only use this method if the Access project is using SQL Server 2000 or a later version as the data source.

1. Create a function in the graphical designer.
2. Select the Orders table, and then add the OrderID, the CustomerID, and the OrderDate fields to the stored procedure.
3. Enter @CustomerID as the criteria for the CustomerID field, and sort the OrderDate field descending.
4. Save the function as fnCustomerOrders, and then close it.
5. Set the fnCustomerOrders function as the row source for the combo box.
   
   NOTE: The following SQL statement is created and saved by the Stored Procedure Designer:

   CREATE FUNCTION dbo.fnCustomerOrders
     (@CustomerID varchar(5))
     RETURNS TABLE
     RETURN ( SELECT TOP 100 PERCENT OrderID, CustomerID, OrderDate
     FROM   dbo.Orders
     WHERE  (CustomerID = @CustomerID)
     ORDER BY OrderDate DESC )
   					

6. Set the OnEnter property of the combo box to the following event procedure:

   Me.SelectOrderCombo.Requery
   					

To test these methods, open the form, and then select from the Select Order combo box. The OnEnter event requeries the combo box, automatically generating a list of orders for the current customer. When an order is selected, the OnClick event of the combo box is executed, and the Orders form is opened, displaying the selected order.