How to log changes in a database

[mlai08]

I have an employee table updated through an input form and want to track any changes made by users. I expect to store this info in a log table including what change has been made on what field, by who and the date/time when that happens.

I could append this info to the log table when the employee record is updated. How should I capture the changes on the record? Do I have to loop through all the form controls to find the Dirty ones?

Is there a better way to track data change in Access?

 

[boblarson]

I've found this Audit Trail to be very easy to implement.

 

[missinglinq]

What you're describing is an Audit Log. It's not something for the faint of heart, but Allen Browne covers the subject in depth here:

http://allenbrowne.com/AppAudit.html

 

[mlai08]

Thanks guys for the quick and helpful response.