Populating global variables from text boxes using a loop to change between variable and text boxes i-CodePudding

I have an Access database and I am using VBA to extract data and place it into global variables.

I have a number of global variables named gblDriver1, gblDriver2, gblDriver3 etc which I am using to hold information from a record set to use in a report.

I have code that opens a recordset and finds the relevant drivers for each date which I have working using 'For i - 1 to j' where j is the date, see below:

Private Sub Command95_Click()

    Dim i As Integer
    Dim j As Integer
    Dim oApp As New Outlook.Application
    Dim oEmail As Outlook.Mailitem
    Dim strDriver As String
    Dim rosteredDay As Date
    Dim strSQL As String
    
    j = cboNumberOfDays
    
    For i = 1 To j
           
        'Set variables
            rosteredDay = Form.Controls("cboDate" & i).Value
            
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
            
            strSQL = "SELECT tblRoster.RosterID, tblRoster.DateRoster, tblDriver.Driver " & _
            " FROM tblRoster INNER JOIN tblDriver ON tblRoster.RosterID = tblDriver.RosterID " & _
            " WHERE (((tblRoster.DateRoster)= #" & Format(rosteredDay, "yyyy-mm-dd") & "#));"
            
            'Open table with driver names for specified date
            Set db = CurrentDb
            Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
            
            ' Check there is data
            If rst.EOF And rst.BOF Then
                MsgBox "No drivers found for the required date!"
            Else
            ' Loop and build a string of driver names
                Do Until rst.EOF
                    strDriver = strDriver   rst![Driver] & "; "
                    rst.MoveNext
                Loop
                    gblDriver1 = strDriver
                    'Me.txtDieselDriver01 = strDriver
            End If
        
        
        'Close recordset and connection to database
        
        strDriver = ""
        rst.Close
        Set rst = Nothing
        Set db = Nothing
    Next i

End Sub

Near the bottom where it says "gblDriver1 = strDriver" I would like to use the number in j to increment the global variable by 1 each time the loop runs thus populating the global variables ready for when the report opens.

I have tried:
gblDriver & i
(gblDriver * I)
("gblDriver") & i

Any help or suggestion would be greatly appreciated.

Thanks in advance

CodePudding user response：

Use an array:

Public gblDriver(1 To 20) As String

' ----

gblDriver(1) = strDriver