Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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:

   Me.Requery

4. 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
returnedthere 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:

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

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

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