I need to know the syntax for using a variable with the RecordSet object name.
It is ropt1 and ropt2 etc.
I would like to loop through using i as the variable. The psuedo code would be like ropt & i & .recordset.
Below is an abbreviated version of the code. I have more iterations, but this is the idea of what I want to do.
Dim ropt1 as Recordset
Dim ropt2 as Recordset
Set ropt1 = CurrentDB.OpenRecordset(SELECT * FROM tblRoster WHERE Medicaid#1 = 'Opt'")
Set ropt2 = CurrentDB.OpenRecordset(SELECT * FROM tblRoster WHERE Medicaid#2 = 'Opt'")
i = 1
ropt & i & .MoveFirst
If ropt & i & .RecordSet <> 0 then
"Do something here"
ropt & i &.MoveNext
loop Until ropt & i & .EOF = True
i = i + 1
Loop until i = 2
Public Function SampleRecordsets() Dim Records As DAO.Recordset Dim Recordsets As New Collection Dim Index As Integer Dim RecordsetName As String For Index = 1 To 4 Set Records = CurrentDb.OpenRecordset("Select * From Table" & CStr(Index) & "") ' Assigned a name to the recordset. RecordsetName = "Recordset" & CStr(Index) Recordsets.Add Records, RecordsetName Next Debug.Print Recordsets.Count For Index = 1 To 4 ' Use assigned name of recordset. RecordsetName = "Recordset" & CStr(Index) Set Records = Recordsets.Item(RecordsetName) Records.MoveLast Debug.Print Records.RecordCount Next Records.Close End Function
Function test() Dim actionToPerform As Variant For i = 1 To 2 actionToPerform = "IterateRst" Application.Run actionToPerform, "table" & i Next End Function
The code works with the assumption you have two tables named table1 & table2, just make the necessary adjustments
Function IterateRST(tableName As String) Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset(tableName) With rst rst.MoveFirst While Not .EOF Debug.Print .Fields(1) .MoveNext Wend Set rst = Nothing End With End Function