Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Forums  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.

Did you know you don't need to move to SQL/Server to run and manage your Access database on the net. There are many alternative solutions. Among them are Amazon's AWS cloud services and various hosting services in nearly every state.

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
                    ![FormName] = Screen.ActiveForm.Name
                    ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                    ![FieldName] = ctl.ControlSource
                    ![OldValue] = ctl.OldValue
                    ![NewValue] = ctl.Value
                    ![UserID] = strUserID
                    ![DateTime] = datTimeCheck
               End With
                    End If
                End If
            Next ctl
        Case Else
            With rst
                ![DateTime] = datTimeCheck
                ![UserID] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = useraction
                ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
            End With
    End Select
    On Error Resume Next
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
    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")
        Call AuditChanges("ContactID", "EDIT")
    End If
    Exit Sub
    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
    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:

Document Revision Control Application

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