Flag data changes

I tried posting this question in another group but no luck, so trying
again with this group.

I need to export data stored in Access to Excel. The Excel file,
which is a client report, needs to show any changes to the data since
the last report (text would be in red). The entire row will not
appear in red, only the specific data that has changed. I can work
out several ways to track changes in Access, but have not come up with
an efficient way to compare current data to prior data in order to
highlight the changes. The report has around 500 records with 20+
fields; I expect looping through each field in each record would be
quite slow. In a perfect world, I would create a custom boolean
property at the field/record level, and set it to true when data is
changed. Then I could just check the value of the property when
creating the report to determine font color. Since that isn't
possible (as far as I'm aware), I need to come up with something else
that will achieve the desired result with acceptable performance.

Any ideas?

Re: Flag data changes

wrote in message
news:3b1e7df9-be9f-42ef-a19c-ef70c3de1844@w40g2000hsb.google groups.com...
>I tried posting this question in another group but no luck, so trying
> again with this group.
>
> I need to export data stored in Access to Excel. The Excel file,
> which is a client report, needs to show any changes to the data since
> the last report (text would be in red). The entire row will not
> appear in red, only the specific data that has changed. I can work
> out several ways to track changes in Access, but have not come up with
> an efficient way to compare current data to prior data in order to
> highlight the changes. The report has around 500 records with 20+
> fields; I expect looping through each field in each record would be
> quite slow. In a perfect world, I would create a custom boolean
> property at the field/record level, and set it to true when data is
> changed. Then I could just check the value of the property when
> creating the report to determine font color. Since that isn't
> possible (as far as I'm aware), I need to come up with something else
> that will achieve the desired result with acceptable performance.
>
> Any ideas?

The way I achieve this is to output data to a spreadsheet on a weekly basis
and apply conditional formatting in the Excel sheet using Automation from
Access VBA. The Excel spreadsheet is used to provide percentage progress in
columns, week by week. In my application I need to colour a cell's
background red if progress has moved backwards, green if it has moved
forwards and no colour at all if it is the same. Here's the relevant bit of
code, you might be able to adapt it for your needs.

Keith.
www.keithwilby.com

'Format the cells for progress. Red for negative, green for positive, else
white.
Set objRange = objXL.Intersect(objSht.Range("L2:IV65536"), objSht.UsedRange)
'Start from Row 2
objSht.UsedRange.Select
With objRange
.Select
.Cells(1, 1).Activate
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="="
& .Cells(1, 0).Address(False, False)
.FormatConditions(1).Interior.ColorIndex = 38 'Conditionally format
cells red
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater,
Formula1:="=" & .Cells(1, 0).Address(False, False)
.FormatConditions(2).Interior.ColorIndex = 35 'Conditionally format
cells green
End With