Advice need to keep copy of updated record 1

[tigersden]

Hi,
What would be the best way of doing this:
A tbale needs to be allowed to be dited & I need to keep a copy of data pre edited, I am thinking along either of these 2 lines:
Note there will only ever be a max of 600 records!

1) Create a temp table & copy all the records to it from the master table. When a user updates any field copy the original record over to a history table & then write back the edited data to the master table.

2) Carry out a similar approach to above but by using multiple recordsets.

Or an even better way that you can think of & I can't.
Thanks in advance
Tim

 

[jebry]

Hi Tim!

Assuming that the edits will be done on a form, you can use the before update event to create an audit table where you capture the field name, the old data, the new data and the date. If you have a way of identifying the person making the change (for instance, if you are on a LAN) then you can also capture who made the change. There are FAQs on this site that can get you started. One thing you will need to know is that a bound control will have an .OldValue property that maintains the original value of the field until the update is actually done. So the .OldValue will hold the original value and .Value will have the edited value. You can just loop through your controls and compare these two properties and add a record to the Audit table for each change made.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[tigersden]

Hi Jerby,
Thnaks once again, I never knew of .oldvalue.
Thanks
Tim