|
(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")
qdf(0) = Me!MyControl
qdf.Parameters(1) = 123
qdf![forms!frmParameters!txtNewDate] = #2/2/98#
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
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)
|
