tracking fields

[cvaccess]

Hello,

I have a form which contains fields populated by a query when the form opens. The fields can be modified by a user (change the data in the field or fill in a field). I have been asked if it is possible to track the field changes. For example, if the user changes the date field, stamp a new field with either their username or date. Basically they would like to track whenever their is a change. Is this possible?

Please let me know if you need more detail.

Thanks.

 

[scottfarcus]

Go to this site:

http://www.candace-tripp.com/access_downloads.htm

Look for Audit Trail example.

Good luck.

 

[cvaccess]

How does it work? It looks like it trails it to a new table tblAudit but I have tried updating/modifying and I do not see a trail (new tbl). Please explain. Thanks.

Function WriteAudit(frm As Form, lngID As Long)
On Error GoTo err_WriteAudit

Dim ctlC As Control

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblAudit")

' For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Then
If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
If Not IsNull(ctlC.Value) Then
rst.AddNew
rst("ID") = lngID
rst("FieldChanged") = ctlC.Name
rst("FieldChangedFrom") = ctlC.OldValue
rst("FieldChangedTo") = ctlC.Value
rst("User") = GetUserName_TSB()
rst("DateofHit") = Now
rst.Update
End If
End If
End If
Next ctlC


exit_WriteAudit:
rst.close
db.close
Set rst = Nothing
Set db = Nothing
Exit Function

err_WriteAudit:
MsgBox Str(Err)
MsgBox Err.Description
Resume exit_WriteAudit