Microsoft KB Archive/281870

= How to use parameterized combo boxes in an Access project =

Article ID: 281870

Article Last Modified on 8/11/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q281870



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).



SUMMARY
Combo boxes provide a good way of presenting a list of choices to a user. If the row source for a combo box needs to be based upon a subset of records from a table or query, you can add a WHERE clause to limit the choices.

The example in this article adds a combo box to the Customers form in the NorthwindCS.adp sample project. The combo box lists the orders that are placed by the current customer. When it is selected, the Orders form opens, showing the details of the selected order.

You can use four different methods to produce a list of matching orders that are placed by a customer. These methods use an Access project (.adp) with Microsoft SQL Server 2000 as the data source. These methods are described in the next section.



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.  Open the Customers form in Design View. In the toolbox, click Control Wizards, and then add a combo box to the form. On the first page of the wizard, select to look up the values in a table or query, and then click Next. On the second page of the wizard, click the Orders table, and then click Next. On the third page of the wizard, add OrderID, CustomerID, and OrderDate as fields in the combo box, and then click Next. 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.</li> On the fifth page of the wizard, click Next again to accept OrderID as the field to store or use in your database.</li> On the sixth page of the wizard, select to remember the value for later use, and then click Next.</li> On the last page of the wizard, label the combo box Select Order, and then click Finish.</li> Open the property sheet for the combo box, and then change the name of the combo box to SelectOrderCombo.</li>  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 = &quot;Orders&quot;

stLinkCriteria = &quot;[OrderID]=&quot; & Me![SelectOrderCombo] DoCmd.OpenForm stDocName,, , stLinkCriteria

Exit_SelectOrderCombo_Click: Exit Sub

Err_SelectOrderCombo_Click: MsgBox Err.Description Resume Exit_SelectOrderCombo_Click

End Sub </li></ol>

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 = '&quot; & Forms![Customers]![CustomerID] & &quot;'&quot; 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 = &quot;SELECT TOP 100 PERCENT OrderID, CustomerID, OrderDate FROM Orders WHERE &quot; _ & &quot;CustomerID = '&quot; & Forms![Customers]![CustomerID] & &quot;' ORDER BY OrderDate DESC&quot;

Method 2: Using a SQL Server View
<ol> 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.</li> Save the view as vwCustomerOrders.</li>  Set the OnEnter property of the combo box to the following event procedure: Me.SelectOrderCombo.RowSource = &quot;SELECT * FROM vwCustomerOrders WHERE CustomerId = '&quot; & Forms![Customers]![CustomerID] & &quot;'&quot; </li></ol>

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
<ol> Create a new procedure in the graphical designer.</li> Select the Orders table, and then add the OrderID, the CustomerID, and the OrderDate fields to the stored procedure.</li> Enter @CustomerID as the criteria for the CustomerID field, and sort the OrderDate field descending.</li> Save the stored procedure as spCustomerOrders, and then close it.</li>  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 </li>  Set the OnEnter property of the combo box to the following event procedure: Me.SelectOrderCombo.Requery </li></ol>

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. <ol> Create a function in the graphical designer.</li> Select the Orders table, and then add the OrderID, the CustomerID, and the OrderDate fields to the stored procedure.</li> <li>Enter @CustomerID as the criteria for the CustomerID field, and sort the OrderDate field descending.</li> <li>Save the function as fnCustomerOrders, and then close it.</li> <li> 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 ) </li> <li> Set the OnEnter property of the combo box to the following event procedure: Me.SelectOrderCombo.Requery </li></ol>

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.

Additional query words: OfficeKBHowTo inf

Keywords: kbhowto kbprogramming kbclientserver kbadp KB281870

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.