VBA & Macro Problems

How to Track All Access Form Changes in an Audit Trail Using VBA

Elise Williams Sep 25, 2026 871 views

Question details

The user needs a reliable VBA method to record all changes made in a Microsoft Access form, as default property comparisons fail to track certain control types.

How to Track All Access Form Changes in an Audit Trail Using VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Implementing a comprehensive audit trail for an Access form to log user modifications across various field types.
Observed behavior: The standard VBA audit method comparing Value and OldValue properties only captures changes in the first six fields and fails to track modifications in list boxes, check boxes, and combo boxes.

Before you start

Ensure your target Audit table is properly structured in Access with dedicated fields to receive the control name, previous value, new value, user ID, and timestamp before modifying your form's VBA code.

Solution 1Recommended

Compare Record Arrays in Form Events

Store the current record in an array during the form's Current event and store a second copy during BeforeUpdate. Compare the arrays to detect and record changes.

By bypassing the standard Value and OldValue properties for individual controls, this method reliably tracks modifications across all control types, including bound and unbound list boxes, check boxes, and combo boxes.

Open Form VBA Editor

Open your Microsoft Access database, right-click the target form in the Navigation Pane, select 'Design View', and click 'View Code' in the ribbon to open the VBA editor.

Initialize Arrays in the Current Event

In the Form_Current event procedure, write VBA code to loop through the record's fields and store their initial values in a module-level array.

Capture Updated Values Before Update

In the Form_BeforeUpdate event, populate a second array with the newly modified field values just before the user saves the record.

Compare Arrays and Log Changes

Iterate through both arrays to compare the values. If a difference is detected, execute an SQL INSERT statement to write the field name, old value, new value, user name, and current timestamp into your audit table.

Comprehensive Tracking: This method works equally well for bound and unbound controls, and it allows you to explicitly choose which form fields to include or exclude from your audit trail.

Solution 2

Compare Control Values on Enter and Exit Events

Capture a specific control's value right as the user enters it, and compare it with the value when the user exits to detect granular changes.

Free Microsoft Office alternative

Discover WPS Office as a Lightweight, Powerful Alternative

While Microsoft Access remains a specialized database tool, WPS Office provides a fully featured, free alternative for your everyday document, spreadsheet, and presentation tasks. It offers robust format compatibility and advanced features like VBA support for macros in Spreadsheets.

1. Download WPS Office: Go to the WPS Office official website and download the free installer for your operating system.
2. Install the Suite: Run the setup file and follow the straightforward prompts to install the software on your device.
3. Open and Edit Microsoft Formats: Launch WPS Office and directly open your existing .docx, .xlsx, or .pptx files without worrying about format distortion or data loss.

High compatibility with Microsoft Word, Excel, and PowerPoint formatsAdvanced VBA and Macro support in WPS Spreadsheet for task automationLightweight application that runs smoothly on Windows, Mac, and LinuxFamiliar, user-friendly interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the OldValue property track changes in Access combo boxes?

The OldValue property generally only stores the previous value for bound controls directly tied to a table field. For unbound controls or specific multi-select list boxes, VBA cannot accurately retrieve OldValue natively, necessitating custom array tracking.

How can I include the user's name in my Access VBA audit trail?

You can capture the current logged-in Windows user by using the Environ("USERNAME") function in your VBA script, then pass that string into your SQL INSERT statement for the audit table.

Does this array comparison method work for continuous forms?

Yes, the Form_Current and BeforeUpdate events fire for the specific record currently being edited. As long as your module-level arrays are properly re-initialized when navigating between records, this approach safely handles continuous forms.

Excel & Data Problems