[AccessD] Recording field changes

On 22 Jun 2004 at 8:43, David Emerson wrote:

> Group,
> 
> I have a client that needs to record all changes to the data on a field by 
> field basis (they don't want to record whole records if there is a change - 
> just the fields that changed (old and new values)).  My initial though is 
> to put code in every field that records the value on entry, compares this 
> on exit and records the change if they values are different.
> 
> Because there are several hundred fields involved I was wondering if there 
> was any easy way of doing the job without adding code to every field.
> 

Here's one way.

Create an Array to hold the changes and a counter:

In the On_Update for the form, step through each data bound control and compare 
ctrl.OldValue to ctrl.Value
For any that are different, store the fieldname and oldvalue in the array and 
increment a counter:

In the After_Update, step through the array and write each change to a logfile 
with something like:

For lLoopcount = 1 to lNoOfChanges
  CurrentDB.Execute "Insert into tChangeLog 
(TimeStamp,Operator,FieldName,OldValue) values (Now(),UserName(),'" & 
strChangeArray(lLoopcount,1) & "','" & strChangeArray(lLoopcount) & "')"
Next

 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.