How to Detect Changes to Bound and Unbound Controls in Access VBA
Question details
The user needs a reliable method to detect when changes are made to both bound and unbound form controls in Access VBA.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Tracking user edits on an Access form to trigger saves or customized validation logic.
- Observed behavior
- The built-in Form.Dirty property only captures unsaved changes to bound controls, failing to recognize edits made to unbound controls.
Ensure you have access to the VBA editor in Microsoft Access and are familiar with basic form event procedures such as AfterUpdate and On Current.
Use a Module-Level Boolean Variable for Unbound Controls
Manually track the state of unbound controls using a Boolean variable, since the Form.Dirty property only works for controls bound to a data source.
Because unbound controls are not connected to the underlying recordset, Microsoft Access does not register changes to them via the standard Dirty property. To bypass this, you must track edits manually.
Open the VBA module for your form. At the very top (in the Declarations section), declare a module-level variable by typing: Dim bUnboundChanged As Boolean
Select your unbound control from the object dropdown. In its AfterUpdate event procedure, set the variable to true: bUnboundChanged = True
To ensure accuracy when navigating between records, open the form's On Current event procedure and reset the variable by typing: bUnboundChanged = False
Use the Form.Dirty Property for Bound Controls
Use the built-in dirty property to detect if any bound fields linked directly to your underlying query or table have been modified.
Looking for a Lightweight Alternative to Microsoft Office?
While Microsoft Access is a specialized database tool, WPS Office provides a comprehensive, free, and highly compatible suite for your word processing, spreadsheet, and presentation needs, complete with advanced VBA support for automation.
- 1. Download WPS Office: Visit the official WPS website and click on 'Free Download' to get the installer for your operating system.
- 2. Install the Suite: Run the downloaded file and follow the on-screen instructions to quickly install the software.
- 3. Open Your Office Files: Launch WPS Office and directly open your existing Microsoft Office files. Your formats, formulas, and VBA macros will be seamlessly supported.
Frequently Asked Questions
Why doesn't the Form.Dirty property work on unbound controls?
Form.Dirty is specifically designed by Microsoft Access to monitor changes made to the underlying recordset (tables or queries). Unbound controls are not connected to this data source, so changes to them bypass the Dirty property trigger entirely.
Can changing a bound control's value via VBA trigger the Dirty property?
It can be inconsistent. The Dirty property is reliably set when a user manually modifies the control's Text property on the interface. Modifying the Value property programmatically via VBA might not always set the form to Dirty immediately.
How do I reset the Form.Dirty property without saving?
You can cancel the changes and reset the Dirty property by using the 'Me.Undo' command in your VBA code. This reverts all edits made to bound controls on the current record and automatically sets Me.Dirty back to False.
