Access Report Gets Total Pages Wrong

Question

Hello,

I have a report with a lot of text boxes that could grow or shrink. One of them that causes the most issues is a Services Performed field.

If the field is small no trouble. It there is a lot of explanations in that field, it causes the report to be two pages.

Page 1 footer Shows "Page 1 of 1"

Page 2 footer shows, correctly "Page 2 of 2"

I've tried code that has a static variable and records the highest me.page number and it works except page 1 never gets recalculated or reformatted so it remains "Page 1 of 1".

I don't know how to get the correct number of pages on Page 2.

I've had code that looks at the length of the Services Performed field and if over say 1000 make the footer "1 of 2"; but that works sometimes at best.

Access can't see to calculated the number of pages correctly when you have text boxes, or fields, that can grow and shrink. Is there a secret to this? lol

Answer 1

This is a 3-1 Report. It is a Service Report, a Traveler Report and a Packing Slip Report. Depending on what type of report it is, fields are made not visible and other fields are moved higher up on the report. If one field, which is a text field, the entire width of the report is empty, then it is made invisible and other fields below it moved a little higher. When in Service Report mode, almost are fields are active. If the work performed field is too large, the billing summary information is pushed to page two.

My thought behind doing that a year or so ago was that it was easier to make small changes to one report rather than 3. That may have been an error, but it does work really cool.

Page two knows it is Page 2 of 2, but Page 1 thinks it is Page 1 of 1. I tried just about every event. For the service report, one field must be turned off and some the rest of them are moved up .top parameter changed. Since I am doing some trickery like that, Access must think it all fits on one page, YET it knows the over-run is to two pages. I can monitor that and change a "of " & totalpages unbound textbox to "2", but by the time my code counts the second page the first page is already rendered and my change doesn't work on the first page.

I may have to run the report once. If a second page is counted, put that count in an invisible field on the form that calls it, then have the code close the report and reopen it and then the report can get the total pages off the form. So it will have to be in the code that calls the report. Hmmm, that's going to take some thought.

Answer 2

The ControlSource property of the text box in the page footer should be like this:

    ="Page " & [Page] & " of " & [Pages]

Is that the case?

Answer 3

That is unfortunate. The catalog report counts the number of pages required to completely print the report, and then automatically reruns the report with that correct page numbering, but it is an advanced technique. It may be too much for this situation.

Answer 4

Unfortunately I didn't find anything in their report that helped with this issue. Right now as a temp fix, I have an input box coming up asking the number of pages, defaulting to one. If the report print preview (as it always comes in) shows that it extended to 2 pages, close the report, re-start it and tell it 2.

That is really clunky - lol

Answer 5

You might get some ideas from the Northwind Developers EditionReport for the Northwind catalog, which has functions to handle page numbering problem.