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

 

[Tomino]

Hi all,

I get the same error in my access project as described here. Can somebody help me out please? I found some code to perform an audit trail on my tables but it wasn't requesting for any comment on changes so I tried to adapt the code but it's without success so far.

Thank you very much in advance for your assistance,

Kind regards,
Tom

Here is the code:

[font=Courier New][size=2][color=red][color=red]audReason = InputBox("Give a reason for changes")[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]'Remove any cancelled update still in the tmp table.[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]Set db = DBEngine(0)(0)[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]sSQL = "DELETE FROM " & sAudTmpTable & ";"[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]db.Execute sSQL[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black] [/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]' If this was not a new record, save the old values.[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]If Not bWasNewRecord Then[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser, audReason ) " & _[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, [/color][/color][/size][/font][color=red][color=red]audReason AS Expr4[/color][/color][color=black][color=black], " & sTable & ".* " & _[/color][/color][font=Courier New][size=2][color=black][color=black]"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"[/color][/color][/size][/font][font=Courier New][size=2][color=red][color=red]db.Execute sSQL, dbFailOnError	'(code gives here error 3061; to few paramters, expected 1)[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]End If[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]AuditEditBegin = True[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black] [/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]Exit_AuditEditBegin:[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]Set db = Nothing[/color][/color][/size][/font][font=Courier New][size=2][color=black][color=black]Exit Function[/color][/color][/size][/font]

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