Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
10 Commandments 

In Memoriam

Terms of Use

VB Petition

Queries: Too few parameters, expected n

Dev Ashish &
Andy Baron

(Q)    Whenever I try to run/execute a query/SQL from code which has a WHERE clause,  I get a runtime errors , "Too Few Parameters. Expected 1."   What's going on??

(A)    Your WHERE clause most probably is referencing a control on a form.  For example,

    strSQL="Select * from tblPeople where PeopleID= forms!SomeForm!PeopleID"

    You have to remember that Jet does not know about Forms/Reports etc.   So it tries to evaluate forms!SomeForm!PeopleID as a parameter instead of looking at the actual control itself.  And your error message is the result.

    The proper way would to concatenate the value returned by the referenced control.  (Note:  If you're not sure how to handle strings and dates in this manner, check out the article titled Use Variables in SQL behind forms)

    strSQL="Select * from People where PeopleID=" & forms!SomeForm!PeopleID

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

1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer