Change Log Query

[Tblqryfrmrpt]

Hi everyone,

I'm trying to create a "Change Log" type of table based on my append and update queries. I have two tables, tblHistorical and tblDaily (data dump). They have the same exact headers. Each day, I have an append and an update query that will append/update the information in tblHistorical from tblDaily.

My problem is how to use a query to build/update another table (tblChangeLog) that will list the pk, a few other identification fields for readability, the field that was updated, old value and new value and finally update date. If nothing has changed for that record, it should not be listed in that table. I've looked at a lot of examples online and I can't find anything that is similar to my issue. The only examples I see are audit trails for when a user changes information in a table using events.

Any ideas?

Thanks

 

[Guus2005]

Use a timestamp when a record was changed. Each time a record was changed change the timestamp for that record. In the end you can easily see what record was changed.
If you store the complete record is anything was changed in that record, it doesn't matter what was changed. Simply copy the record to the historical table. This method is simple and fast.

If you only want to store the changes of the record, it's a different ballgame.
Best way to do this is detect the change before the record was updated (BeforeUpdate event in a bound form) or track the change in the save buttons Onclick event when you are using an unbound form. This method is more precise, takes more time to develop and is more difficult to rollback the next day.

HTH

 

[Tblqryfrmrpt]

Hi Guus,

Thank you for your reply. I was looking at trying to use BeforeUpdate and AfterUpdate to track changes before, but I was under the impression that this only worked when you're talking about controls. I didn't think it would capture changes from an update query.

I'm trying to look at the before information before the update query and then the after the query was run and if there were any changes, put the pk, field, before and after value into the ChangeLog table. Does this make more sense?

Thanks

 

[Guus2005]

Yes, that makes sense and it is the way to go but if your a rookie i am not sure you can pull it off.

Try it, stick with it and when you need help, just post another question!

Share & Enjoy!