X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Audit Table / Best Method, Access 2010    
post May 12 2017, 04:36 AM

Posts: 609
Joined: 11-January 09
From: UK

Hi All

I'm getting an audit table ready for a master table; so when a user:
(1) loads a new record in the master table (via form in "data entry mode set = yes") it also gets 'added/duplicated' to the master_AuditTable. So for this i would imagine an event on "save" or "close" of form to insert into master_AuditTable (please advise if there is a better way to do it).
(2) creates an update via a form (where "data entry mode set = "no") - when a single field within that record changes - i need to duplicate the entire record as a new record in the master_AuditTable.

What is the best method for dealing with this? Is there a way to identify whether any of the fields on a form has changed, and then if so - add a new row to the master_AuditTable to capture every field (i.e. copy the whole record and put it into AuditTable)...

Go to the top of the page
post May 12 2017, 04:48 AM

UtterAccess VIP
Posts: 7,004
Joined: 30-June 11

Did you check the code archive?


Allen browne is another reliable source, see: http://allenbrowne.com/AppAudit.html
Go to the top of the page
post May 12 2017, 07:30 AM

UtterAccess VIP / UA Clown
Posts: 32,217
Joined: 21-January 04
From: LI, NY

That's not what an audit table does. An Audit table records changes to a record. Generally it records the Table, field, date/time, RecordID, old/new values and who made the change.

See my blog on using Data macros for an audit table.
Go to the top of the page
post Oct 28 2019, 06:56 AM

Posts: 7,115
Joined: 22-December 10
From: England

It doesn't sound to me like the way to do this at all.

Simply duplicating an entire record doesn't tell you what the change was.

I feel that the purpose of an audit is to tel you what has changes were, not just to save the whole record and say "this record is different to what it was"

What I normally do is choose the fields I'm interested in, and set up a table to record ( as already said by ScottGem)
The key of the audit record *Autinumber, incrementing, no duplicates)
The key of the record changed,
Field name
old value
new value
who changed it (I use environ(Username), but some people don't like that method in case a user may be savvy and devious enough to spoof it.)
date and time changed
date and time record was added. (using the default field value in the table definition)

This means the you can easily display the audit records in the order the changes were made

In terms of determining if a field was changed, I save the original value to a variable on entering the form.

On leaving the form, I then compare the current value of each of my interested field with the original, and write to the audit trail as appropriate.

How are you deciding if a record is changed? - The dirty flag is not reliable.

Go to the top of the page
post Dec 4 2019, 01:30 AM

Posts: 1
Joined: 4-December 19

It is very difficult for us to get access for forms. Here are some simple steps which helps you and which tells you how can you use best assignment writers for finding forms. I would like to show you more because these are really simple steps and you get them in very quick way.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    10th December 2019 - 08:28 AM