Tracking changes to individual fields of a record

[ALSav]

Can anyone tell me if it is possible to track the changes to the individual fields of a record held on an Access database.
For example if there is a 'Personal Details' table with fields Name, Address, Telephone, Email etc. would it be possible to record the fact that the telephone number only, changed on a given date but the email address was updated on a different date.

Would this involve creating a log file and running searches on it?

Any help appreciated

 

[MichaelRed]

In Ms. Access, this is fairly straight forward. There is an "IsDirty" property which is set whenever the content of a specific control is 'changed'. You can easily check all of the bound controls on a form in the BeforeUpdate event of each form, and YES, create a log file to record the info you want.

The VB "IsDirty" property appears to be somewhat different, at least from a quick glance at the help file.

I THINK you could simulate the Ms. Access approach by Using the GotFocus and LostFocus events. Basically, the GotFocus Event would need to 'record' the original value of the (selected) controls, while the LostFocus event would need to compare the current value to the original value, and record any diff in the log file.

There will be numerous 'cases' and exceptions which need to be handled by any such procedure, such as figuring out how/when to reset the 'original' value (should only do this when the app changes the "record" of the form) and wheather the record was actually written to the db ... etc.

Depending on the complexity of the db/app, you might also consider setting up UDT's corresponding to the tables which may be changed by your app. Whenever you go to write a record, fill them with the current record and compare on a field by field basis, and write the diff to the log.

In any case, you need to be aware that thiese processes will (can) only capture the changes made through the app. It is easy/trivial to go directly to the db and add/edit/delete records w/o having any record of the changes.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[steveblum]

In a full blown database, such as SQL-Server or Oracle, you would do this by using Update triggers. I haven't worked with Access since Access 97, but to my knowledge it doesn't offer triggers. If Access 2000 does, this would be the way to do it. You would execute code in the trigger to write a log entry. If it doesn't have triggers, which is likely, your best bet will be Michael's advice.