Charts out-of sync in Preview & OutputTo, but Print OK

[Doug in Portland ME]

I've got a report with an embedded chart. The chart is in
the report's detail section. A new page occurs at the end
of each detail section, so each page contains some
calculated data in report fields, and a chart below.

The report and chart are linked on the field Program_ID.
The report's RecordSource and the chart's RowSource are
different, however both return 18 records, one for each
Program_ID. The field Program_ID is returned in each of
the result sets.

When I display the report in Preview mode, the report
fields are fine, however the chart data on a given page
doesn't always correspond. For example, stepping through
the report pages would yield the following:

Page 1: Report=Program A, Chart=Program A
Page 2: Report=Program B, Chart=Program B
Page 3: Report=Program C, Chart=Program B
Page 4: Report=Program D, Chart=Program D
Page 5: Report=Program E, Chart=Program D
etc.

With identical data, the results vary from perfect to one
page out of sync to several pages out of sync - never the
same, no pattern I can see.

The same occurs when using OutputTo to generate a .snp
file in VBA.

HOWEVER!!! No matter how out of sync the charts are in
Preview mode or in the output snapshot file, if I print
the report to a printer, it comes out correct 100% of the
time.

I am using Access 2002 SP-1. I've spent the last 6 hours
searching the internet for any info on this phenomenon
without success. I've recreated the report and all
underlying queries from scratch in an brand new database
with the same result.

The whole point of this exercise is to set up an automated
e-mail report distribution system - but if I have to
physically print the reports in order to have them be
correct, that' hardly automated. I need to use snapshots
because I'm the only Access license here. I don't want to
have to re-work this whole thing using Crystal reports...
HELP!!!

 

[SA]

Doug:

This is a common problem and it has to do with timing issues between the
chart's update and the display of data. You can fix this with very simply
vba. In the section of your report that contains the chart object, add code
like this:

Dim objChart as Object
Set objChart = Me!NameOfChartControl
objChart.Refresh
'optional, forces data into Chart's data sheet for design
'objChart.Application.Update
Set objChart = Nothing

 

[Doug in Portland ME]

This did not work at first - I tried it in both the
section Format and Print events. Adding a DoEvents after
the .Refresh, though, did the trick. Maybe because the
report and chart are both based on pretty complex crosstab
queries accessing attached SQL Server tables, the
refresh "lag time" is excessive? Just a thought...

Thanks very much Steve!