Creating a report of only changed data 1

[jgarnick]

I need to be able to create a report of only the fields where data was changed. For example, I have an address form. If a user changes any of the address/phone number/email fields, I need to be able to collect that change (only the changed data) and drop it into a report. Or perhaps have all the data print on the report, but the changed data be in bold?? I want the changed data to be obvious. Right now, I am using the form's afterupdate property to run a macro that flips a flag in the table. The report then pulls together all the records that have the flag flipped. But, I can not tell which field was changed, only that A field was changed.<br>
<br>
Any ideas??

 

[JimHorton]

If you don't want to have a flag for each field, you should set one bitmapped field, and set bits based on the ordinal field number of each field. Then use bitwise (OR/AND) masks to set the bits per field in AfterUpdate (of each field) and ANDmask to see if the particular field was changed.<br>
--Jim<br>

 

[jgarnick]

Anything easier? I've never worked with bitmapped fields in the way you explained..........<br>
<br>
Thanks!

 

[elizabeth]

You could use the OnUpdate event of each control to store into variables the data that was changed. Then copy it to a new record in a Changed table and print that.<br>
<br>
But doesn't it sound more interesting to try something you've never done before?<br>
<br>

 

[JimHorton]

In a bitfield, the Masks are as follows:<br>
Lets say it's an Integer, 2 bytes. Starting from rightmost bit 0:<br>
Bit0=1,Bit1=2,Bit2=4,Bit3=8,Bit4=16, etc---&gt; Bit 14=16384. (remember it a Signed integer, so if you define it as an integer field, you can't use bit 15, the sign bit, you'll get an overflow--it's best to use one long int bitfield per 31 datafields)<br>
<br>
Use an OR mask to set the bit, an AND mask to read it:<br>
<br>
You have 8 fields (simplified)<br>
Field# 2 (zero based) was changed.<br>
In AfterUpdate of the Field, do me!bitfield = Me!bitfield OR 4<br>
the bitfield now looks like:<br>
00000100<br>
It has a value of 4.<br>
Now, field 7 changes. In After Update of the *Field*:<br>
me!bitfield = Me!bitfield OR 128<br>
Now, the bit field is:<br>
10000100, having a value of 129. <br>
To read, lets say you're checking field 7, say, in either Before or After Update of the *Form*<br>
If me(7) AND 128 then 'will return Mask's value (128) if bit set<br>
'then it's bit is set, do whatever<br>
endif<br>
Now, you need to decide when to clear the bitfield, ie, when is the 'new' data condsidered 'old data', which must then be flagged when it is then changed again--per session, per day, per user, etc--user 1 changes record, user 2 changes it back. Lots of stuff to think about.<br>
<br>
If this seems tedious, it is somewhat, but it's a tradeoff--the alternative of a separate 'changed' flag field, though, may seem easier to code, it adds so much to the weight of the table, and if it's on a network, Access would need to gather, ie, 31 separate fields to read or set these values, when a Long Integer would do the same in 4 bytes. Multiply that by a table of, say, 500,000 records, and the tradeoff decision is easier.<br>
<br>
Having said all that, remeber you have the Form.Dirty, the Form!Control.Oldvalue properties you can use as well, but only at runtime when the form is open--what the bitfield does is store the 'flags' after the form is closed.<br>
--Jim

 

[JimConrad]

Excellent post from TipMaster JimHorton.<br>
<br>
As he said, you have a lot of requirements to think about!<br>
<br>
If you do need to keep track of each users individual changes (Bill sets a record to &quot;3&quot;, then Bob changes it back to &quot;4&quot;, then Bill changes it back again...), then:<br>
<br>
Elizabeths suggestion (a &quot;changed&quot; table) is the way to go. You could add some columns to the &quot;changed&quot; table to track who made the change, date/time stamp to tell when the change occurred, add a column to store the name of the field(s) which were changed, etc.<br>
<br>
You could generate reports based on the date/time stamp (everything changed since last Friday).<br>
<br>
Obviously, you should also delete rows from the &quot;changed&quot; table periodically. <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>