Link to home
Start Free TrialLog in
Avatar of Scott Palmer
Scott PalmerFlag for United States of America

asked on

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

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'")
Dim i

i = 1

   Do
   ropt & i & .MoveFirst  
   Do  
      If ropt & i & .RecordSet <> 0 then
       "Do something here"
     end if
      ropt & i &.MoveNext      
   loop Until ropt & i & .EOF = True
      i = i + 1
   Loop until i = 2


Thanks,

Scott


ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott Palmer

ASKER

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

Open 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

Open 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

Open in new window

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

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