Accessing Ms Comparing Two Recordsets
I am trying to compare two sets of records. the first rs1 has random entries. The second rs2 has default values for these entries. I am initially looking to take each of the records and see if they match the standard value sets in the second recordset. Each recordset has four fields and all four must match.
I just need help with the loop. I am trying to write non-matching records to an excel file. That's what I have so far
While Not rs1.EOF
With rs1
.MoveFirst
With rs2
.MoveFirst
While Not rs2.EOF
counter = counter + 1
a = 0
If rs1!Kk = rs2!Kk Then a = a + 1
If rs1!CC = rs2!CC Then a = a + 1
If rs1!HN = rs2!HN Then a = a + 1
If rs3!TN = rs2!TN Then a = a + 1
If a > 3 Then GoTo correct
.MoveNext
If rs2.EOF Then
If rs!Table_Name <> "table1" Then
i = i + 1
j = 1
counter = counter + 1
objSht.Cells(i, j).Value = "casenum" & rs1.Fields(1)
j = j + 1
stat_counter = stat_counter + 1
End If
If i = 65500 Then
Set wbexcel = objexcel.ActiveWorkbook
''//Set objSht = wbexcel.Worksheets("Sheet2")
Set objSht = wbexcel.Worksheets.Add
i = 2
End If
End If
correct:
rs1.MoveNext
Wend
End With
End With
Also any ideas on how I can highlight based on 2 fields that match the standard and 3 fields that match the standard values
source to share
Are the recordsets already sorted? I guess that's the case where you go to the next one rs2
in the mismatch. Personally, I would clarify to make it 100% confident.
Also I would test this thoroughly with a small test dataset with a few edge cases to make sure you get what you expect.
With that said, your code looks like it works, but I have a few small guidelines to make it easier to read.
First, I recommend cutting the nested With rs1
and With rs2
. Just refer to each recordset explicitly so that you can clearly see what is happening with each rs. eg:
If a > 3 Then GoTo correct
.MoveNext
becomes
If a > 3 Then GoTo correct
rs2.MoveNext
Next, your if statements using a = a + 1
can do some tidying. eg:
If rs1!Kk = rs2!Kk and rs1!CC = rs2!CC and rs1!HN = rs2!HN and rs3!TN = rs2!TN then
''// Do Nothing or maybe increase a count or whatever :)
else
WriteToExcel(objSht , rs1.fields)
end if
You will need to write a WriteToExcel () function, but that will make the next step easier. I think you want to write on different sheets depending on the matches?
If rs1!Kk = rs2!Kk and rs1!CC = rs2!CC and rs1!HN = rs2!HN and rs3!TN = rs2!TN then
''// Do Nothing
else if rs1!Kk = rs2!Kk and rs1!CC = rs2!CC and rs1!HN = rs2!HN then
WriteToExcel(objSht2 , rs1.fields)
else
WriteToExcel(objSht , rs1.fields)
end if
You can also look at switches in the case where you want two matches rather than special matches as above ... oh and variable j
seems a little overkill.
source to share