VBA & Macro Problems

How to Create an Audit Trail in Microsoft Access with VBA

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to create an audit trail to record field modifications and log the current user, but the existing macro fails to update the audit table properly.

Product: Microsoft Access
Device & OS: not provided
Scenario: Tracking record modifications and capturing the user who made the changes within a database.
Observed behavior: The Access macro does not insert an audit row when a field changes, and the VBA Environ function is difficult to implement directly within a macro to populate the ChangeBy field.

Before you start

Ensure you have an existing 'Audit' table created with matching data types for the fields you want to track, plus additional fields for the user name and timestamp. Verify you have 'Design View' access to the forms you intend to monitor.

Solution 1Recommended

Use Form's BeforeUpdate Event with an Append Query

Replace standard macros with a VBA BeforeUpdate event to accurately track when a record changes and log the user's details.

Using a standard macro for audit trails can be unreliable, especially when attempting to track the current user with system functions like Environ. Instead, leveraging a Form's BeforeUpdate event combined with a VBA-executed append query ensures that an audit row is inserted only when data actually changes.

This approach allows you to capture the original values before they are overwritten and use a custom GetUser function to securely log who made the modification.

Open the Form in Design View

Navigate to the form you want to track in your Access database, right-click its tab, and select 'Design View'. Open the Property Sheet.

Create the BeforeUpdate Event

In the Property Sheet, go to the 'Event' tab. Find the 'Before Update' property, click the ellipsis (...) button, and select 'Code Builder' to open the VBA editor.

Implement a Custom GetUser Function

In a standard VBA module, write a custom 'GetUser' function to retrieve the current Windows username, avoiding the limitations of using the Environ function directly in macros.

Write the VBA Audit Logic

Inside the BeforeUpdate event procedure, write VBA code to compare the '.OldValue' and '.Value' properties of your bound controls to detect if an actual change occurred.

Execute the Append Query

Use 'CurrentDb.Execute' within your VBA script to run a SQL INSERT statement. This statement should append the '.OldValue', the field name, the current timestamp, and the result of your GetUser function into your audit table.

Tip: If you are unsure how to structure the VBA code, searching for sample databases like 'ChangedRecordDemo.zip' online can provide an excellent working template for the BeforeUpdate event.

Free Microsoft Office alternative

Looking for a Lightweight Office Alternative?

While WPS Office does not include a relational database management tool like Microsoft Access, it is an exceptional, free alternative for your everyday document, spreadsheet, and presentation needs. Enjoy high compatibility with Microsoft Office formats in a lightweight, user-friendly suite.

1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
2. Manage Data in Spreadsheets: Open WPS Spreadsheets to organize, filter, and track your data as a flat-file alternative to relational databases.
3. Enable Developer Tools: Access the built-in VBA editor in WPS Spreadsheets to automate tasks and track cell changes using worksheet events.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Free and lightweight suite for everyday office tasks.WPS Spreadsheets supports VBA and macros for flat-file data tracking and automation.Seamless migration with a familiar, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the Environ function work well in Access macros?

The Environ function is a VBA function used to retrieve operating system environment variables (like the current username). It is often restricted or difficult to call directly from a standard Access macro due to sandbox security modes and syntax limitations in macro expressions.

What is the BeforeUpdate event in Access?

The BeforeUpdate event triggers right before changed data in a control or record is saved to the database. This allows developers to validate the new data, compare old and new values, or cancel the update entirely before any changes are committed.

How do I capture the previous value of a field in Access VBA?

You can use the .OldValue property of a bound control in VBA (for example, Me.ControlName.OldValue) to retrieve the original data from the database before the user made their current uncommitted modifications.

Excel & Data Problems