Well basically I am working on an MSAccess application and on the form where administrators will view and edit user data I want to be able to call an event with the old and new values of a field whenever it is changed. This function will add a record into the audit table to track changes.
I have no problem creating the query to add the entry to the audit table but I don't know where to place the function call. I've tried a few form level events so I don't have to go into each of the fifty fields and edit their onBlur events to check for a new value but have had no luck.
Any tips would be welcomed.
Microsoft has an article on this: How to create an audit trail of record changes in a form in Access 2000.
You can call your function in the After Insert,After Update and After Del Confirm events. These are kind of like triggers that fire after a record's been inserted, updated or deleted.
solution (1) would be to add an event handler in the "beforeUpdate" event. Remou's proposal is very interesting for that.
solution (2) would be to compare data in the recordset to data in the control in the afterUpdate event: for controls bound to fields in recordsets, there is allways a step where the value in the control differs from the one in the recordset. This is very easy when control sources are straight field names.
But I did not like the concept of auditing changes at the Form level: its results can be ambiguous as data changed at the form level might not be saved at the table level, either because form can be closed before the underlying recordset is updated, or because the SQL query is not sent to the server.
If your data update is made through SQL synatx, solution (3) would be then to store the "INSERT" or "UPDATE" strings sent from your Access app to your database server in a "transaction log".
