I have created an audit trail vba code for my MS-Access database : I copied the code, and got it working for the "New" case:
But I get Error 483 when I try to do an "Update":
Here's the Code:
Option Compare Database
Public Function AuditLog(RecordID As String, UserAction As String)
On Error GoTo AuditErr
Dim DB As Database
Dim rst As Recordset
Dim ctl As Control
Dim UserLogin As String
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT * from tbl_AuditLog")
UserLogin = Environ("Username")
Select Case UserAction
Case "New"
With rst
.AddNew
![EditDate] = Now()
![User] = UserLogin
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID)
.Update
End With
Case "Delete"
With rst
.AddNew
![EditDate] = Now()
![User] = UserLogin
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID)
.Update
End With
Case "Edit"
For Each ctl In Screen.ActiveForm.Controls
If (ctl.controltpe = acTextBox _
Or ctl.ControlType = acComboBox) Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![EditDate] = Now()
![User] = UserLogin
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID)
!FieldName = ctl.ControlSource
!OldValue = ctl.OldValue
!NewValue = ctl.Value
.Update
End With
End If
End If
Next ctl
End Select
rst.Close
DB.Close
Set rst = Nothing
Set DB = Nothing
AuditErr:
If Err.Number = 0 Then
Exit Function
Else
MsgBox Err.Number & " :: Unable to create audit log ::" & Err.Description
End If
Exit Function
End Function
It breaks at specifically this line:
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
Now again I copied this code, and in the source for the code it worked. But I feel, that something must be missing here. I just don't know what. Anyone able to see the error, or point me in a good direction to start?
