Best way to detect and log record changes (1 Viewer)

[hk1]

This is more of a theoretical question since it will generally be impractical to implement.

Let's say I have a form that's bound directly to a table and the table has about 25 fields. Now I'd like to detect when the user makes a change to a record and simply log the change. For example, if they change the Cost or the Retail Price, I'd like to log that price change.

I might log the change made in a table or a text file. I'm not concerned about that aspect. I'm interested in how you would recommend going about detecting the change.

Normally I would probably use a variable to store the value of Cost using the Form's OnCurrent event and then check the value of cost during the BeforeUpdate or AfterUpdate event for the form. Since I'd like to do this for all fields, I'm wondering if there is a cleaner solution, maybe one that uses an array.

 

[SOS]

I use this Audit Trail (very simple to implement).

 

[hk1]

I installed that Audit Trail thing and modified it a tiny bit. Looks like it's going to work for what I want it for. Thanks a lot.

 

[SOS]

Cool, glad to hear.

 

[accesslearner]

I am not sure if i can continue this post or use new one.

I used the audit trail mentioned and it worked.

the routine passes the uniqID_feild and uniqID as parameters.

is there anyway i can get the value of the id instead of a number

For eg.

say in my contacts table i have

contactID as the uniqID which is passed and the UniqID - ContactID which is 1 or 2 based on the record is passed.

i want to see the name of the person too

eg. contactID 1 & Name Sandy
ContactID 2 & Name Harry
how to do this.

and to disable the prompts.

 

[hk1]

To disable the prompts you just need to comment out the line that prompts the user to fill in a reason. It should look something like this:

gsReason = InputBox("Reason for change(s)?", "Reason for change(s)?")

 

[accesslearner]

Thanks i got that part. I guess what i was trying to ask was is there any way i maybe can Concatenate the uniqID with the value it referes to in the argument. So that at all times i can track if it is company than the company name, if it is contact the contact name where the changes are being made.

thanks for reading this post

 

[hk1]

If you have the Unique ID in there you shouldn't really need the name. You can always make a query that will link your contacts table to the audit trail table and show you the name or company name for the contact.

 

[accesslearner]

Thank you for your response. I have used a query to display the value something that never occured to me. I had one more thing that i need to deal with. When we add a new record the uniqid uniqID feild gets saved and displayed in the log if i require the new value to be stored how can i acheive it in the code.
thanks once again

 

[hk1]

What new value are you talking about. The new UniqueID value?

Usually this audit trail function makes one entry for every field that changed. Does it not show any changes for you? Or does it just not show changes if you change the UniqueID value?

 

[accesslearner]

When a new record is entered a value gets stored and the id generated. before that the value is null.

in this code when the action is record deleted it highlights all the values del in delvalues
if i want the same when the action is new record and want to show the new records how to do it. right now the values are not stored in previous value for a new record because the first time it would be null and when i make a change to the new record than both prev value and new value is shown. i understand why it is blank but is there a way to display the values of the new record once i added it in the form. i have attached a image to show too.

when i update a record the changes are shown
when i delete the record the changes are shown and values are set to null
when i insert a new record can i store the values and display it in the audit log the first time the record is inserted.

 

[hk1]

I think you will have to customize the Audit Trail functions to get this functionality. I did change mine so that the audit trail function doesn't actually do the deleting of the record.

There's a section towards the top of your Audit Trail function that handles New Records differently. You could try commenting that out and see if it saves individual values. I have never tried it.