Too few parameters. (1) expected

Question

I'm using VBA to pass a parameter to a stored query, but keep getting the error. Here is the stored query:

PARAMETERS Parm1 Long;
UPDATE tJ010_SRP4Codes INNER JOIN tZ100_VendorProfiles 
ON tJ010_SRP4Codes.SRP4Code = tZ100_VendorProfiles.JdeSRP4Code 
SET tJ010_SRP4Codes.Vendor = 'TEST'
WHERE ((([Parm1]=tZ100_VendorProfiles.VendorID)));

Here is the VBA that calls the query:

Dim Parm1 As Long
db.QueryDefs(!QueryName).Parameters(Parm1) = intVdrProfileID
db.Execute !QueryName, dbFailOnError

I've tried with and without the "Parameters..." line in the stored query. Same error both ways. The stored query works fine on it's own, just not when called from VBA.

Answer 1

Use a DAO.QueryDef object, supply the parameter value, and execute the QueryDef

Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs(!QueryName)
qdf.Parameters("Parm1").Value = intVdrProfileID
qdf.Execute dbFailOnError

Parameters("Parm1") means the parameter named Parm1. Without quotes there, Parameters(Parm1), Access would interpret Parm1 to be a variable. If you don't have such a variable, that would throw an error.

It's also possible to identify a parameter by number instead of name. The numbering is zero-based, so Parameters(0) means the first parameter.

Note I assumed !QueryName is a recordset value and is the name of a saved query. And I don't know where intVdrProfileID comes from, but assume it's valid for the parameter value.