ACC2000: How to Create a Combo Box That Is Based on a Parameterized Stored Procedure

View products that this article applies to.

This article was previously published under Q304252

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

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

Expand all | Collapse all

SUMMARY

In a Microsoft Access project (ADP), you can use a parameterized stored procedure as the row source of a combo box on a form. This article gives you an example of how to pass a parameter to the stored procedure that you are using as the row source.

MORE INFORMATION

Open the sample project NorthwindCS.adp.
Click Stored Procedures under Objects, and then click New.

In the Design window of the new stored procedure, replace the existing text with the following Transact-SQL:

Create Procedure MyProcedure
@text1 varchar(15)
AS
SELECT CustomerID, CompanyName
FROM Customers
WHERE city = @text1
ORDER BY CompanyName

Save the stored procedure.
Click Forms under Objects, click New, and then click OK to create a new, unbound form.
Add a combo box to the new form, and then set the following properties:
Name: cboCompany
ColumnCount: 2
ColumnWidths: 0";1.5"
BoundColumn: 1
Add a text box to the form, and name it txtParam.

Add the following code to the AfterUpdate event of the txtParam text box:

'The following line will pass the value in the text box to the stored procedure.
Me!cboCompany.RowSource = "EXEC MyProcedure '" & Me!txtParam.Value & "'"
Me!cboCompany.Requery

Save the form, and then open the form in Form view.
Type London in the text box.
Press the TAB key to move to the combo box.

Note that only customers from London appear in the list.