Access 2010 Audit Trail

[MHamid]

Hello,

I have been trying to get my audit trail function to work for a week now and it's not doing anything. I don't get an error message, it's just not adding the changes to the AuditTrail table.

I created the function module as shown below:

Option Compare Database
Option Explicit

Public Function AuditChanges(RecordID As String, UserAction As String)
    On Error GoTo AuditChanges_Err
    
    Dim DB As Database
    Dim rst As Recordset
    Dim clt As Control
    Dim UserLogin As String
    
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("select * from AuditTrail", adOpenDynamic)
    
    UserLogin = Environ("USERNAME")
    Select Case UserAction
        Case "New"
            With rst
                .AddNew
                ![DateTime] = Now()
                !UserName = UserLogin
                !FormName = Screen.ActiveForm.Name
                !RecordID = Screen.ActiveForm.Controls(RecordID).Value
                !Action = UserAction
                .Update
            End With
        
        Case "Delete"
            With rst
                .AddNew
                ![DateTime] = Now()
                !UserName = UserLogin
                !FormName = Screen.ActiveForm.Name
                !RecordID = Screen.ActiveForm.Controls(RecordID).Value
                !Action = UserAction
                .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
                            !RecordID = Screen.ActiveForm.Controls(RecordID).Value
                            !Action = UserAction
                            !FieldName = clt.ControlSource
                            !OldValue = clt.OldValue
                            !NewValue = clt.Value
                            .Update
                        End With
                    End If
                End If
            Next clt
End Select
rst.Close
DB.Close
Set rst = Nothing
Set DB = Nothing

AuditChanges_Err:
    MsgBox Err.Number & " : " & Err.Description, vbCritical, "ERROR!"
    Exit Function

End Function

I then created the code in my Data Entry Form under the Before Update event to add any new or edited data into the audit trail table.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Call AuditChanges("CaseNo", "New")
    Else
        Call AuditChanges("CaseNo", "Edit")
    End If
End Sub

I also added the following code in the After Delete Confirm event to keep track of deleted records in the audit trail.

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then Call AuditChanges("CaseNo", "DELETE")
End Sub

Am I missing something with the audit trail?
I have the AuditTrail table, I have the audit function, and I added the code to the Data Entry Form where I want the audit trail to occur.

I am not sure if this might help, but I do have buttons in my form to add, delete and edit records. But each button has a different code to add, delete, edit records in the MasterThrougput table.

I would appreciate some help if possible.


Thank you,
Miriam

 

[xenou]

I am not sure if this might help, but I do have buttons in my form to add, delete and edit records. But each button has a different code to add, delete, edit records in the MasterThrougput table.

Can you elaborate? What is this code?

 

[MHamid]

The code to add a record is:

Private Sub AddRecord_Click()

Dim strEndDate As String

If IsNull(Me.EndDate) Then
    strEndDate = "Null"
Else
    strEndDate = "'" & Me.EndDate & "'"
End If

CurrentDb.Execute "INSERT INTO MasterThroughput([SID], [Start Date], [End Date], [CaseNo], [Entity Count], [Referenced Entity #], [Regional Assist?], [Vendor Assist?], [Fully Referenced?], [LOB], [Comments])" & _
                "VALUES ('" & Me.SID & "', '" & Me.StartDate & "', " & strEndDate & ", '" & Me.Case & "', '" & Me.EntityCount & "', '" & Me.ReferencedEntity & "', '" & Me.RegionalAssist & "', '" & Me.VendorAssist & "', '" & Me.FullyReferenced & "', '" & Me.LOB & "', '" & Me.Comments & "');", dbFailOnError
MsgBox "Record Added Successfully", vbInformation, "SUCCESS!"

Me.EndDate = ""
Me.Case = ""
Me.EntityCount = ""
Me.ReferencedEntity = ""
Me.RegionalAssist = ""
Me.VendorAssist = ""
Me.FullyReferenced = ""
Me.LOB = ""
Me.Comments = ""
 
End Sub

The code to delete a record is:

Private Sub delete_Click()

    If MsgBox("Are you sure you want to delete?", vbYesNo) = vbYes Then
        CurrentDb.Execute "DELETE FROM MasterThroughput" & _
               " WHERE CaseNo =" & Me.Case
         MsgBox "Record Deleted Successfully", vbInformation, "SUCCESS!"
    End If

Me.EndDate = ""
Me.Case = ""
Me.EntityCount = ""
Me.ReferencedEntity = ""
Me.RegionalAssist = ""
Me.VendorAssist = ""
Me.FullyReferenced = ""
Me.LOB = ""
Me.Comments = ""

End Sub

The code to edit a record is (but this code is a work in progress as it dose not to exactly what I want it to do):

Private Sub EditData_Click()
Dim rs As Recordset
Dim varCaseNo As Variant

If IsNull(DLookup("CaseNo", "MasterThroughput", "Case='" & Me.Case.Value & "'")) Then
  MsgBox "Case does not exist ... Goodbye!", vbInformation, "Error!"
End If

Set rs = CurrentDb.OpenRecordset("MasterThroughput")
With rs
   .Edit
   .Fields("End Date") = Me.EndDate
   .Fields("Comments") = Me.Comments
   .Update
End With
MsgBox "Record Updated Successfully", vbInformation, "SUCCESS!"

exitHere:
rs.Close
Set rs = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

Please let me know if you need more information in order to help me out with this issue.

Thank you,
Miriam

 

[xenou]

Not sure but it seems to me you are bypassing the form events for update, delete, etc. by doing it yourself in code.
I'd put messages in the form events to see if they fire (or not):

Private Sub Form_AfterUpdate()
    MsgBox "Form After Update Fired!"
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    MsgBox "Form Before Update Fired!"
    '//More code
End Sub

If my hunch is correct, you don't see these messages so no events are ever firing to trigger your audit trail code.

 

[MHamid]

Hello,

I added the message boxes and I did not see either message pop-up.

 

[xenou]

Okay, so no form events firing for updates, deletes etc. because you have created your own code for that. You need to call your audit trail code from the buttons where you script your updates, deletes, etc.

 

[MHamid]

How do I do that?
would it be as simple as adding the call codes into by button codes?

 

[xenou]

Correct, just like that.

 

[Micron]

MHamid, this looks familiar - I think I helped you out with some of this before. For sure, there is no call to the function within any of the event code you posted so it will never run. Wondering if you realize you must pass parameters to that function (RecordID and UserAction) when you call it, and I don't see variables or values for those in any of that code. Post back if you need more help.