Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Create a Combo Box That Is Based on a Parameterized Stored Procedure

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

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Keywords: KB304252, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 304252
Revision : 2
Created on : 6/28/2004
Published on : 6/28/2004
Exists online : False
Views : 281