Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Compare Tables

Compare Tables

Compare Tables

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
ElseIf rstBase(PrimaryKeyField) > rstVarying(PrimaryKeyField) Then
ElseIf rstBase(PrimaryKeyField) < rstVarying(PrimaryKeyField) Then
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

FieldChanged = False
End If

RE: Compare Tables

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

RE: Compare Tables

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.

RE: Compare Tables

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

CODE -->

 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
End Sub 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close