Print preview chart doesn't match chart in printed report

[czpevak]

I have a report that has a group header, and the header is set up so that for each group a line-graph (via an imbedded chart) is generated based on data from that group. I am having 2 problems:

1. Sometimes (on a random basis) the same chart appears for every group, even though the charts should be different (because, of course, the underlying data is different). Sometimes I have to run a report 5 or 6 times before the correct report generates (with different graphs for each group).

2. Sometimes (and this is more frustrating) the print preview is correct, but the actual printed copy repeats the same graph for every group.

The query used to generate the line-graph is a TRANSFORM statement, and I am using Access 2000. Does anyone know what is going on or what I can do to fix this problem?

Thanks in advance for your assistance.

 

[MichaelRed]

Without carefully inspecting the data, the transform and the actual report, I certainally can not say where the issue is. It 'SOUNDS LIKE a timing issue between some parts of the process. An example MIGHT be the 'same' transform (name) is used for each section, with some code changing the input or parameters or selection for each group. The report 'executes' before the code is able to change the transform - so you end up with the 'duplicate' data sets for the graph.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[czpevak]

Michael,
Thanks for getting back with me. On a hunch, I searched through Microsoft's knowledge base for a similar problem on previous versions of Access. I found the problem for Access 97, and it describes the cause as "Microsoft Access 97 is not requerying the LinkChildFields and LinkMasterFields properties in the chart object" (article Q165836). Unfortunately, the solution they give works for standard queries, but not for crosstab queries like mine.

Microsoft's solution is to add this statement to the WHERE clause of SQL script:

WHERE [LinkChildField]=Reports![Name of Report]![LinkMasterField]

Again, this works for a standard query, but I need to use a crosstab query to obtain a multiple-series line graph. When I use this code with a crosstab query, I get the error that the "Reports![Name of Report..." is not a valid field name or expression. Do you know a way around this problem?

Thanks again for your help.

 

[MichaelRed]

NameOfReport means you should be substituting the actual Name of YOUR report in lieu of the italiacized "parameter".

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[czpevak]

Michael,

I apologize, I should have specified earlier that the appropriate substitutions have alrealy been made. I replaced my actual report and field names with the italicized descriptions above so you would know which items I was referring to. I would like to add that the code addition suggested by Microsoft does work for a single series line graph, so I know that the "Reports![Name of Report!LinkMasterField" that I am using is both the correct field and syntax. However, this same code in the WHERE statement of a crosstab query does not work. Any ideas?

 

[MichaelRed]

Hmmmmmmmmm,

Back to can't see it from my house (ergo Quality Control says it is O.K.!). I would still see it as more of a timing process. these issues DO occur, despite the general silence on the topic. VB operates asynchrously to at least some degree with database functions. I would suggest that - as a test - you generate a version of your report with a number of subreports. Set a filter on each subreport to a record selection criteria. Set a few subreports up to do your graphs in place of the Groups. See what happens there. If that approach 'works' you might look at how you could filter the groups for your regular report.

Your app 'seems interesting', If it is not terribly sensitive, I would look at a copy of the whole enchallida and see if I can make it work.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[JoeMiller]

You could also try using another select query to select your data out of the crosstab and base your report on that. See if the fix helps the report then.

Joe Miller
joe.miller@flotech.net

 

[sandifromdallas]

I was having the same problem and finally came across this:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q318079

Method 1 mentioned in this article didn't work for me, but method 2 did.

 

[hughesai]

I have seen this problem regularly. Frequently there is only a problem with the first chart in a series.

The problem is definitely timing as MichaelRed has said above. I have always found that method 2 in the M$ article quoted by sandifromdallas does work, but I have never had to put it in a loop. One "DoEvents" command in the OnFormat event always works for me.

One interesting side effect, however, is that, if you are in print preview, with zoom at full page, and you keep "page down" of "page up" pressed to rapidly change pages, it invariably crashes Access (A97 and A2K).

Anyone else seen this or better still know a way to protect against it (the error is always a "MSAccess.exe caused a general protection fault" - not a trappable VB message).

The problem is avoidable with care, and all my limited number of users know about it, but it is annoying . . . . . . . .