|[ Team LiB ]|
Recipe 14.6 Pass Parameters to Stored Procedures from an ADP
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?
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
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:
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.
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 ]|