Access Report - Weird Result

[Zwoker]

Greetings everyone,

I have a report in MS Access 2003 that looks fine in print preview mode. When I click the print button the data on the printed output, in the first footer changes. Some of the values have the same last few digits as the preview but different preceding digits, some are completely different.

I know this is probably hard to answer without seeing the result and the report code (which does some selective accumulating for the footer), but has anyone seen anything like this before? And if so, what is the problem?


Thanks,
Zwoker.

[Stewart Ross]

Hi Zwoker. As you know it is indeed difficult to speculate on this without seeing the report output or the footer code involved.

If you are using the On Format and On Print methods you need to be aware that these can be called many times for any one section of your report. A possibility is that the number of calls to these events is different between the print preview and actual print modes.

If your code does not correctly handle multiple calls to these events you could be setting and accumulating values in error without realising it. You can test the PrintCount and FormatCount values to ensure that multiple calls are dealt with correctly. By the way, multiple calls are the norm - as sections are moved around from one page to another and the page reformatted you can expect many format events to be fired before On Print occurs.

Another possibility is that the variables are not being initialised appropriately, and consequently spurious data is being added to the accumulators. Just speculation on my part, though.

Sounds like you will have to be very clear about the logic of what you are trying to achieve. You will have to adopt very systematic approaches to debugging if you are using event code to set your accumulator values.

-Stewart

[Zwoker]

Hi Stewart,

Thanks for the response.

My selective accumulation code in those events is already in a condition of PrintCount=1 and I have set up initialisation for my accumulated values. Both at the beginning and end of the processing – in case MS Access doesn't initialise when you click the print button.

If the initial Print Preview result was wrong I'd be looking for a simple coding error. But it is correct, as is an export of the data. It's just the physical print that produces something different, and wrong.

I'm finding the hardest thing for this issue is that it will not let me step through the code when it prints - it just does it ignoring any breakpoints etc... that I have.

I have decided to take the code out of the report that does the selective accumulation and put it into the VBA code that extracts the data in the first place. It can do the same work and just produce the final summary data for reporting.

I'd still love to know what's going on, but I guess now I never will.


Regards,
Zwoker.

[Stewart Ross]

Hi Zwoker. Don't give up on this one - something is amiss if you are unable to breakpoint your code. As a test I set a breakpoint in the On Print code of the detail section of a report and VBA stopped as normal, allowing all the usual stepthroughs, variable comparisons etc. A possibility, which you may think just cannot be so, is that if the VBE etc are OK then the breakpoint code is not being called at all (residing in a section of the report that is not being formatted or printed, say?).

If you need someone to look at your code for a second opinion feel free to zip a sanitised version of your DB and attach it to a new post, or if you do not wish to post a public copy send me a PM and I would be happy to supply you my e-mail address accordingly.

Cheers

Stewart

[Zwoker]

Hi Stewart,

Perhaps I was a bit unclear...

My break points are triggered Ok when the report runs initially - its just when it has displayed the "good" result in the print preview window, and I click the Print button in MS Access, that it doesn't trigger any break points when it creates the "bad" physical printout.

Are you finding that you do get the code breaking in both situations? Until I had the problems with this report I had assumed that MS Access simply took what it had already created (and displayed in the print preview) and sent it to the printer - but obviously this isn't the case - it appears to actually rerun the code and produce the output that way. Why in this situation it does it differently the second time is the heart of the mystery.

I have already re-coded the solution to to the data summrising in the VBA code data-sourcing area (it runs from the form that starts the activity). It now produces simple data records that the report correctly shows and prints, without any code needed in the report itself.

SInce I haven't seen this problem before, and googling on it shows nothing, there must have been something specific I have done in the report properties and/or code that has triggered the weird result, but at this point I'm happy to just move and and start on the next task I have to do... Time is running short - as usual. *grin*


Regards,
Zwoker.

[NeoPa]

While finding the explanation may be interesting, I would certainly recommend the "Do it in the SQL" approach you're currently using wherever possible.

