I need to know the syntax for using a variable with the RecordSet object name.

Thanks Norie.  This was abbreviated code for the example.  There are 6 fields that need to be checked, so I have it written out for all 6, but I was hoping have less code.

Thanks,
Scott

You can use Collection to hold the recordsets:

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

Select allOpen in new window

Well you can work it a bit like this

Function test()
Dim actionToPerform As Variant
For i = 1 To 2
actionToPerform = "IterateRst"
Application.Run actionToPerform, "table" & i
Next
End Function

Select allOpen in new window

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

Select allOpen in new window

The code works with the assumption you have two tables named table1 & table2, just make the necessary adjustments

Scott

What are you actually doing with the multiple recordsets?

I second Norie's question. Cause often using a SQL approach can be simpler and/or more efficient.

Please close this.

Thanks,
Scott