It seems when I try to use querydef, I get a 3265 error "item not found in
thiscollection". I created the table and the query then I pasted your code
and still got the error.
at qdf.Parameters("val1") = 4
:
The key is to use a querydef to set the parameters then open the
recordset from that.
I created a query called "Query2" with two parameters "val1" and
"val2" and two fields, "field1" and "field2"
Query2:
SELECT field1, field2
FROM Table1
WHERE field1 Between [val1] And [val2];
' This code demonstrates use of parameters with a querydef
Public Sub testthis()
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
' reference to current database
Set db = CurrentDb
' load the query to the querydef
Set qdf = db.QueryDefs("Query2")
' set the parameters
qdf.Parameters("val1") = 4
qdf.Parameters("val2") = 8
' open the query in the recordset
Set rs = qdf.OpenRecordset(dbOpenDynaset)
' print the results of the query to the immediate window
Do While Not rs.EOF
Debug.Print rs!field1 & " " & rs!field2
rs.MoveNext
Loop
' clean up
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub
Cheers,
Jason Lepack
On Mar 13, 8:58 am, JoeA2006 <
[email protected]>
wrote:
Thanks Jason. Now I need to pass two parameters to the query. I have a form
with txtLocation and txtMonYr
The declaration of the function is:
Public Function ExportDetail(Locn As String, MnYr As String) As String
The click event on the form is:
Private Sub cmdExport_Click()
Call ExportDetail(txtLoc, txtPeriod)
End Sub
Can I still use the OpenRecordSet Method using parameters?
:
You ahve a small bracketing oops...
Set rs = CurrentDb.OpenRecordSet("qryLocations"), _
dbOpenSnapshot
this should be:
Set rs = CurrentDb.OpenRecordSet("qryLocations", _
dbOpenSnapshot)
Cheers,
Jason Lepack
On Mar 12, 5:04 pm, JoeA2006 <
[email protected]>
wrote:
I am unable to run code to export data from a query to excel using
Set rs = CurrentDb.OpenRecordSet("qryLocations"), _
dbOpenSnapshot
I am using a DAO connection in Access 2000.
My code works fine when I create a SQL string :
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
I have data from several queries I need to export and do not want to have to
write a sql statement for each one. I would like to be able to just set the
connection to the query object itself if possible.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
