Greetings Fellow Access users,
I'm working on an MS Access document that requires an audit trail. When I save/update data or add new data on the required form I receive an error '3001: Invalid Argument'
However VBA does not come up with any debug issues. That data saves in the required table however nothing is inputted the audit trail table......
I'm out of ideas on how to fix this.
Thanks in advanced
Here is the code I'm using in the form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Call AuditChanges("Book_ID", "New")
Else
Call AuditChanges("Book_ID", "Edit")
End If
End Sub
-------------------------------------------------------------------------------------------------
Here is the Code in the Module section if it helps:
Public Function AuditChanges(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_AuditTrail", adOpenDynamic)
UserLogin = Environ("Username")
Select Case UserAction
Case "New"
With rst
.AddNew
'!DateTime = Now()
!UserName = UserLogin
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID).Value
.Update
End With
Case "Edit"
For Each ctl In Screen.ActiveForm.Controls
If (ctl.Control = acTextBox _
Or ctl.Control = acComboBox) Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
' !DateTime = Now()
!UserName = UserLogin
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID).Value
!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:
MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error"
Exit Function
End Function