Error 3061 too few parameters expected 2 (1 Viewer)

GoodyGoody · Jan 1, 2020

Hi, I'm getting the above error on the second line in my VBA code below. Can anyone explain what I am doing wrong please? I added the first line as a test just to see if the query works from within VBA and it does work fine. It seems to be suggesting you cannot run a query using the db.openrecordset which has parameters drawn from a form even if the form is open. Is that correct?

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
set db = CurrentDb()
DoCmd.OpenQuery "qryRaceEventTeamResultsIndividual"
Set rs1 = db.OpenRecordset("qryRaceEventTeamResultsIndividual")

theDBguy · Jan 1, 2020

I suggest you try using Leigh's Generic Recordset. For example:

Code:

Set rs1 = fDAOGenericRst("qryRaceEventTeamResultsIndividual")

MajP · Jan 1, 2020

You also can resolve it in the code instead of having paramater queries.

Code:

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
dim strSql as string

set db = CurrentDb()
strSql = "Select * from somequery where someField = " & forms("someForm").somecontrol
'strSql = "Select * from somequery where someField = '" & forms("someForm").somecontrol & "'" for text
Set rs1 = db.OpenRecordset(strSql)

NauticalGent · Jan 1, 2020

Pretty nifty bit of code DBG!

theDBguy · Jan 1, 2020

NauticalGent said:
Pretty nifty bit of code DBG!

Thanks, but it's not mine. Thanks to Leigh Purvis (from UK). Cheers!

vba_php · Jan 1, 2020

well hey welcome back so soon Mr. Table Array!

this:

Code:

DoCmd.OpenQuery "qryRaceEventTeamResultsIndividual"

will most likely throw the error you're talking about if there is a parameter argument in the query itself, because you can't include parameters in a method like that when run in code (I don't think, anyway). this:

Code:

Set rs1 = db.OpenRecordset("qryRaceEventTeamResultsIndividual")

might be up in the air in terms of whether it will work or not if the form that holds the parameter values is actually open. I would guess it still won't work because running code is such a disconnect operation when compared to going through the same motions using the GUI interface of access. did you test this scenario?

GoodyGoody · Jan 2, 2020

Thanks DBGuy and especially Leigh Purvis. I'm such a nerd I would like to know why it doesn't work with db.openrecordset but it does with docmd.openquery but another time! Yes, MajP, you can do it in code but then you have to potentially repeat the code wherever you use that query and that creates the potential for bugs. And vba_php, this is the last bit of the the MVP race timing database I am writing and the most challenging (for me) in terms of logic, coding etc. and I just decided I had to bite the bullet. (still haven't quite sorted the logic so that it is generic enough to be usable but not so much that it will takes months to code. Without you guys it would be near impossible as I am doing this in my spare time from a very busy day job so thanks a lot everyone for all comments.

theDBguy · Jan 2, 2020

GoodyGoody said:
Thanks DBGuy and especially Leigh Purvis. I'm such a nerd I would like to know why it doesn't work with db.openrecordset but it does with docmd.openquery but another time! Yes, MajP, you can do it in code but then you have to potentially repeat the code wherever you use that query and that creates the potential for bugs. And vba_php, this is the last bit of the the MVP race timing database I am writing and the most challenging (for me) in terms of logic, coding etc. and I just decided I had to bite the bullet. (still haven't quite sorted the logic so that it is generic enough to be usable but not so much that it will takes months to code. Without you guys it would be near impossible as I am doing this in my spare time from a very busy day job so thanks a lot everyone for all comments.

Hi. Glad to hear you're all sorted for now. Good luck with your project.

MajP · Jan 2, 2020

Code:

Yes, MajP, you can do it in code but then you have to potentially repeat the code wherever you use that query and that creates the potential for bugs.

IMO putting parameters that reference form controls in a query is the biggest way to create bad code. I would never put a form reference in a query, but people do it all the time leading to seriously over complicated solutions. Hard to write, hard to debug, hard to error check.

vba_php · Jan 2, 2020

theDBguy said:
Hi. Glad to hear you're all sorted for now. Good luck with your project.

you always issue a follow up when they say thanks, guy. I dont do that very often and i dont think many others do either. Thats quite nice of you!

The_Doc_Man · Jan 3, 2020

What have I told you before, Adam? Politeness is the lubrication in the gearbox of human communication. And TDBG has that.

vba_php · Jan 3, 2020

The_Doc_Man said:
TDBG

= the db guy? i think i got an old timer's joke finally! (or, acronym, if u will)

Error 3061 too few parameters expected 2 (1 Viewer)

GoodyGoody

Registered User.

theDBguy

I’m here to help

MajP

You've got your good things, and you've got mine.

NauticalGent

Ignore List Poster Boy

theDBguy

I’m here to help

vba_php

Forum Troll

GoodyGoody

Registered User.

theDBguy

I’m here to help

MajP

You've got your good things, and you've got mine.

vba_php

Forum Troll

The_Doc_Man

Immoderate Moderator

vba_php

Forum Troll

Similar threads

Users who are viewing this thread