Outer Loop Running 1 time VBA

Question

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

You set rstFiltered=rstB and then loop through rstFiltered until EOF. rstB and rstFiltered both point to the same recordset, so your outer loop only runs once... And where does rstA come from? — Tim Williams
– Tim Williams, Commented Jul 22, 2013 at 16:45
rstA is recordset in which i am comparing the data from recordsetB to — heinst
– heinst, Commented Jul 22, 2013 at 17:24
OK, but you still have the problem: rstB and rstFiltered are the same object. — Tim Williams
– Tim Williams, Commented Jul 22, 2013 at 17:41
I got rid of that and now im just dealing with rstA and rstB, but same problem is happening. Should I go until the end of the recordset by getting the number of records? Would that help? — heinst
– heinst, Commented Jul 22, 2013 at 17:57

Zaider · Accepted Answer · 2013-07-22 19:42:29Z

1

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

edited Jul 22, 2013 at 19:42

answered Jul 22, 2013 at 19:07

Zaider

2,0231 gold badge24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

heinst Over a year ago

Thanks...Ill post my final code as an edit so you can see how i modified it.

Dick Kusleika · Accepted Answer · 2013-07-22 18:15:25Z

0

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

answered Jul 22, 2013 at 18:15

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

1 Comment

heinst Over a year ago

What Im trying to do is go check tableB against tableA to see what changed, what got added and what got deleted, so thats why I need the outer loop to loop once for every record and the inner to loop 23,000 times (depending on data amount) so every record is checked agains every record

Collectives™ on Stack Overflow

Outer Loop Running 1 time VBA

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related