Passing Parameters into query recordset

[pbaldy]

dk, can you open a recordset on a query with form parameters in it? I have to leave for a doctor's appointment right now, so don't have time to test. In previous versions, you can't open the recordset with jumping through some hoops.

 

[pbaldy]

Gotta go, but one querydef should be fine, but open the recordset using the querydef, as done in the article.

 

[hillz2k]

it isnt db the code is db....(something )..... i just didnt want to post what the name is on here - lets say its dbAccess and not db.....sorry for the confusion

 

[dkinley]

Yes, pbaldy ...

I have several queries set up to export data. I open them as a snapshot, format the data, cycle and shove into a preformated excel spreadsheet. I utilize the same query and adjust the criteria as a reference to input.

-dK

 

[dkinley]

Your code ...

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

Private Sub Search_Button_Click()

Just theorizing here ... but the the variables are declared outside the function and not as a public. Can't recall with certainty, but not sure if the sub function can see the variables or not. Possible issue of the function not seeing rsaction when you execute?

-dK

 

[hillz2k]

well if i edit the query so that it just contains 1 parameter rtaher than 5 i can get it to work.....so now its just figuring out how to define the other 4

 

[hillz2k]

so i have.....

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

Private Sub Search_Button_Click()
Set db = CurrentDb
Dim vabQuery As String
Dim qdfpara1 As QueryDef

vabQuery = "qryfaultfinder"

Set qdfpara1 = db.QueryDefs(vabQuery)
qdfpara1![Forms!frmsearch!product_type] = Me.Product_Type


Set rsquery = qdfpara1.OpenRecordset()



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


this works - but onlycontains 1 parameter - not all 5

 

[hillz2k]

YEAY!!!!!!!!!!!!!!!!!!

I GOT IT TO WORK

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

Private Sub Search_Button_Click()
Set db = CurrentDb
Dim vabQuery As String
Dim qdfpara1 As QueryDef

vabQuery = "qryfaultfinder"

Set qdfpara1 = db.QueryDefs(vabQuery)
qdfpara1![Forms!frmsearch!product_type] = Me.Product_Type
qdfpara1![Forms!frmsearch!range] = Me.Range
qdfpara1![Forms!frmsearch!type] = Me.Type
qdfpara1![Forms!frmsearch!typedetails] = Me.TypeDetails
qdfpara1![Forms!frmsearch!manufacturers] = Me.Manufacturers


Set rsquery = qdfpara1.OpenRecordset()



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

it was as simple as that to define the other 4 parameters


thanx dk and pbaldy for all your help

Much appreciated

 

[dkinley]

Hmmm ... you have ... Dim qdfpara1 As QueryDef

Not sure, but I have specified in the past as ...

Dim qdfpara1 As DAO.QueryDef

-dK

 

[hillz2k]

its ok dk - ive sorted the problem now - thanx for all your help......its people like you and pbaldy that makes these forums worth while

 

[dkinley]

haha! rock on!

I was gone to an older machine to compare yours against some of my old '03 coding.

Glad my meandarings could contribute something and pbaldy's expertise got you squared away.

-dK

 

[poduska]

Thanks PBaldy as the link from MS http://support.microsoft.com/default...b;en-us;209203 worked perfectly.

The trick utilized in the example is to create a queryDef and assign it the query you are trying to run.
Next assign to the queryDef each of the parameters, just like in the query critera, one row at a time. Follow the example line starting with "qdfMyQuery!". In the example you will see what looks like the query criteria but if you look closely you will notice it is formatted different with extra square brackets. When you reassign this with the actual criteria copied from the it all works.

What a great feeling to get this working.