Logging Changes

  • Thread starter Thread starter Kath via AccessMonster.com
  • Start date Start date
K

Kath via AccessMonster.com

Hello all,
If I want to keep a history (including user names - which I have code for)
that tracks changes done to data, how is this best achieved?
 
You will need an audit table or tables to keep track of the changes. I say
table or tables, because depending on how sophisticated you want to get with
this, it could take 2 tables. The first table would be the Change table.
Possible fields are:

ChangePky - Auto Number
ChangeDate - Should include time
ChangeUser
ChangeTable - The table name that changed

Then for the Change Details table
ChangeDetailPky - AutoNumber
ChangeDetailFky - Links to ChangePky in Change Table
ChangeDetailFieldName
ChangeDetailDataType
ChangeDetailOldValue
ChangeDetailNewValue

Since we have multiple data types, converting everything to text is
necessary. Carry the field name and data type so in can be converted back to
its original format if necessary.

Now, to make it work, we need a module that will do the work for us. In
that module, you will have to create the records for the audit. Then you
will have to call the module anytime you update a record in the tables you
want to audit. The best place to do this would be in the Before Update event
of the table's form. It should not execute if you set Cancel = True at any
time during the Before Update event. So be sure you do not call the routine
if you cancel the update.
The reason to put it here is that you can capture each control's OldVaule
property for the record. Once the record is updated and you move to another
record, that value is lost.
So then you have to spin through all the updateable controls on your form to
determine the data type of the underlying field, get the old value, the
current value, and put them all in the detail table.

Good Luck.
 
Thanks for the info. :) Would you be able to just show an example (using
maybe one field)? I am not sure exactly how to do all you said. (If not, that
is ok...I can play with it.)
 
Sorry, Kath, I don't have the code with me. It is at home (somewhere). I
have a hot project going on today, so I can't really take the time to write
some sample air code. I will see if I can find the old module or before the
week is out, I can send some samples.

Thanks for your patience and understanding.
 
Back
Top