Passing Parameters into query recordset

[hillz2k]

Howdy folks,

I hope someone can help me out of this little problem.

I have a query that uses parameters from a form as its criteria. What I have to do is send action emails. I have the email generate set up and works fine but what i am trying to do is create a DAO.recordset (i know i should be using ADO but im used to DAO). I have the recordset set up:

Set rsaction = db.OpenRecordset(vabQuery, dbOpenDynaset)

(vab is a varibale set up to contain the name of the query as this form runs and generates emails using two different buttons)

When i click the button to run the code i get the error message:

run-time error '3061'
too few parameters. expected 5

these 5 parameters that the query uses in criterias are text boxes on the the form the buttons are on........how do i send the parameters in a recordset.....is it using the 'querydef' function and if so how do i use it for sending more than one parameter?

Thanx for taking the time to read this.....if you need more info then ill be more than happy to supply.....if you can solve me problem then heres me thanx in advance.


hillz

 

[dkinley]

Since you are calling a predefined query, embed the criteria into the query itself.

For the query, in the criteria column of the data use ...

[Forms]![FormName]![ControlName]

To reference the text boxes on the form to pass the criteria. When the query executes, it will refer to those text boxes for data.

Note: If cannot control the integrity of the data input on these text boxes you can alter the criteria expression to something like ...

Like [Forms]![fPrintSUB001]![cmbOrg]) & "*".

For wildcard searches.

-dK

 

[pbaldy]

How about

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

 

[hillz2k]

dk,

thats what i have in the criteria of the query. If i open the query normally it shows the data i require and works perfectly.....but when i try to open it as a recordset it knows it requires parameters but it doesnt use the ones embedded into the query it wants them redefined (i think thats what its getting at).

its like i have to create a where clause in the vb that contains the embedded paramenter from the query?? Am i making any sence??

 

[dkinley]

Oh okay ... since you are not doing anything to the data ... instead of opening as dbOpenDynaset try ...

Set rsaction = db.OpenRecordset(vabQuery, dbOpenSnapshot)


-dK

 

[hillz2k]

pbaldy,

thanx for that - i think ive got it defining the querydefs, but if i use:

Set rsaction = db.OpenRecordset(vabQuery, dbOpenDynaset)

after i 'set' the querydefs i still get the same error message.

If i enter debug mode and hover over the querydefs the yellow help box appears showing the correct information so i think i have defined them correctly, but im guessing the line above doesnt state to use the querydefs when opening the query.....any help there??

 

[dkinley]

Does your query name have any spaces in it?

-dK

 

[hillz2k]

nope - qryactionfiner

 

[hillz2k]

qryactionfinder even

 

[pbaldy]

What's the complete code? Did you notice the slightly different way the recordset is opened in the KB article?

 

[hillz2k]

what i have so far is:

Set para1 = db.QueryDefs(vabQuery)
para1![Forms!frmsearch!product_type] = Forms![frmsearch]![Product_Type]
Set para2 = db.QueryDefs(vabQuery)
para2![Forms!frmsearch!range] = Forms![frmsearch]![Range]
Set para3 = db.QueryDefs(vabQuery)
para3![Forms!frmsearch!type] = Forms![frmsearch]![Type]
Set para4 = db.QueryDefs(vabQuery)
para4![Forms!frmsearch!typedetails] = Forms![frmsearch]![TypeDetails]
Set para5 = db.QueryDefs(vabQuery)
para5![Forms!frmsearch!manufacturers] = Forms![frmsearch]![Manufacturers]



Set rsaction = db.OpenRecordset(vabQuery, dbOpenDynaset)


and its the set rsaction...... line thats causing the problem

 

[dkinley]

heheh .. hmmm

So you have all the elements ...

Dim db As DAO.Database
Dim rsaction As DAO.Recordset

Set db = CurrentDb
Set rsaction = db.OpenRecordset(vabQuery, dbOpenSnapshot)

In the references you have included MS Office Access database engine Object Library?

-dK

 

[dkinley]

The coding ...

Set para5 = db.QueryDefs(vabQuery)
para5![Forms!frmsearch!manufacturers] = Forms![frmsearch]![Manufacturers]

is not required. Just place the "Forms![frmsearch]![Manufacturers]" into the query itself.

Then add the following to my previous post in correct order.


Dim vabQuery As String

vabQuery = "qryactionfiner"

 

[hillz2k]

i do have the object library yes....i was expereincing problems before and found that to be the issue so thats solved - ill post the code better this time, including all the defining:

Option Compare Database
Dim db As DAO.Database 'variable to point to a database
Dim rsaction As DAO.Recordset

Private Sub Search_Button_Click()

Set db = CurrentDb
Dim vabQuery As String
Dim para1 As QueryDef
Dim para2 As QueryDef
Dim para3 As QueryDef
Dim para4 As QueryDef
Dim para5 As QueryDef

vabQuery = "qryactionfinder"

Set para1 = db.QueryDefs(vabQuery)
para1![Forms!frmsearch!product_type] = Forms![frmsearch]![Product_Type]
Set para2 = db.QueryDefs(vabQuery)
para2![Forms!frmsearch!range] = Forms![frmsearch]![Range]
Set para3 = db.QueryDefs(vabQuery)
para3![Forms!frmsearch!type] = Forms![frmsearch]![Type]
Set para4 = db.QueryDefs(vabQuery)
para4![Forms!frmsearch!typedetails] = Forms![frmsearch]![TypeDetails]
Set para5 = db.QueryDefs(vabQuery)
para5![Forms!frmsearch!manufacturers] = Forms![frmsearch]![Manufacturers]



Set rsaction = db.OpenRecordset(vabQuery, dbOpenDynaset)


With rsaction
If rsaction.RecordCount > 0 Then
.MoveFirst
Do While Not .EOF

.....................etc

 

[pbaldy]

hillz:

Did you notice the slightly different way the recordset is opened in the KB article?

dk: you can't open a recordset on a query with parameters in it without this extra work.

 

[hillz2k]

all of the [forms!][frmsearch]........ that i have as the parameters is also in the criteria sections of the query as this works opening normally - its just when it comes to the recordset you have to redefine all these....am i right pbaldy?

 

[dkinley]

Apologies pbaldy and to all.

I am using Access2007. I am not sure if a fluke of '07 or my misunderstanding.

I understood that hillz had created a query and saved it and was wanting to execute as mentioned (instead of creating on on the fly). In my '07 application I set the criteria in the query (and did not have to specify in code) and it is working fine.

Thanks for pointing out the specification in the article in case this will not work once they release an update to '07 - that way I am not slamming my head up against the wall wondering what is going on.

-dK

 

[hillz2k]

sorry - i should have said it was access 2003

 

[hillz2k]

pbaldy - that website you posted has an example of a recordset:

Set rstCountOrders = qdfMyQuery.OpenRecordset()

and it defines the:

qdfMyQuery as querydef

have i defined it wrong - should i have just ONE querydef and somehow add all 5 parameters to it or is having 5 querydef correct??

and if so how do i open the recordset using all 5 querydef and not just 1 like the example??

Thanx

 

[dkinley]

Just in case (I can't recall) db is a reserved word ... (I've always used dbs as a habit because of the way I was taught).

Try changing Set db = CurrentDb

to Set dbs = CurrentDb and replicate the change to variable name throughout the code.

-dK