Hi
Joining this thread late on, I know, but can I make a suggestion.
Create a function in a new module
Add the following logic
Create 2 recordsets RsOld and RsNew
Working on the assumption that both tables contain the same field names and the same number of fields
Dim FldName as String
Set RsOld = CurrentDb.OpenRecordSet("Old Table")
Do Until RsOld.EOF
Set RsNew = CurrentDb.OpendRecordSet("Select * From New Table Where PK ='" & RsOld("PK") & "'")
If Not RsNew.EOF And Not RsNew.BOF Then
For x = 0 To RsOld.Fields.Count -1
fldName = RsOld(x).Name
If RsOld(fldName) <> RsNew(fldName) Then
....Contents of field has changed ... Do Something Here
Endif
Next x
Else
.... New table has no matching PK ? Deleted
.... Do something else here
End If
RsOld.MoveNext
Loop
RsOld.Close
RsNew.Close
Set RsOld = Nothing
Set RsNew = Nothing
This will loop through each field in each record in the first table and compare it to the same field in the new table via the PK.
Only thing to consider is the error coding for Null Values in either tables and fields therein.
The advantage of this is that if you add new fields to the tables you do not need to worry about the ordinal position matching in both tables.
You could have a third table that contains the fields
PK
FldName
OldValue
NewValue
DateDetected
Now when a mismatch is detected you can post the differences to the table which shows the PK (Duplicates Ok), The name of the field that has changed, What the value was prior to change, what the new value is and what date you ran this procedure. Therefore you will end up with full traceability for all your data changes.
Code Master