How best to make a Record of Changes

[philqw78]

I have a table of data for which every change to the data in the table must be recorded. What is the best way to do this with minimum work, and therefore mistakes, from the end user?
I was thinking make a table with the following

• Record ID
  Field Affected
  Old Field Value
  New Field value
  Change Implementer
  Date Changed

I will then use a form to fill the table. Its just getting the form to pull the old field value and let them type a new value, change the value to new in the original and save both in the change record table that is perplexing me.
Thanks

[pod]

you are on the right track

you may want to check out this other thread

http://bytes.com/topic/access/answers/743580-ms-access-audit-trail

and this ..

http://www.everythingaccess.com/tutorials.asp?ID=Creating-an-Audit-Trail-%28Source-Code%29