How to Capture Old Access Form Values Before Updating a Record
Question details
The user needs to record the previous values of specific bound controls into an audit table before saving changes to a form.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Tracking historical data changes in an Access database by capturing old form values upon modification.
- Observed behavior
- Whenever a user modifies fields such as MachSerialNo, ProjectAssetNo, or ProjectLocation in the MachHist form, the old values must be automatically inserted into the TblMachDataChange table along with the current FormNo.
Ensure you have a recent backup of your Access database before modifying VBA code, and verify that the 'TblMachDataChange' table is correctly structured to receive the historical data fields.
Use the Form's BeforeUpdate Event with VBA
The most robust and standard method is to trigger a VBA script during the form's BeforeUpdate event to compare current values against their .OldValue properties.
The BeforeUpdate event is the ideal place for data validation and auditing. Because the record has not been saved to the underlying table yet, Access still retains the original data in the .OldValue property of bound controls.
Right-click the 'MachHist' form in your Access Navigation Pane and select 'Design View'.
Open the Property Sheet (press F4), click on the 'Event' tab, find 'Before Update', choose '[Event Procedure]' from the dropdown, and click the builder button (...) to open the VBA editor.
In the VBA editor, write conditional statements to compare `Me.MachSerialNo.Value` with `Me.MachSerialNo.OldValue`. Repeat this logic for `ProjectAssetNo` and `ProjectLocation` to track whether any value actually changed.
If changes are detected, build a SQL INSERT string that maps the `.OldValue` of each field to the corresponding columns in `TblMachDataChange`, ensuring you include `Me.FormNo`.
Run the SQL command using `CurrentDb.Execute strSQL, dbFailOnError` to safely insert the historical record before the new data is committed to the database.
Store Original Values in the Tag Property
An alternative approach is to use the form's Current event to store initial values, which can then be compared upon updating.
Looking for a Lightweight Microsoft Office Alternative?
While WPS Office does not include a database application like Access, it provides powerful, free, and highly compatible alternatives for Word, Excel, and PowerPoint. If you want to streamline your document, spreadsheet, and presentation tasks without heavy subscription fees, WPS Office is an excellent choice.
Frequently Asked Questions
What is the difference between .Value and .OldValue in Access VBA?
The `.Value` property represents the current data entered in the control, even if it hasn't been saved yet. The `.OldValue` property retains the original data from the bound table field before the user started editing the current record.
Why use the BeforeUpdate event instead of AfterUpdate for capturing old values?
The BeforeUpdate event triggers before the database commits changes to the table. This allows you to safely access the `.OldValue` property. In the AfterUpdate event, the old values have already been overwritten and are no longer accessible.
How do I handle Null values when building the SQL INSERT statement?
You can use the `Nz()` function in VBA to convert Null values into empty strings (`""`) or zero, preventing errors when constructing your SQL string. Alternatively, explicitly check for `IsNull()` before appending the value.
What does dbFailOnError do in CurrentDb.Execute?
Adding the `dbFailOnError` option ensures that if the SQL INSERT statement fails (e.g., due to a data type mismatch or missing table), VBA will generate a run-time error rather than failing silently, which helps you easily debug the issue.
