Using a form text field as a parameter in stored procedure

[Guest]

I am setting up an Access Project. I have a form with text fields that I
want to use as parameters in a stored procedure. For example, in the
cretieria field of the stored procedure I have:
='[Forms]!FormName!textfieldName'. I can not get the query to recognize the
text field as the criteria. Thank you

 

[Guest]

Try it this way --
=[Forms]![FormName]![textfieldName]

 

[Guest]

Assuming that you are running the StoredProcedure using a PassThrough query,
the pass through quesry run on the server and not in access and this is why
it doesn't recognize objects in Access.

I handled it that way:
Each SP return a prameter that indicate if the SP accomplished it task or
not, and some SP returns a recordset.

So, I created a PassThrough Query that I assign to it an the appropriate
code to run the SP, and then I open a recordset to check the return values
You need to set the return records property of the query to Yes.

Function RunSP()
Dim db As Dao.DataBase , rs as Dao.RecordSet
Set db = CurrentDb
Application.CurrentDb.QueryDefs("QueryName").SQL= "EXEC SP_Name '" &
Me.FieldNameInForm & "'"
Set rs = db.OpenRecordset("QueryName")
If not rs.Eof then
' Check the return code
End If
End Function

======================
If the field type is number, change the parameter to be without the single
quote:

Application.CurrentDb.QueryDefs("QueryName").SQL= "EXEC SP_Name " &
Me.FieldNameInForm