Basing a Recordset on a Recordset

[Sorrells]

In VBA coding, Access97, can I reference a recordset in a 2nd recordset? My current code returns a Run Time Error of 3421: “Data Type Conversion Error” when doing so in a fairly simple set of syntax. Ultimately, if I had my way, a 3rd recordset would be referenced as well.

I have two abbreviated recordsets displayed below to give an idea of what I am talking about but I will mostly appreciate general comments as to if a recordset can be referenced within a 2nd recordset.

Set rst_All_Tasks = dbs.OpenRecordset("SELECT DISTINCTROW " & Table_Name & ".Task, " & _
Table_Name & "." & OG_abbrev & " AS OG_AB, " & _
Table_Name & ".DFT," & _
Table_Name & ".Priority," & _
Table_Name & ".Freq_No," & _
Table_Name & ".Task_ID " & _
"FROM " & Table_Name & _
" ORDER BY " & Table_Name & ".Priority")


Set rstAvail_Tasks = dbs.OpenRecordset("SELECT DISTINCTROW " & rst_All_Tasks!Task, _
rst_All_Tasks!og_ab, _
rst_All_Tasks!Priority, _
rst_All_Tasks!Freq_No & _
" FROM rst_All_Tasks")

NOTE1: If in the 2nd SQL I use quotes as I normally do, then Access responds that it cannot find the table or query.

NOTE2: The 1st SQL statement uses variables Table_Name & OG_Abbrev

 

[WayneRyan]

Sorrells,

Here's a simple sample ...

Dim dbs As Database
Dim rst1 As RecordSet
Dim rst2 As RecordSet
Dim sql As String

sql = "Select * " & _
      "From Table1 " & _
      "Order by Field1"
Set rst1 = dbs.OpenRecordset(sql)
While Not rst1.EOF And Not rst1.BOF
   sql = "Select * " & _
         "From Table2 " & _
         "Where Table2Key = '" & rst1!Table1Key & "'"
   set rst2 = dbs.OpenRecordset(sql)
   If rst2.EOF and rst2.BOF Then
      MsgBox("Unexpected orphan - " & rst1!Table1Key)
   End If
   rst2.Edit
   rst2!SomeField = rst1!SomeField
   rst2.Update
   rst1.MoveNext
   Wend
Set rst1 = Nothing
Set rst2 = Nothing
Set dbs = Nothing

Wayne

 

[Sorrells]

Wayne,

Interesting! I have posted my question in 3 forums (TeckTips & dbForums) both of which replied that this is impossible in Access. I like your response better.

I'll play around with the clues you have provided and then get back to this post.

 

[Sorrells]

Wayne,

I see now that the code example you provided has a serious divergence from my objective. The recordset rst2 is based on 'Table2' but my 2nd rst must be based on rst1.

To make matters worse, the table rst1 is based on varies from one instance of the form being opened to another.
Table_Name = "[Lookup:" & lbl_1.Caption & "]"

In my program, rst1 can return values from any of about 13 tables depending on the value of "Table_Name".

Finally, what I want is that rst2 be based on rst1 and not a table, primarily because I will never know which table is pertinent in a particular instance with the form.

The replies of creating a query then basing a 2nd query on it also does not lend itself to a variable table name. The initial query would have to construct a table name, something that the query grid seems completely unwilling to do.

I am afraid that I am stuck with creating a temporary table and using nested recordsets to populate it. I can then assign the table as the RowSource for the listbox.

This I have completed successfully before but it seems so darn inefficient!

Let me know what you think.