Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Form Programming Tutorial
Audit Trail







Form Subtotal Fields
Master/Detail Forms
NotInList Event
Speed Up Forms
Unbound Form
Change Textbox Height
Get Website Data
Access Audit Trail

Form Query Parameter



Audit Trail in Access Forms in 6 Steps



Our programming team has 15 members containing a wide variety of technical database knowledge and programming experience. Experts in small business management and scientific database programming.

Do you and your clients need access to data on the Internet? We can create a secure system to match your requirements perfectly.


Audit trails are useful tools (and sometimes required by law) to track data changes from your forms. Programmers often debate which is better, table level auditing or form level auditing. Personally I find table level auditing takes longer to set up. But either way works just fine.  Below is a simple example of creating an audit trail on a Microsoft Access form.

First create your Audit Table (call it tblAuditTrail)

 
ChangeID Autonumber
DateTime Date/Time
UserID Short text
FormName Short text
FieldName Short text
OldValue Short text
NewValue Short text
Action Short text
RecordID Short text
 

2.       Then create a new module, and place this code into it:
 
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 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
                    ![FormName] = Screen.ActiveForm.Name
                    ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                    ![FieldName] = ctl.ControlSource
                    ![OldValue] = ctl.OldValue
                    ![NewValue] = ctl.Value
                    ![UserID] = strUserID
                    ![DateTime] = datTimeCheck
                    .Update
               End With
                    End If
                End If
            Next ctl
        Case Else
            With rst
                .AddNew
                ![DateTime] = datTimeCheck
                ![UserID] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = useraction
                ![RecordID] = 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
 
3.       Check your references from the Tools menu and be sure to have these selected: 

 Save and close the module.

 4.       Next, open your form in design view and choose the fields you want to audit. Open the property sheet and scroll down to the Tag property (it’s at the bottom) and type Audit. All fields with the audit tag will be audited.

 

5.       Behind the forms beforeupdate property, put this code:
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
On error goto errhandler
 
    If Me.NewRecord Then
        Call AuditChanges("ContactID", "NEW")
    Else
        Call AuditChanges("ContactID", "EDIT")
    End If
    Exit Sub
 
errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
           VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
 
End Sub
♫NOTE: Where I have ContactID you would change that to whatever your key field is (EmployeeID, RoomID, etc)
 
6.       And behind the forms After Delete Confirm, add this code:
 
Private Sub Form_AfterDelConfirm(Status As Integer)
On error goto errhandler
    If Status = acDeleteOK Then Call AuditChanges("ContactID", "DELETE")
Exit Sub
 
errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
           VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
 
End Sub
 
This is triggered when the user confirms the deletion of a record.

 
That’s it! You can audit away.









A Blue Claw Software Design Template:

Asbestos Survey Project Database
 








A Blue Claw Database Design Article:

How To Create Faster Access Databases
 









Microsoft Office Forms:
 MS Access 2003
 Access 2007
 Access 2010
 Access 2013




Contact Information

Copyright 2000-2017 Blue Claw Database Design