I have an audit table and function as follows. You could modify to include the trapping of old value and new value:
tblAudit
ID [primary key]
AuditDate [Date]
UserName [text]
Action [text]
Item_ID [integer]
Create a query called qryAudit which is a direct view to tblAudit.
This would go on the form which is being tracked provided you add a new records through a button called btnAdd.
Private Sub btnAdd_Click()
Dim x as Integer
Dim ItemNumber As Long
On Error Resume Next
ItemNumber = Me![ID] 'this is provided your pk for the row is called ID
X = AuditUpdate ("Add New Record", ItemNumber)
End Sub
If you only need to know when they were changed and what was changed then use the following:
Private Sub Form_AfterUpdate()
Dim x
Dim ItemNumber As Long
On Error Resume Next
ItemNumber = Me![ID]
x = AuditUpdate ("Modify Record", ItemNumber)
End Sub
X calls AuditUpdate which would be something like the following in a new
module:
Function AuditUpdate((Flag As String, ItemNumber)
Dim db As Database
Dim SqlString As String
On Error Resume Next
Set db = CurrentDb()
SqlString = "INSERT INTO qryAudit (AuditDate, UserName, Action, Item_ID) values ('" & Date & "','" & CurrentUser() & "','" & Flag & "'," & ItemNumber & ")"
db.Execute (SqlString)
End Function
Watch out for word wrapping!
HTH,
Beth M
|
