Database record changes - How to audit?

I have a table named tblAudit.  In this table in addition to an auto-number ID field are 7 fields.  They are:

EditDate, User, RecordID, SourceTable, SourceField, BeforeValue, and AfterValue.

Any time a change is made be it edit, addition, or deletion is made I need the audit table to have a record made to it capturing the values.  How can I do this?  Do I need some code for every form?  Or a module that works for every change?

What would to code look like?  I've never done this before.

--Steve
SteveL13Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you're using 2010 or later you can use the new Data macros. Scott Gem shows how to do this here: https://scottgem.wordpress.com/2012/10/18/audit-trail-using-data-macros-2/
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You would add in some code and hook it up in every form.  See:

http://allenbrowne.com/appaudit.html

Bounce back if you have any questions (I'm sure you will).

Jim.
0
 
SteveL13Author Commented:
Wow!  Lots to absorb.  Back to you after I review and see what I can do with it all.  Thanks so far.
0
 
SteveL13Author Commented:
Scott,

I like your suggestion.  Just a couple of comments:

When adding a new record I get a record in the tblAuditTrail.  But I wish it would capture the value I entered in each field for FieldName and NewValue.  It isn't doing that.

When deleting a record I wish it would capture the record ID that was deleted.  It isn't doing that.

Can the code be altered to do these things?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can the code be altered to do these things?
You haven't posted any code or screenshots, so the only way I can answer that is "Maybe" ...
But I wish it would capture the value I entered in each field for FieldName and NewValue.  It isn't doing that.
It's up to you to capture those values if you want. You'd need to use the "SetValue" action for each field that you want to capture.
When deleting a record I wish it would capture the record ID that was deleted.  It isn't doing that.
Data Macros offer a BeforeDelete and AFterDelete action, so you could probably write to the audit log in the BeforeDelete event.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.