How to compare a recordset with For Each Value Access VBA-CodePudding

This recordset needs to compare with value getting inside For Each :

Do While Not rs.EOF
    Debug.Print Trim(rs!CodeNr)
    rs.MoveNext
Loop

Result: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

For Each idx In td.Indexes
    If InStr(1, idx.Fields, " ScanSpe_") = 1 Then
        Debug.Print ExtractNumber(idx.Fields)
    End If
Next

Result: 1 10 11 12 13 14 15 16 2 3 4 5 6 7 8 9

How can check these two results that I know which one is a match or no match? Any help would be appriciated.

CodePudding user response：

Open rs sorted on Str(rs!CodeNr), not in a Do .. While but a For .. Next loop.

Likewise for the ts.Indexes; use a For .. Next loop.

Then you can match the value of the counter of the first loop and the value of the counter of the second loop and check for a match of the two values.

Exactly how depends on, if you wish to find matches or no-matches, and if the two counts of elements in the two collections are equal or not.

CodePudding user response：

I tried myself and this piece of code worked quite fine.

Do While Not rs.EOF
    For Each idx In td.Indexes
        If InStr(1, idx.Fields, " ScanSpe_") = 1 Then
            'Debug.Print ExtractNumber(idx.Fields)
            If ExtractNumber(idx.Fields) = Trim(rs!CodeNr) Then
                If rs!GS1Dupli = 0 Then
                   rs.Edit
                   rs!GS1Dupli = -1
                   rs.Update
                End If
            End If
            
        End If
    Next
    rs.MoveNext
Loop