Run-time error 3061 - Too few parameters. Expected 1

[Marin]

Hi all,

I am getting this run-time error when I try to execute SQL
statement from VB code in Access 2000. Here is my code"

Dim sCqlStr As String
Dim myDb As DAO.Database
Dim myRs As DAO.Recordset

Set myDb = CurrentDb

cSqlStr = "SELECT Unit FROM q_NotImp2Imp;"

Set myRs = myDb.OpenRecordset(cSqlStr)

This is where it failed.

I can run this query ("Select Unit From q_NotImp2Imp") in
Access SQL query window and it works just fine.

Please help!

Regards, Marin

 

[Ken Snell]

Assuming that q_NotImp2Imp is a query, I am guessing that it contains a
parameter. You need to resolve that parameter in your SQL routine when you
call it via code. And you can do that by using the SQL statement of the
query itself and then evaluating the parameter before you call the query.

 

[Marin]

You are partialy right, q_NotImp2Imp is query, but it does
not need any parameters to run, it just select records
based on where statement (value from forms field, not
passed by parameter). As I sad, this query, without any
modification, returns proper dataset when I run it in
query window.

 

[John Spencer (MVP)]

If you are getting a value from a control on a form, then that is what is
causing the parameter message to pop up.

 

[Geoff]

As you are getting the error message "Too few
parameters. Expected 1", you must put a
PARAMETERS clause in the SQL string in order
to feed the criteria into the WHERE clause.

As it stands, it seems your SQL string is calling data
from a query that requires criteria. So you'll need to
create that first query in code using an SQL string with
a Parameters clause. Then base your second SQL
on the query you've created in code.

However, it might be better to create just one SQL string
that does everything you want, calling data directly from
the tables and resolving the criteria (parameters) at the
same time.

An example of a parameter query:

cstrSQL = "PARAMETERS " & _
"[ID] Long; " & _
"SELECT " & _
"[Field1], " & _
"[Field2] " & _
"FROM [TableName] " & _
"WHERE " & _
"(((TableName.ID) = [ID])) " & _
"ORDER BY TableName.Lastname, " & _
"TableName.Firstname;"

Note:

(a) If you were designing the above SQL in the query
design grid, [ID] is what you'd put in the criterion row
to prompt the user to enter a parameter. But when you
switch to SQL view, the PARAMETERS clause won't
be there.

(b) Be careful with semicolons, commas and spaces
in SQL string.

Then create a temporary query based on the SQL
string:

Set qdf = db.CreateQuerydef("", cstrSQL)

Then you can set the criteria (parameters):

qdf.parameters![ID] = Me.txtID

Good luck with your project.
Geoff