Recordset loop skipping entry

cadlum · June 26th, 2015, 04:21 PM

I have 2 identical tables of asset information. Table 1 has 251 records while table 2 has 84 records. All 84 records are in table 1 right now. My end goal is to be able to click a button, have vba script run, and table 2 updates table 1 with any changes from different fields. I know there are merging options with query and what not but the exact structure of my tables doesnt play well with it.

Right now, my code is able to loop through the values of each computername and display it. For some reason, when it gets to the 130th record of table one, the loop skips it and returns the 131st record, it stays one ahead for the rest of the loop and then prints the 130th record finally.

Since I am trying to compare field values, this sudden shift throws everything off. Any ideas why it skips?:confused:

Code:

Option Compare Database

Private Sub UpdateAssetsBTN_Click()
On Error GoTo Err_Proc

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set rs1 = CurrentDb.OpenRecordset("Assets1")
Set rs2 = CurrentDb.OpenRecordset("Assets2")

If rs1.RecordCount <> 0 And rs2.RecordCount <> 0 Then
    rs1.MoveFirst
    While Not rs1.EOF
        If rs1.Fields("ComputerName") Like "JT-*" Then
            Debug.Print "JT1: " + rs2.Fields("ComputerName")
            Debug.Print "IT1: " + rs1.Fields("ComputerName")
            rs2.MoveNext
        End If
        rs1.MoveNext
    Wend
End If

'always close what you open
Exit_Proc:
 On Error Resume Next
 rs.Close
 Set rs = Nothing
 Exit Sub
Err_Proc:
 MsgBox Err.Number & ": " & Err.Description
 Resume Exit_Proc

End Sub