Error 3061 too few parameters expected 2

[GoodyGoody]

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]

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

Set rs1 = fDAOGenericRst("qryRaceEventTeamResultsIndividual")

 

[MajP]

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

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]

Pretty nifty bit of code DBG!

 

[theDBguy]

Pretty nifty bit of code DBG!

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

 

[vba_php]

well hey welcome back so soon Mr. Table Array! this:

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:

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]

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]

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]

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]

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]

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

 

[vba_php]

TDBG

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