Table events were added the Microsoft Access with version 2010. These events provide a database-wide method to perform procedures in one central location. Therefore for many events you don’t need to put them in the form code which may have to be called as a procedure from several forms.
The only drawback that we see with the table level events is that you must code them using the Macro language. Note that your database must be in .accdb format and not in .mdb format!
Here is an image of the table design screen for table events:
Here is the event macro for a little audit trail on the Meter Value field:
Access monitors when the Meter_Value field has changed as part of the cascading events in the system. Once the value changes the database macro sets up the audit fields with the values needed. The source of the data for each of these fields varies and includes fixed text values, such as the table name “M_Equip_Meter_Reads”, a special field value which is the field value before it gets updated ( [Old] ), also a special user define function which determines the current user on the computer. Here is the function code:
I tried putting Environ function directly in the macro but it didn’t work.
Here is the table M_Equip_Meter_Reads that we are going to update:
When we change a meter value in the table it generates an audit record in the audit table showing the original (old) value and the new value:
We have recently create an example and tutorial on how to create an audit trail in Access Forms. See out forms based audit trail.
MS Access 2003
Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016