My guess at your issue (from what little I've seen in here) is that some of your code (including calculations) is being re-run at a time that you haven't factored into the logic of your code (although you've clearly considered this aspect). Handling this perfectly can be really quite tricky.

I'm sure this would be a lot clearer if you were able to step through the code of course.

[Zwoker]

Greetings,

A slightly belated and related follow up on this topic.

I had thought that I was all done writing reports in MS Access, but last week I was asked to do just one more... After producing a prototype I received the now expected "can you just make this one little change to the way it does its totalling".

What they asked for had me jumping into the VBA code in the report, and doing selective totalling. Sure enough sometimes it worked exactly as expected, sometime it didn't.

To cut a long story short, I eventually discovered why.

Even though I had the code inside a FormatCount=1 in the On_Format routine, it was getting executed multiple times, on some occasions. It all became clear when I read the help on the Retreat event. It seems if you cause reformatting to occur, commonly when you have KeepTogther set, that the Format event can be called more than once with a value of 1 in the Count parameter. Nasty!

Moving the code to On_Print fixed the counting issues, but killed my KeepTogether display (I don't know why).

So once again I moved all the counting and selective accumulation back to the SQL code that creates the table that the report prints on. All works well now.

The moral of this story? Don't try doing this sort of VBA coding in MS Access reports. It's just not worth the trouble.

And as for this specific topic, I guessing that the actual printing of the output fitted slightly differently on the "page" than the print preview mode, so it was forced to do retreats and reformatting to get it to work. Hence the difference between the print preview and the physically printed output.


Regards,
Zwoker.

P.S. I have now hung up my MS Access "hat", so I wont be here very often any more. Thanks to everyone that has helped with all the weird and wonderful SQL, Data Connect, Reporting, VBA coding and all the other MS Access problems I have encountered over the last few months.

[Stewart Ross]

Thank you for the update, Zwoker, and for all your contributions to this forum. I hope we can continue to be of interest to you in the future too.

I wish you all the best with the role or tasks which take over from your previous database work.

Regards

-Stewart

[NeoPa]

...The moral of this story? Don't try doing this sort of VBA coding in MS Access reports. It's just not worth the trouble.
...

I'm glad you got things going, and posting the reasons you found was very helpful, thanks.

I would like to point out that the quoted statement is simply one person's view though. With careful reading of the help system and, admittedly a fair amount of complicated logical thinking, it's quite possible to code these events correctly to produce consistent results. Not for the faint-hearted of course, but possible.

[Zwoker]

I'm glad you got things going, and posting the reasons you found was very helpful, thanks.

I would like to point out that the quoted statement is simply one person's view though. With careful reading of the help system and, admittedly a fair amount of complicated logical thinking, it's quite possible to code these events correctly to produce consistent results. Not for the faint-hearted of course, but possible.

For this one particular issue, that of doing code-selective totalling based on fields in the the detail section, I think it would be very hard to be sure you can get it correct 100% of the time. I actually tried putting some "undo" code in the Retreat event and found that wasn't as reliable as the help made it sound. Perhaps its just an Access 2003 issue and is fixed in later versions, or perhaps I just can't think of a way to code a solution using the Format, Print and Retreat events that works - which is more likely. *grin*

Anyway, from my perspective I found it much easier to code in the SQL side, and then I only had to be sure the report printed what was already in the table.

Something that I found slightly amusing was the flawed version of the report actually passed the User Acceptance Testing. They never noticed that some group totals had double values, even though these ones were always at the top of the page. Oh well.


Regards,
Zwoker.

[NeoPa]

...
Something that I found slightly amusing was the flawed version of the report actually passed the User Acceptance Testing. They never noticed that some group totals had double values, even though these ones were always at the top of the page. Oh well.
...

:)

That's much less rare than you'd suppose I'm afraid.

Think back to your schooldays and think about how few children liked Maths. Well, it's no different in adult life I'm afraid. Many adults are still fundamentally nervous around numbers (and checking can be mundane work for those that don't have the problem too).