Question Audit trail question (1 Viewer)

[bftsg]

Hi - I have some VBA code I downloaded some time ago and use in one of my applications. I need to change the coding so that ALL data fields are saved and not just the record ID. The module fires as soon as a new record ID is created and records that value only, rather than all of the data in the record.
Should I add another control in After Update or similar?

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 tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    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
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            ![FormName] = Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.Value
                            .Update
                        End With
                    End If
                End If
                    Next ctl
         Case Else
            With rst
                .AddNew
                ![DateTime] = datTimeCheck
                ![UserName] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
'                ![NewValue] = ctl.Value
'                ![FieldName] = ctl.ControlSource
                .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

 

[pbaldy]

I suspect it's not the code, but where you're calling it from. I'd suggest the before update event of the form.

 

[The_Doc_Man]

To do auditing, there are only two GOOD places for it. Paul's suggestion of BEFORE UPDATE event, but if you have code that can cancel the update, then EITHER you have to be careful that you don't audit something that won't happen -OR you can put the audit in the AFTER UPDATE event to record what just happened.

In the most recent case where I had this issue, I used BeforeUpdate as the place to test for canceling the update and AfterUpdate as the place where any auditing occurred. BUT that is a style choice, not a technical choice. I was using those events to keep the code limited and "pure" because it was a military system and they asked for everything to be relatively simple. So splitting the auditing away from the pre-update validation seemed the simplest thing IN THAT CASE!

 

[pbaldy]

I don't believe OldValue is still relevant/accurate in the after update event Doc.

 

[The_Doc_Man]

Oops, true Paul. My audit wasn't capturing .OldValue, only new values, so in my case it didn't matter. But looking more closely at the code, I see that .OldValue IS referenced.

bftsg - if you need .OldValue to be captured, you have to use BeforeUpdate. If you ever decided you didn't need it, the AfterUpdate event would be OK.

 

[pbaldy]

You'd have to change the test as well:

If Nz(ctl.Value) <> Nz(ctl.OldValue) Then

 

[pbaldy]

By the way, I'll make the same comment here I made on a similar thread yesterday:

I wouldn't open the recordset on the whole audit table. I don't use ADO much but I'd either use the argument that would open it for append only, or add a criteria to the SQL that won't return any records, like:

"SELECT * FROM tblAuditTrail WHERE 1=0"

or

"SELECT * FROM tblAuditTrail WHERE KeyFieldName = 0"