INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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.

Jobs

Compare Tables

Compare Tables

(OP)
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

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

(OP)
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:
qryMatches

CODE -->

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

CODE

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 

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!

Resources

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