Tracking changes

Topic

New Reply

Will access track all changes in a table? We are going to run our backlog in Access 2000. Now we have 15 fields for each record. What we want is any time any of the fields is changed individually or as a group or the whole record is deleted, we would like a copy of the entire record before and after to be appended to another table with the current date added.

Currently we are running our backlog in Excel using macros, but it has gradually gotten complicated. I am the only person to understand the Excel macros and I think Access is a much better platform for what I am doing. I have it pretty well done except for this area.

Reply | Quote

Replies

What you will need to look into is: .OldValue

HTH

Reply | Quote

No, Access will not track changes in and of itself, and it won’t do it at all from tables. If you want to do this, you will have to use a form with controls bound to the underlying table’s fields.

In the BeforeUpdate event of the form, you would have a routine to loop through the controls and assign the OldValue property of each control to a separate module level variable (you would use variables rather than writing directly to the log table because they might cancel the update).

Then in the AfterUpdate event of the form, you would compare the variable values with the control values to make sure there was actually a change. If there was, you would either open a recordset and use AddNew to add a new record, populating it with the values in the variables and then adding another and populating it with the new values, or you could create the SQL for an append query for each set of values and execute it for each to add the records that way.

Reply | Quote

Thanks
I really appreciate it. You will probley hear from me later as I try to write it. It sounds just what I need.

John

Reply | Quote