CODEWITHSUNDEEP
ms-accessvba
heinst
heinst

Reputation: 8786

Outer Loop Running 1 time VBA

I have this code, that I am trying to filter and compare data from tables, the inner loop runs the correct number of times, but the outer only runs once and I cannot figure out why.

Do While Not rstA.EOF

' Gets the first serial for filtering
rstA.MoveFirst
serialNumber = rstB!serial
rstB.Filter = "serial = '" & serialNumber & "'"
' A nested loop for the filtered rst and goes til the end of that
Do While Not rstB.EOF

    If rstA.Fields("serial") = rstB.Fields("serial") Then

        If rstA.Fields("accountnumber") <> rstB.Fields("accountnumber") Then

            'Print Message

        ElseIf rstA.Fields("model_number") <> rstB.Fields("model_number") Then

            'Print Message

        End If

    Else
    ' This will always be the default until there are two matching serial numbers
    ' This will advnace rstA +1 and make rstFiltered stay put
    ' I had to work around the advancements on the outside of this statement
    ' Thats why I move rstA (+1 = 1) and rstFiltered (-1 +1 = 0)
        'Print message
        rstB.MovePrevious
    End If
    rstB.MoveNext
    rstA.MoveNext
Loop
Loop

I tried to advnace the rstB in between the two ending loop statements like:

rstB.MoveNext

but the compiler said that the recordset was empty.

EDIT FINAL CODE:

Do Until rstB.EOF

Do Until rstA.EOF
    If rstA.Fields("serial") = rstB.Fields("serial") Then
        If rstA.Fields("accountnumber") <> rstB.Fields("accountnumber") Then
            accountMessage = "Account number A, " & rstA.Fields("accountnumber") & ", and Account " _
            & "number B, " & rstB.Fields("accountnumber") & ", for serial number ," & rstB.Fields("serial") & ", do not match."
            Debug.Print accountMessage
        ElseIf rstA.Fields("model_number") <> rstB.Fields("model_number") Then
            accountMessage = "Model number A, " & rstA.Fields("model_number") & ", and Model " _
            & "number B, " & rstB.Fields("model_number") & ", for serial number ," & rstB.Fields("serial") & ", do not match."
            Debug.Print accountMessage
        End If
    Else
    ' This will always be the default until there are two matching serial numbers
    ' This will advnace rstA +1 and make rstFiltered stay put
    ' I had to work around the advancements on the outside of this statement
    ' Thats why I move rstA (+1 = 1) and rstFiltered (-1 +1 = 0)
        rstB.MoveNext
        rstA.MovePrevious
    End If
    rstA.MoveNext
Loop
rstB.MoveNext
Loop

Upvotes: 0

Views: 447

Answers (2)

Zaider
Zaider

Reputation: 2013

Your last line in your first inner loop, rstA.MoveNext, is what is making your first outer loop reach EOF and only run once.

From your comment on Dick's question is this a fair assumption of what you are trying to accomplish?

It looks like what you are trying to do is go through table B, get the serial number, check against records in table A.

Assuming there is only one record in B for every serial number since you keep B in the same position if A doesn't match so that the loop continues.

Do Until rstB.EOF

    Do Until rstAB.EOF
        If rstA.Fields("serial") = rstB.Fields("serial") Then
            If rstA.Fields("accountnumber") <> rstB.Fields("accountnumber") Then
                'Print Message
            ElseIf rstA.Fields("model_number") <> rstB.Fields("model_number") Then
                'Print Message
            End If
        End If
        rstA.MoveNext
    Loop

    rstB.MoveNext
Loop

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33175

When you filter rstB down to (presumably) one record, it reaches EOF after the first pass. You would have to Clone rstB then filter the Clone. But it seems like you could use a query to get just the records you want.

Sub Comparerss()

    Dim rstFiltered As ADODB.Recordset
    Dim aSql(1 To 4) As String

    Set rstFiltered = New ADODB.Recordset

    'outer join were tblB stuff is Null means they don't match tblA
    aSql(1) = "SELECT tblA.Serial"
    aSql(2) = "FROM tblA LEFT JOIN tblB ON (tblA.ModelNumber = tblB.ModelNumber)"
    aSql(3) = "AND (tblA.AccountNumber = tblB.AccountNumber) AND (tblA.Serial = tblB.Serial)"
    aSql(4) = "WHERE (((tblB.AccountNumber) Is Null) AND ((tblB.ModelNumber) Is Null));"

    rstFiltered.Open Join(aSql, Space(1)), CurrentProject.Connection

    Do While Not rstFiltered.EOF
        Debug.Print rstFiltered.Fields("Serial").Value
        rstFiltered.MoveNext
    Loop

End Sub

Upvotes: 0

Related Questions