Re-Calculate [Page] and [Pages] after formatting occurs?

[Ash Skilton]

Hi,

I'm fairly new to Access and VBA. I have a slight problem with printing a report that i'm hoping someone can advise me with.

The report can have one or multiple pages and the Page Footer only appears on the last page.

To achieve this I have used the VBA code:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
If [Page] = [Pages] Then
Me.PageFooterSection.Visible = True
Else
Me.PageFooterSection.Visible = False
End If
End Sub

This tells it to only show Page Footer if the current page is equal to the total pages putting it on the last page only.

This works but my problem is it calculates the total pages before it does the formatting.
So for example the report with the Page Footer on each page may take up 3 pages but once the Page Footer is removed from the first 2 pages it reduces the total pages needed from 3 to 2.
So when I print the report it only prints 2 pages but shows as 1 of 3 and 2 of 3 and the Footer does not print on page 2 because it thinks there is a 3rd page that it should be printing on that no longer exists.

So my question is there a way for it to re-calculate how many pages there are after my formatting has occurred before I print so it knows how many pages there are once the footer has been removed from everything but the last?

Using Report Footer is not an option because I need the footer to be at the absolute bottom of the last page and with Report Footer it can appear half way up the page if that is where the Report Details finish.

Access Version: 2010 Professional.

 

[KenHigg]

I assume you mean you can't use 'Report footer', not 'Form footer'?

 

[Ash Skilton]

I assume you mean you can't use 'Report footer', not 'Form footer'?

Sorry yes that's a typo will update my post thanks :]

 

[stopher]

Hi Ash

You might want to reconsider using the report footer. The way to do this is to get the report to add padding so that the report footer appears at the bottom. This is relatively easy to calculate. Take a look at post #4 here that gives an example. If you look at the code I've provided an explanation of how it works.

Whilst I sure it must be possible to do it using page footer, there are a few problems to overcome as you have already experienced. I guess you'd have to calculate how many footers will be removed and then work out how many pages this would remove. But the problem is exacerbated by the fact that you could have keep-together options which would create inadvertant space. I've no idea how you would begin to calculate this. It's a minefield.

Hopefully you can get Report Footer to work as per my example.

hth
Chris

 

[Ash Skilton]

Thanks for the reply.

One problem I encounter with this method is the report is an invoice and has Totals/Subtotals in my Report Footer which I want to be positioned under the Report Details and not at the bottom of the page?

The Page Footer i'm using is for a Signature and terms of conditions that are at the bottom of the invoice.

Edit: Is it possible to place another tab between the Dummyfooter and Form footer with the totals etc?

 

[stopher]

You don't need to have your totals in the report footer. What I mean is you can create another dummy group to act as your report total section (maybe called dummygroup0). Then the rest of the solution I provided will work fine.

hth
Chris

 

[Ash Skilton]

Thanks Chris this solved my problem perfectly.