A recordset is a set of records you can read from (and update, and even add to) in Visual Basic (from a source: a table or a query). It's not something you can reference in the from-part of a SQL-statement. That's reserved for tables and queries. No recordsets, no variables, no databases, nothing except tables and queries.
In your example it should be:
SQLQuery = "SELECT * FROM TblMain WHERE Pet = 'dog'"
Set rsSecond = dbMydatabase.OpenRecordset(SQLQuery)
No need at all for rsFirst, unless your program needs to read all the records and the dog-records in parallel.
Hope this helps.
Here's what I've done:
Dim dbMydatabase As DAO.Database
Dim rsFirst As DAO.Recordset
Dim rsSecond As DAO.Recordset
Dim SQLQuery As String
Set dbMydatabase = OpenDatabase("C:\Pathlist\db1.mdb")
SQLQuery = "SELECT * FROM tblMain"
Set rsFirst = dbMydatabase.OpenRecordset(SQLQuery)
Now everything is peachy so far. But when I add the following to create the second recordset as a subset of the first one, I get an error. Note: Pet is a field in tblMain.
SQLQuery = "SELECT * FROM rsFirst WHERE Pet = 'dog'"
Set rsSecond = rsFirst.OpenRecordset(SQLQuery)
That last line is where I get run-time error 3421: Data type conversion error. Anyone know how to correct the code to prevent the error?
Note: There will be 8 recordsets, each created as a subset of the last. But once I know how to make one recordset from another, I can do the code for the rest.
Any help and/or insight will be greatly appreciated.