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.
Blue Claw Database Design is your premier source of Microsoft Access programming and database support. Both small business and department-level databases. Desktop installation as well as cloud/internet based solutions.
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:
In this example we will create a simple audit trail for our table
M_Equip_Meter_Reads. And, specifically for the field Meter_Value.
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.