Setting recordset to query in Access VBA [Solved]

[Besoup]

hey I am trying to set a recordset to a query in my database so I can use the .findfirst function. I keep getting a error saying that the item is not found in the collection.

Is this possible or do I have to use a table instead of a query for my recordset?

the code I am using is:

Dim rcQuery1 as Recordset
 
Set rcQuery1 = Recordset("Query1")

I am getting stuck on the set statement.

Thanks for any help.

[ivobenkovic]

Hi....

the easiest way to do this is to refer the Microsoft DAO 3.6 Object Library (if you use at least Acess 2000) and work with the DAO objects.

Dim db As DAO.DataBase
Dim rs as DAO.Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordSet("Query1")
While Not rs.Eof
  ' Do what has to be done ;-)
  rs.MoveNext
Wend
Set rs = Nothing

if you only want to execute the query:

Dim rs As ADODB.Recordset
Set rs = CurrentProject.Connection.Execute("select * from Query1")
While Not rs.Eof
  ' Do what has to be done ;-)
  rs.MoveNext
Wend
Set rs = Nothing

[Besoup]

Ty

[Besoup]

Actually I thought that was gonna work but now I am getting a too few parameters error with the following code:

Dim db As DAO.DataBase
Dim rs as DAO.Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordSet("Query1")

[Besoup]

Got it now.... me dumb. Thanks for the help.

[RobDog888]

The OpenRecordSet method takes parameters in addition to the query.

Private Sub OpenMe()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Query1", dbOpenDynamic, dbReadOnly, dbOptimistic)
End Sub