creating an audit log

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi does anyone know how i may be able to retrieve ONLY the fields in two
identical tables that do not contain matching values? i currently have a
database that i need to log user changes. i am appending the original record
to a table using the beforeupdate event on a form. And i am also appending
the updated record to a table using the afteruupdate even of the same form.
my problem is i dont want to display all 100 fields that my table has i only
want to display the changes. I just don't know how to write a program that
will loop through the fields or controls and somehow return only the values
that do not match. I'm hoping someone out there has the answer and can put me
out of my misery. thanks so much!
 
A 100 Fields sounds a bit on the de-normalized side

Sub TrackDiffs(ByVal ID As String) As String
' Assuming ID Beeing The Primary Key of Both Tables
Dim Db As DAO.Database
Dim RsA As DAO.Recordset, RsB As DAO.Recordset
Dim FldA As DAO.Field, FldB As DAO.Field
Dim Res As String

Set Db = Access.CurrentDb()
Set RsA = Db.OpenRecordset("SELECT * FROM TableA Where ID='" & ID & "'",
DAO.DbOpenSnapshot)
Set RsB = Db.OpenRecordset("SELECT * FROM TableB Where ID='" & ID & "'",
DAO.DbOpenSnapshot)
For Each FldA In RsA.Fields
Set FldB=RsB.Fields(FldA.Name)
If VBA.IsNull(Fld.Value) And VBA.IsNull(FldB.Value) Then
' Do Nothing
ElseIf VBA.IsNull(Fld.Value)= True And VBA.IsNull(FldB.Value) = False
Then
Res = Res & "," & FldA.Name
ElseIf VBA.IsNull(Fld.Value)= False And VBA.IsNull(FldB.Value) = True
Then
Res = Res & "," & FldA.Name
ElseIf Fld.Value <>FldB.Value Then
Res = Res & "," & FldA.Name
End If
Next
RsA.Close: Set RsA = Nothing
RsB.Close: Set RsB = Nothing
Set Db = Nothing
TrackDiffs = VBA.Mid(Res,2)
End Function

HtH

Pieter
 
Back
Top