Access Cookbook

Pass Parameters to Stored Procedures from an ADP

Problem

You have a form that is based on a stored procedure. How do you pass parameter values from a combo box to the stored procedure?

Solution

The InputParameters property allows you to pass parameters to the form’s record source. The InputParameters property can be used with stored procedures or with direct Transact-SQL statements. If you use the InputParameters property with a SQL statement, you must formulate the SQL statement with a question mark as the parameter placeholder:

SELECT * FROM MyTable WHERE Price > ?

You then need to set the InputParameters property of the form, specifying the parameter name and data type, and where the value can be obtained. In the case of a SQL statement using question marks, the name you choose for the parameter is not important:

Price money = Forms!frmOrderInputParameter!txtSearch

Here’s how to set up your forms to supply input parameters to stored procedures:

1. The example form in 14-06.adp has a combo box for the user to select a royalty percentage. Set the form’s RecordSource property to the byroyalty stored procedure, as shown in Figure 14-14.

   

   Figure 14-14. Set the form’s RecordSource property to the byroyalty stored procedure

2. Set the InputParameters property to the following expression:

   @percentage int=Forms!frmParameters!cboParameter

3. Type the following code in the combo box’s AfterUpdate event: ...