Access Project: Automatically Pass Parameters to Run Store Procedure Button

Question

In an Access Project (ADP file ONLY) in Access 2007, how do I automatically pass parameters to a stored procedure when the stored procedure is called by clicking a button? The wizard allowed me to link a stored procedure to a button, but clicking the button prompts users for the parameters. I want to pass properties of the current record to the stored procedure and I don't see a way to do it. Maybe I need to wire this up somehow in the properties of the On Click event, but I don't see hwo to do it.

Answer 1

The solution appears to be to modify the On Click event to the following:

DoCmd.RunSQL ("EXEC StoredProcedureName @ParameterName=" & FieldName)

This resolved my issue. This can be simplified to the following:

DoCmd.RunSQL ("EXEC StoredProcedureName " & FieldName)

I also found it was helpful to declare a string and build the SQL string that I wanted to execute separately. So here's what I really used:

Dim strSqlCode As String
strSqlCode = "EXEC StoredProcedureName " & FieldName1 & ", " & FieldName2 & ", " FieldName3
DoCmd.RunSQL (strSqlCode)

Simple and to the point.