Access Forms Audit Trail

Audit Trail in Access Forms in 6 Steps

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:

Audit Trail in Access Forms

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.

Audit Trail in Access Forms

Audit Trail in Access Forms

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.

deletion of a record

That’s it! You can audit away.

Some more related Articles:

Form Subtotal Fields Master/Detail Forms NotInList Event Speed Up Forms
Unbound Form Change Textbox Height  Get Website Data Form Query Parameter