I'm trying to track changes made to a subform. Anything added, deleted, or editted will be recorded in a table called Audit_tbl. When I make a add or edit a record i get a 3001:Invalid argument error. I'm calling the function in the form as Before Update event procedure.
Attached are the reference libraries I've added.
Attached are the reference libraries I've added.
Code:
Public Function AuditChanges(RecordID As String, UserAction As String)
On Error GoTo auditerr
Dim db As Database
Dim rst As Recordset
Dim clt As Control
Dim userloging As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Select *from audit_tbl", 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 "Delete"
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 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.Value
.Update
End With
End If
End If
Next clt
End Select
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
auditerr:
MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error"
Exit Function
End Function