Too few parameters error.

[Mark Wipf]

Can anyone tell me why I get a Run time error of 'Too few
parameters. 1 expected.' when I try to open a query as a
recordset. The query references a field on a form. When
I manually open the query, it works as expected... try and
open it in code... Too few parameters. What gives.

Many thanks in advance! You guys are awesome.

 

[JSand42737]

Can anyone tell me why I get a Run time error of 'Too few
parameters. 1 expected.' when I try to open a query as a
recordset. The query references a field on a form. When
I manually open the query, it works as expected... try and
open it in code... Too few parameters. What gives.

Many thanks in advance! You guys are awesome.

Mark

The SQL of the query probably looks like this:

SELECT * FROM tblName WHERE Field1=Forms!frmMain!Field1;

When this is used in VBA code, you need to concatenate the value from the form
to the SQL statement, so that it gets interpreted:

Dim strSQL As String
strSQL="SELECT * FROM tblName WHERE Field1=" & Forms!frmMain!Field1

In the example above, I have assumed that Field1 is numeric. If it was text,
then you would need to wrap in single quotes:

Dim strSQL As String
strSQL="SELECT * FROM tblName WHERE Field1=' " & Forms!frmMain!Field1 & " ' "

 

[Marshall Barton]

Can anyone tell me why I get a Run time error of 'Too few
parameters. 1 expected.' when I try to open a query as a
recordset. The query references a field on a form. When
I manually open the query, it works as expected... try and
open it in code... Too few parameters. What gives.

When you open a query from an Access feature, such as the
QBE window or using DoCmd.OpenQuery, Access takes care of
the parameters for you. The VBA environment doesn't know
about about parameters, so you have to take of them
yourself. For opening a recordset, this is done by using
the query's QueryDef object to open the recordset (see Help
for details). The general idea is along these lines:

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
Dim rs As Recordset
Set db = CurrentDb()
Set qdf = db.QueryDefs("nameofquery")
For Each prm In qdf.Parameters
Set prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
. . .
rs.Close : Set rs = Nothing
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing