Good Afternoon All;
I have created an audit trail procedure for an access database, for a form based on a query, unfortunately it is giving an error when running, but does record the changes to the audit table, my setup is as follows:
I have a table called: tbl_audit which contains the following fields:
Audit_id – AutoNumber
Datetime – Date/Time field with a default value set to: =Now()
User_name – Text
Form_name –Text
Field_name –Text
Original_value –Text
New_value – Text
Record_id – Number
I have a module called: Audit with the following code:
And on my form I have the following events set:
Before Update:
After Del Confirm:
The code does capture the changes to the audit table (tblAUDIT); but gives an error when running:
Error!
Operation is not supported for this type of object
I can’t see where this is going wrong, your help as always is appreciated.
Kind Regards
Tor Fey
I have created an audit trail procedure for an access database, for a form based on a query, unfortunately it is giving an error when running, but does record the changes to the audit table, my setup is as follows:
I have a table called: tbl_audit which contains the following fields:
Audit_id – AutoNumber
Datetime – Date/Time field with a default value set to: =Now()
User_name – Text
Form_name –Text
Field_name –Text
Original_value –Text
New_value – Text
Record_id – Number
I have a module called: Audit with the following code:
Code:
Option Compare Database
Option Explicit
Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblAUDIT", cnn, adOpenDynamic, adLockOptimistic
strUserID = Environ("USERNAME")
Select Case UserAction
Case "EDIT"
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![form_name] = Screen.ActiveForm.Name
![record_id] = Screen.ActiveForm.Controls(IDField).Value
![field_name] = ctl.ControlSource
![original_value] = ctl.OldValue
![new_value] = ctl.Value
![user_name] = strUserID
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![user_name] = strUserID
![form_name] = Screen.ActiveForm.Name
![Action] = UserAction
![record_id] = Screen.ActiveForm.Controls(IDField).Value
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub
And on my form I have the following events set:
Before Update:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo errHandler
If Me.NewRecord Then
Call AuditChanges("TF_Ben_ID", "NEW")
Else
Call AuditChanges("TF_Ben_ID", "EDIT")
End If
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
End Sub
After Del Confirm:
Code:
Private Sub Form_AfterDelConfirm(Status As Integer)
On Error GoTo errHandler
If Status = acDeleteOK Then Call AuditChanges("TF_Ben_ID", "DELETE")
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
End Sub
The code does capture the changes to the audit table (tblAUDIT); but gives an error when running:
Error!
Operation is not supported for this type of object
I can’t see where this is going wrong, your help as always is appreciated.
Kind Regards
Tor Fey
Last edited: