Novice Programming Question- Best Practices for Logging Changes to records/Recording Missing Info

[sjs94704]

OK, I have a typical Customer Details form with various fields, subforms, etc. I have a routine called 'LogIt' which creates log entries into a table called LogT. These log entries track user movements throught the database.



Basically, I was thinking that the best thing to do to record any changes to a record, I need proper code in the OnChange event. I'm looking for some direction on how to potentially write some code to accomplish this.

I am simply trying to avoid creating an AfterUpdate event for absolutely every single field!!!!!

And, this may be a tall order, but I would also be interested to know if anybody has any suggestions as to how to properly alert the user that a customer is missing information. I'm interested in developing say a task menu where entries get made as the database is being used to help the users of the database know what issues need attention to help them properly maintain the database. As these tasks are handled, they are either marked as completed, or just deletes it.

I don't know, anyone have any thoughts on this ??

Thanks for your time and input.
Steven

[CJ_London]

you would run the code on the before update of the form, not each control - otherwise if a user makes a change -you update the log-then user hits esc to abort the change

with regards the code, there are plenty of examples out there for different ways of doing it - which is right for you is down to you. Try googling 'access log changes' or 'audit log' or similar to find a number of links

[CJ_London]

if anybody has any suggestions as to how to properly alert the user that a customer is missing information

you need to clarify what you mean - do you mean at the time the user is updating a record, or some sort of 'to do' list which highlights records which have missing or incomplete data?

For the former, use the form before update event - you'll find plenty of examples in this forum and others for the code

For the latter, create a query (called say qryAudit) which lists all records with missing data and on say your navigation form have an unbound control with conditional formatting to set the back colour to red if 'Dcount("*","qryAQudit")<>0. You could then put some code on the click event to open the query in a form

[NTC]

so this is an issue that has to be approached with some design common sense; one can easily time stamp that a record has been changed (and by who if there is a log in or Active directory); and one practical technique is to rely upon archived back ups if one needs to see the prior data.

but to attempt this for each field in the record would require an edit flag for every field - which is completely impractical - and so if you don't want to rely on archived back ups then the alternative is that one must write the entire record to a log table before allowing the edit.... and so now you have a serious overhead of activity going on in the background - particularly in a multi user database. this should not be undertaken lightly.

I have done plenty of work, particularly with insurance companies, where one must definitely retain the prior version of data and not over write - but this is not change logging per se. In this case one must have a management process whereby one 'creates a new record', apply a 'record end' time stamp to the existing record and duplicate the data with a new 'record start' time stamp and a revision number that increments up; so they have the history at hand of policy changes. This approach is quite common in higher end applications that need it.