Dirty, anyone...?

[Crispy]

...not as interesting as it sounds.

I am looking to use the Dirty function in Access 97 to create an audit log within my database. What I would like to do is the following:
In my Form (Form1), which is based on Table1, I would like to put an after update event on selected fields to do the following. If the field has been altered in any way, I I want to create a new record in Table2 recording the date/time of the change, the username, the old value and the new value of the field. I don't want it to be apparent to the user that this procedure has taken place.

I am way over my head here but I'm sure this can be done. Do any of you VBA experts have any ideas for the code? (I'm a VBA virgin so be gentle!)

 

[ColinEssex]

The "Dirty function" applies to the form as a whole, therefore your selected fields may not have been changed.

You will need to use the "OldValue" function. If you have a close button or addNew button, I would put it there to check if the value of any or all of your selected fields has changed (that will be the last action on that screen).

If you need any more help with code please post with details

Col

 

[Pat Hartman]

The AfterUpdate event (control or form) ONLY fires if the particular control/form has been changed so you don't need to check the dirty property. You just need to put your code in the correct event. I would not however, save any records based on the changes to individual fields since they may never actually be applied to the table if the user cancels the update. Use the AfterUpdate event of the form to actually write your records. You can compare the current value of a field to it's OldValue in the BeforeUpdate event to see which fields will updated.