Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 14.6 Pass Parameters to Stored Procedures from an ADP

14.6.1 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?

14.6.2 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:

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

Set the InputParameters property to the following expression:
```
@percentage int=Forms!frmParameters!cboParameter
```
Type the following code in the combo box's AfterUpdate event:
```
Me.Requery
```
Run the form. The form opens, but no data is displayed because no value has been specified for the @percentage parameter and there are no records with a blank percentage. Select an item from the combo box and the form will be requeried, picking up the value from the combo box and reexecuting the stored procedure.

Although this technique eventually works, it's not an ideal solution because it involves a wasted round trip to the server the first time the form opens. The stored procedure executes with a null value in the place of a valid parameter value that would return records. No error is returned—there simply weren't any matching records.

A more efficient solution is to write code that sets the record source only when a royalty percentage has been selected:

Open the form in design view and delete the form's RecordSource property setting (byroyalty) and the InputParameters property setting.

Delete the existing code in the AfterUpdate event of the combo box and replace it with the following:

Private Sub cboParameter_AfterUpdate( )
    Me.RecordSource = "EXEC byroyalty " & Me.cboParameter.Value
    
    ' Run this code only the first time the combo box
    ' is requeried.
    If Me.txtAuID.Visible = False Then
        Me.txtAuID.Visible = True
        DoCmd.RunCommand acCmdSizeToFitForm
    End If
End Sub

Test the form by opening it in form view. The byroyalty stored procedure will be executed only when the user selects an item from the combo box.

14.6.3 Discussion

The form opens in unbound mode, with no record source set. The text box that displays the au_id value is hidden. When the AfterUpdate event of the combo box occurs, the form is automatically requeried:

Me.RecordSource = "EXEC byroyalty " & Me.cboParameter.Value

Then the text box is unhidden so that the result set can be displayed:

If Me.txtAuID.Visible = False Then
    Me.txtAuID.Visible = True
    DoCmd.RunCommand acCmdSizeToFitForm
End If

Figure 14-15 shows the form with all of the records displayed.

Figure 14-15. A form that passes a value to a stored procedure

Although you could leave the form with the original property settings specifying the stored procedure name in the RecordSource property and the parameter value in the InputParameters property on the properties sheet, it wastes a round trip across the network in a request for records that will always fail.

Whenever you are creating applications against server data, it is a good idea to minimize your use of network and server resources as much as possible. You will then be able to support a larger number of users and provide better performance.

[ Team LiB ]