Maximizing Connectivity & Productivity

Access – Too Few Parameters. Expected 1

From Andy Baron:

When a parameterized query is run in Access, Access provides an expression service that evaluates many parameters. For example, a reference to a control on an open form will be evaluated automatically, and the query containing that parameter will use the value contained in that control. This expression service is not available when you use VBA code to execute a parameterized action query or to open a recordset based on a parameterized query that returns records.

Your code must supply the exact parameter values to be used. This is done through the use of QueryDef objects. QueryDef objects have a Parameters collection containing Parameter objects that have a Name property, a Value property and an index in the collection. These objects and properties are used to supply the required parameter values.

Set db = CurrentDb
Set qdf = db.QueryDefs(“MyQuery”)

‘several syntaxes are possible
qdf(0) = Me!MyControl
qdf.Parameters(1) = 123
qdf![forms!frmParameters!txtNewDate] = #2/2/98#

Set rst = qdf.OpenRecordset(dbOpenSnapshot)
‘or qdf.Execute dbFailOnError

With Access queries that contain paramters that are all references to Access controls on open forms, you can simulate the expression service that Access provides when the queries are run through the user interface, as follows:

Set db = CurrentDb
Set qdf = db.QueryDefs(“MyQuery”)

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
‘ or qdf.Execute dbFailOnError

Leave a Reply

You must be logged in to post a comment.

Mississauga, ON, Canada 905.607.3500