Recordset FindFirst in an Array

Question

I am using Access to run VBA code. I have an array that runs through a recordset query "rstQueryTrainings". In this query, there are multiple rows per ID. What I would like this array to do is be able to identify if a row contains the first unique ID in rstQueryTrainings, and if so create a character string. If it is not the first unique ID in the recordset, then create a different character string. To note, these character strings make up a body of an email. My code uses the Recordset Findfirst property, and if there is a match then bookmark that record. If the record is bookmarked then run character string. If it is not bookmarked, then run a different character string. I get the following error:

Error 3077: Syntax error (comma) in expression

I'm not sure if this error is popping up because I'm using FindFirst in an array.

requiredTrain = ""
        If tArraySize <> -1 Then
            ID = ""
            For eachTraining = 0 To tArraySize
                ID = PersonnelCompList(eachTraining)
                Debug.Print ID
                IDstring = "PersonnelCompList = " & ID
                Debug.Print IDstring
                rstQueryTrainings.FindFirst IDstring
                If Not rstQueryTrainings.NoMatch Then
                    rstQueryTrainings.Bookmark = varFirstMark
                End If  
                If IsEmpty(rstQueryTrainings.Bookmark) Then
                    requiredTrain = requiredTrain + "<LI>" & "<b>Course</b>: " & ", " & trainingList(eachTraining) & ", <b>Number</b>: " & courseNumList(eachTraining) & ", <b>Link</b>: " & courseNumList(eachTraining) & " | </b>" & statusList(eachTraining) & "</b>" & " (" & daysRemaining(eachTraining) & " days remaining)" & "</LI>"
                Else
                    requiredTrain = requiredTrain + "<b>Personnel Due</b>: " & PersonnelCompList(eachTraining) & "<LI>" & "<b>Course</b>: " & ", " & trainingList(eachTraining) & ", <b>Number</b>: " & courseNumList(eachTraining) & ", <b>Link</b>: " & courseNumList(eachTraining) & " | </b>" & statusList(eachTraining) & "</b>" & " (" & daysRemaining(eachTraining) & " days remaining)" & "</LI>"
                End If
                Debug.Print requiredTrain
            Next eachTraining
        End If

score 0 · Answer 1 · answered Mar 11 '20 at 07:58

I'm not sure what the Bookmarks are for. This should do:

requiredTrain = ""
For eachTraining = 0 To tArraySize
    ID = PersonnelCompList(eachTraining)
    Debug.Print ID
    IDstring = "PersonnelCompList = " & ID
    Debug.Print IDstring
    rstQueryTrainings.FindFirst IDstring
    If rstQueryTrainings.NoMatch Then
        requiredTrain = requiredTrain + "<LI>" & "<b>Course</b>: " & ", " & trainingList(eachTraining) & ", <b>Number</b>: " & courseNumList(eachTraining) & ", <b>Link</b>: " & courseNumList(eachTraining) & " | </b>" & statusList(eachTraining) & "</b>" & " (" & daysRemaining(eachTraining) & " days remaining)" & "</LI>"
    Else
        requiredTrain = requiredTrain + "<b>Personnel Due</b>: " & PersonnelCompList(eachTraining) & "<LI>" & "<b>Course</b>: " & ", " & trainingList(eachTraining) & ", <b>Number</b>: " & courseNumList(eachTraining) & ", <b>Link</b>: " & courseNumList(eachTraining) & " | </b>" & statusList(eachTraining) & "</b>" & " (" & daysRemaining(eachTraining) & " days remaining)" & "</LI>"
    End If
    Debug.Print requiredTrain
Next eachTraining

Recordset FindFirst in an Array

1 Answers1