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


The information in this article applies to:

  • Microsoft Access 2000
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).

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

  1. Open the sample project NorthwindCS.adp.
  2. Click Stored Procedures under Objects, and then click New.
  3. 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
  4. Save the stored procedure.
  5. Click Forms under Objects, click New, and then click OK to create a new, unbound form.
  6. Add a combo box to the new form, and then set the following properties:

    Name: cboCompany
    ColumnCount: 2
    ColumnWidths: 0";1.5"
    BoundColumn: 1

  7. Add a text box to the form, and name it txtParam.
  8. 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
  9. Save the form, and then open the form in Form view.
  10. Type London in the text box.
  11. Press the TAB key to move to the combo box.

    Note that only customers from London appear in the list.
Modification Type:MajorLast Reviewed:6/28/2004
Keywords:kbhowto kbinfo KB304252
©2004 Microsoft Corporation. All rights reserved.