DLemarr,
I use a table to store changes made in table data through forms, which I
call tblChanges, and which has the following fields:
UserName
TimeStamp
FormName
PKName
PKValue
ControlName
OldValue
NewValue
(all fields text except TimeStamp which is Date/Time)
Then, I paste the following procedure in a general module:
Sub Log_Change(frm As String, pknam As String, pkval)
Dim db As DAO.Database
Dim rst As DAO.Recordset
oldv = Forms(frm).ActiveControl.OldValue
newv = Forms(frm).ActiveControl
If oldv = newv Then Exit Sub
Set db = CurrentDb
Set rst = db.OpenRecordset("tblChanges")
With rst
.AddNew
.Fields(0) = Environ("UserName")
.Fields(1) = Now
.Fields(2) = frm
.Fields(3) = pknam
.Fields(4) = pkval
.Fields(5) = Forms(frm).ActiveControl.Name
.Fields(6) = oldv
.Fields(7) = newv
End With
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
And, finally, I use the Before Update event of each control whose changes in
data I want to track, to call the procedure like:
Call Log_Change(Me.Name, "PKField", Me.PKControl)
where PKField should be the actual name of the PK field in the underlying
table, and PKControl should be the actual name of the control bound to that
field.
I put the sub in a general module and pass the parameters so the process can
be used with several forms in a database. If only needed in one, the sub can
be placed in the form's module, and the code simplified through the use of
the Me.keyword.
Note: for the code to work, you will need to add a Microsoft DAO reference
(3.51 for A97, 3.6 for A2K or newer) in your VB editor window, if not
already there: Tools > References, scroll down to the appropriate ref. and
check it.
HTH,
Nikos
