Create Audit Trail Database

TGoldenEye

How to Create an Audit Trail Database

Audit Trail is a system that traces the detailed transactions. It is a record of the changes that have been made to a database or file. Audit Trail Database helps you keep track of all changes in database such as adding, updating, or deleting record or data on each single field and on each form.

The function of AuditTrail will be a little bit different if form is used under the Navigation Form or the Navigation form under the Navigation form. So I have a separate file for the Navigation form.

There are two MS Access Files in this download zip:

  1. Create AuditTrail Database.accdb
  2. AuditTrail Navigation under Navigation.accdb

Create AuditTrail Database.accdb

audit

Highlight functions or features in this sample file include:

#1 Learn how to create Audit Trail table from example.

create audit table

#2 Learn how to create forms and apply the Audit Trail function to form.

create audit form

#3 Implement a Login Form to the audit trail database and capture the login user from the Login Form to the Audit Trail table.

audit login

#4 Learn how to create a search form or filter the audit trail data by the login user or user action.

filter audit trail

#5 There are two example reports for the Audit Trail database. You can learn how to use a query that created from two table as a row source or the Deleted Customer Report.

audit trail reportdelete report

# 6 It can capture the change in the sub-form. The code will be a little different from a regular standalone form.

  subform

#7 It also can capture the change on the datasheet form on the Navigation Form.

Navigation form

Modules

audit functionThere are two modules that contain some public functions which are used in the VB in forms.

For example, the Audit Trail function and GetUser function are located here and can be used on any form.

Download file contains:

5 tables, 7 Forms, 1 Query, 2 Report and 2 Modules

audit trail table form

AuditTrail Navigation under Navigation.accdb

The function of AuditTrail will be a little bit different for the form that used under the Navigation Form or the Navigation form under the Navigation form. The reference of form under the Navigation form is a little tricky.

Highlight functions or features in this sample file include:

#1 Audit Form and Subform under the Navigation Form

  • Customer_DS datasheet form is a Subform within a Customer_Type form
  • The Customer_Type form is a target form under the 1st Navigation Form, Customer Type under One Nav tab
  • FilePath is Navigation Form>Customer_Type
  • And FilePath is Navigation Form>Customer_Type>Customer_DS
  • We can record the change (AuditTrial) on Customer_Type and Customer_DS form

design under one nav  one nav

#2 Audit Form and Subform under the 2nd Navigation Form which is located under the 1st Navigation Form

  • Customer_DS Nav2 datasheet form is a Subform within a Customer_Type Nav form
  • The Customer_Type Nav form is a target form under the 2st Navigation Form (Nav2), Customer Type under Two Nav tab
  • The 2nd Navigation Form (Nav2 Form) is located under the 1st Navigation Form (Navigation Form), Home tab
  • FilePath is Navigation Form>Nav2 >Customer_Type Nav
  • And FilePath is Navigation Form>Nav2 >Customer_Type Nav>Customer_DS Nav2
  • We can record the change (AuditTrial) on Customer_Type Nav and Customer_DS Nav2 form

Design under two nav

under two nav

The AuditTrail  function is located under each form due to the difference of each function.

audit code ds

Download file contains:

4 tables, 7 Forms, 1 Query and 4 Modules

table list audit

Audit Trail Video Preview

Related posts