tracking record changes in a form

[Steve K]

I so far found only one example of code in the Knowledge
Base that allows tracking of changes of records on a
form,http://support.microsoft.com/default.aspx?scid=kb;en-
us;183792&Product=acc97 This basically uses one Memo
field to store all the data changed in all the fields on
the form. It works well, but it's limited by not being
able to query/filter through this stored data later on.

Are there alternative methods that people have developed
that stores the data in separate or distinct fields vs one
field?

 

[Nikos Yannacopoulos]

I can think of two alternatives:

1)If you have certain fields that may be edited
frequently, a good way would be to create a separate table
for each, bound with a one-to-many relationship to the
main table, and store the pertinent information in those
tables, where you could also add fields to hold date/time
and/or user who made the change. In this case you can
remove the original field(s) from the main table.

2)If changes occurr erratically in all fields, and your
table is a big one in terms of number of fields, you might
consider storing all the current data in the main table as
you currently do, and create one table to hold all the
changes. This new table would need a foreign key field
bound to the primary key in your main table, plus a field
to hold the field name in the main table that was changed,
and another one for the previous value (you could also add
date/time and user as above). This would require some
fairly advanced coding!

Nikos

 

[Tim Ferguson]

Are there alternative methods that people have developed
that stores the data in separate or distinct fields vs one
field?

One standard method is to trap each field's BeforeUpdate event, and write
to an audit table with fields like:

EditDate
User
TableName
FieldName
OldValue
NewValue

but bear in mind that you cannot catch updates made by SQL queries, or by
VBA, nor by editing the datasheet, or in Excel, or in a VB front end, or by
a form where you forgot to put the auditing code or....

If you really need a solid audit trail, you can use a proper DBMS server
like SQL server or MSDE etc.

All the best


Tim F