Form level event when data changes in MSAccess form?
Ok, I am mainly working on an MSAccess application and a form where admins will view and edit user data. I want to be able to fire an event with the old and new field values whenever it changes. This function will add an entry to the audit table to track changes.
I have no problem creating a query to add an entry to the audit table, but I don't know where to put the function call. I've tried several level events so I don't have to go into each of the fifty fields and edit their onBlur events to check the new value, but no luck.
Any advice would be appreciated.
source to share
Microsoft has an article about this: How to Create an Audit Trail of Form Record Changes in Access 2000 .
source to share
Solution (1) should be to add an event handler to the "beforeUpdate" event. Remou's proposal is very interesting for this.
solution (2) would be to compare the data in the recordset with the data in the control in the afterUpdate event: for controls bound to the fields of the recordset, there is always a step where the value in the control differs from the value in the records. It's very easy when the control sources are direct field names.
But I didn’t like the concept of auditing changes at the form level: the results could be ambiguous because data changed at the form level might not be saved at the table level, because the form might be closed before the underlying recordset is updated, or because, that the SQL query is not being sent to the server.
If your data update is through SQL syntax, solution (3) will then store the "INSERT" or "UPDATE" rows sent from your Access application to your database server in a "transaction log".
source to share