Compare Tables

[air1access]

I working with the code below.
This works perfectly if I just have 1 primary key that is a number field.
Now I have 2 fields that are the primary key. One is a number, and one is a text field.
I need to be able to compare each record by the 2 fields...

How can I get this to do that...?

Do Until rstBase.EOF
If rstVarying.EOF = True Then
rstBase.MoveNext
ElseIf rstBase(PrimaryKeyField) > rstVarying(PrimaryKeyField) Then
rstVarying.MoveNext
ElseIf rstBase(PrimaryKeyField) < rstVarying(PrimaryKeyField) Then
rstBase.MoveNext
Else
For Each fld In rstBase.Fields
If Nz(rstBase(fld.Name)) <> Nz(rstVarying(fld.Name)) Then

db.Execute "INSERT INTO Event_Changes_1 (Event_ID, FieldName, OldText, NewText, Modified_Date, Carrier) " & _
"SELECT " & rstBase(PrimaryKeyField) & ", '" & fld.Properties("Caption") & "','" & Nz(rstBase(fld.Name), "<Null>") & "','" & Nz(rstVarying(fld.Name), "<Null>") & "', '" & rstVarying!Modified_Date & "', '" & rstVarying!Display_Name & "';"

FieldChanged = True

End If
Next fld

rstBase.MoveNext
rstVarying.MoveNext
FieldChanged = False
End If
Loop

 

[MajP]

Why can you not base this on a query joining tblBase to tblVarying. Then just loop that query. No need to loop two recordsets.

 

[air1access]

Majp -
I'm doing it this way so it writes out the changes to a 3rd table...
Its not actually making the changes - just writing out whats change in each field - if there are any changes.

 

[MajP]

Sorry. I was not clear enough. Here is my query:
qryMatches

SELECT 
 EmployeesBase.LastName, 
 EmployeesBase.FirstName
FROM 
 EmployeesBase 
INNER JOIN 
 EmployeesNew ON (EmployeesBase.FirstName = EmployeesNew.FirstName) 
 AND (EmployeesBase.LastName = EmployeesNew.LastName);

The two tables have firstname and lastname as the PK

Public Sub TrackChanges()
  Dim RSMatches As dao.Recordset
  Dim rsBase As dao.Recordset
  Dim rsNew As dao.Recordset
  Dim PK1 As String
  Dim PK2 As String
  Dim fld As Field
  Dim fld1 As Field
  Dim fld2 As Field
  
  Set RSMatches = CurrentDb.OpenRecordset("qryMatches")
  Do While Not RSMatches.EOF
    PK1 = RSMatches!FirstName
    PK2 = RSMatches!LastName
    Set rsBase = CurrentDb.OpenRecordset("Select * from EmployeesBase where FirstName = '" & PK1 & "' AND LastName = '" & PK2 & "'")
    Set rsNew = CurrentDb.OpenRecordset("Select * from EmployeesNew where FirstName = '" & PK1 & "' AND LastName = '" & PK2 & "'")
    For Each fld In rsBase.Fields
       Set fld1 = rsBase.Fields(fld.Name)
       Set fld2 = rsNew.Fields(fld.Name)
       If fld1.Value <> fld2.Value Then Debug.Print fld1.Name & ": "; fld1.Value & " new value: " & fld2.Value
    Next fld
    RSMatches.MoveNext
  Loop
End Sub