Audit Trail

[usfarang]

Hi everybody, I'm developing an audit trail for the first time. It works when I add a new record but not when I change anything. By works I mean it adds the record to the audit table.


Here is some code that hopefully somebody can spot where I'm going wrong.

Dim DB As Database
Dim rst As Recordset
Dim clt As Control
Dim UserLogin As String


Set DB = CurrentDb
Set rst = DB.OpenRecordset("select * from tblAuditTrail", adOpenDynamic)


UserLogin = Environ("Username")
Select Case UserAction






Case "Edit"


For Each clt In Screen.ActiveForm.Controls
If (clt.ControlType = acTextBox _
Or clt.ControlType = acComboBox) Then
If Nz(clt.Value) <> Nz(clt.OldValue) Then

With rst
.AddNew
![DateTime] = Now()
!UserName = UserLogin
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID).Value
!FieldName = clt.ControlSource
!OldValue = clt.OldValue
!NewValue = clt.NewValue
.Update


End With
End If
End If


Next clt
End Select




'tblAuditTrail
'AuditTrailID
'DateTime
'UserName
'FormName
'Action
'RecordID Number
'FieldName
'OldValue
'NewValue


'frmEmployees
'BeforeUpdateEvent


If Me.NewRecord Then
Call AuditChanges("EmpID", "New")
Else
Call AuditChanges("EmpID", "Edit")
End If

[June7]

Review http://allenbrowne.com/AppAudit.html

[usfarang]

Ok, issues resolved. Minor 3 character mistake in code.
Thanks,