Too few parameters. Expected 4 (runtime error '3061') 1

[mygmat123]

I get...

"Too few parameters. Expected 4 (runtime error '3061')"

error
when I run my query through VBA using the currectDB.execute mySQL command. But if I run the same exact query through the GUI interface in access it runs fine. Whats is going on?

Thanks you in advance.

 

[Ed2020]

I have come across similar problems in Access 2000.

The only answer I have come across is to use:

DoCmd.SetWarnings False
DoCmd.OpenQuery "QueryName"
DoCmd.SetWarnings True

Not ideal, I know; but it's the only solution I'm aware of.

Ed Metcalfe.

Please do not feed the trolls.....

 

[ClydeDoggie]

The SetWarnings answer is for a different issue. Action queries like update and maketable normally prompt user with different information and Docmd.SetWarnings False stops these prompts. What you are seeing is not really a 'Problem' but just an issue that has to be dealt with when running queries thru code. I bet your query references controls on a form for criteria or as calculated fields - in fact I know it does. These are the parameters that you are being prompted for. Beware these could be in a sub-query to your query you are running. To run a query that has any parameters, even if they refer to an open form, you need to use a querydef and explicitly set the parameters. A quick example:

dim rst as recordset
dim qdf as querydef
dim db as database
set db=currentdb
set qdf = db.querydefs("YourQueryName")
qdf.parameters(1)=Forms!frmMain!txtName
set rst=qdf.openrecordset

It's better code to use the Parameter Name instead of the index, that way if you move fields around in your query it won't break the code.

Let me know if you have any questions on how exactly to implement this, I'll give a more thorough example.

 

[KenReay]

Hi

The "Too few parameters. Expected 4 (runtime error '3061')"
error almost always points to a syntax error in the SQL.

Post the SQL and let us see it



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Ed2020]

This error message sometimes appears when using CurrentDB.Execute, even though there are no syntax errors or form references.

Using DoCmd.OpenQuery is a workaround. The DoCmd.SetWarnings is simply used to turn off the Action Query messages - I never said it was a part of the solution.

Ed Metcalfe.

Please do not feed the trolls.....

 

[ClydeDoggie]

Ed,
No offense intended, it wasn't clear to me that the Docmd.Openquery was the solution. I am interested to know, does this occur randomly among queries or just with specific queries? In other words, will the same query trigger this error sometimes and not others, or once a query generates this error it always will? I never use this particular syntax, always use a recordset variable, so I have never seen it except in the situations I described.

 

[Ed2020]

ClydeDoggie,

No offence taken.

From what I have seen a query will either never generate the error, or always generate the error. It is at least consistantly annoying!

It doesn't seem to have anything to do with the complexity (I have a single table, single field update query that does it), or type of Action query (update, delete, append, etc...).

Perhaps copying and pasting the SQL into a new QueryDef would help - I have seen queries that have worked for years suddenly decide they are "too complex"; pasting the SQL into a new querydef solves this problem.

My guess is that mygmat123 is using Access 2000, which is a horrible, buggy release of Access - even Access 95 had less bugs than 2000 and that's really saying something.

Ed Metcalfe.

Please do not feed the trolls.....

 

[mygmat123]

The following was the solution...

DoCmd.SetWarnings False
DoCmd.OpenQuery "QueryName"
DoCmd.SetWarnings True

I am interested in ClydeDoggie's example, to improve my VB...

dim rst as recordset
dim qdf as querydef
dim db as database
set db=currentdb
set qdf = db.querydefs("YourQueryName")
qdf.parameters(1)=Forms!frmMain!txtName
set rst=qdf.openrecordset

How do you specify which parameter gets which form!txtbox

e.g.

If my query has [Forms!frmMain!txtName] as a parameter and [Forms!frmMain!txtName2] as another parameter, then how would you differentiate between the two. Would you use...

qdf.parameters(1)=Forms!frmMain!txtName
qdf.parameters(2)=Forms!frmMain!txtName2

?

In addition, you stated...

"It's better code to use the Parameter Name instead of the index"

Do you mean I should use....

qdf.parameters(fieldname)=Forms!frmMain!txtName
qdf.parameters(fieldname2)=Forms!frmMain!txtName2
instead of the column number?

Also, how would use the above code assuming you have the following formula in place.....

myfieldformula: field1*field2/Forms!frmMain!txtName

Will the above work if I use

qdf.parameters(myfieldformula)=field1*field2/Forms!frmMain!txtName

And if so how do I index which column it will be.

I now this is a lot, so thank you!


P.S. I'm using Access 2002 from Office XP.

 

[ClydeDoggie]

If my query has [Forms!frmMain!txtName] as a parameter and [Forms!frmMain!txtName2] as another parameter, then how would you differentiate between the two. Would you use...

qdf.parameters(1)=Forms!frmMain!txtName
qdf.parameters(2)=Forms!frmMain!txtName2"

Yes, though I think I made a mistake on the index, I believe it is 0 based (I have to figure it out again each time I use it!)

Regarding the Parameter Name: In the query, instead of putting Forms!frmMain!txtName put [WhatName], then you could use qdf.Parameters("WhatName").

I am a little unclear on the third question. I believe you are saying you have a calculated field in your query that is called myfieldformula and it is defined as field1*field2/Forms!frmMain!txtName. If this is the case you would simply leave as-is if you are using indexes, or you could use this formula and use a parameter name
myfieldformula: field1*field2/[WhatName]

If you are using the index method and you want to know the order the parameters are requested, simply run the query with the form closed and you will be prompted for the parameters in the order they are called.

Let me know if you need more information on any of these answers.

Peter

 

[mygmat123]

THANK YOU!!! You understood my question, and answered well.

I now understand how to use your method. But is there a benefit of using your method over DoCmd.OpenQuery "QueryName"?

Thanks again

 

[Ed2020]

Yes, ClydeDoggie's method would still allow you to execute the query from the CurrentDB object and therefore allow you to utilise Transaction processing. DoCmd.OpenQuery won't allow you to do this.

Ed Metcalfe.

Please do not feed the trolls.....