Report/Page footer at bottom of last page - handle more lines on first page

I have an report listing lines on an Quote with a subtotal and large footer to handle terms an conditions.

Works fine with one page with page footer set to bottom of page but does not work when the number of lines means more than one page.

I realise I cannot set a report footer to bottom of page and need to use page footer for this.

I can force the page footer to invisible or cancel = true on the first page but this leaves a gap - i.e. I think the report works out the number of lines that will print on the first page before making the footer not visible. Also I have tried changing the height on the footer (which would be the best option) but still leaves a gap.

I know there is a lot of information on this on EE and I have looked through a lot but most deal with this as cancel = true on page footer.

I guess needs to work out how many pages first and needs to know how many lines in each but perhaps there is a way with a

Appreciate any ideas on printing more lines on the first page where no footer.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If I read you correctly, I believe you are not using a Report Footer because you possibly want to print more than one quote at the same time, and you want only one footer for each quotation that you print, be it a single page or multi page quote.

Is this the case ?
Helen FeddemaCommented:
In the report properties sheet (the Format tab), you can select Not with Rpt Ftr for the Page Footer property, so it won't print if the report is only one page long.
donhannamAuthor Commented:
Thanks for comments.

I am trying to print one quote at a time. Issue is with multiple pages - I want the summary - total values and some terms and conditions to print at the bottom of the last page.

With report footer I believe I cannot set this to print at the bottom of the page so cannot use this.

Therefore Page footer looks like the only option - I want it not to print - or print with a reduced height - on all but the last page.

I can achieve this with code I have found in EE to "on format page footer" set cancel = true when page footer prints on all but the last page.

However this leaves a gap on the first page where the page footer was. I believe this is because the report calculated the number of detail records on the first page before getting to the page footer.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hamed NasrRetired IT ProfessionalCommented:
To understand the problem, do you need to print the report like this?
First page: report header, page header
2nd page: page header, page footer (small size)
last page: you need page header,  page footer (big size)
no report footer
donhannamAuthor Commented:

Yes this is correct
Hamed NasrRetired IT ProfessionalCommented:
Try this:
' page 1 no footer if more than 1 page:
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If Page = 1 Then
        If Page = pages Then
          Cancel = True
        End If
    End If
End Sub

'last page no report footer, and increase PageFooterSection height
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    PageFooterSection.Height = PageFooterSection.Height + 3000 ' need to mange that
    Cancel = True
End Sub
donhannamAuthor Commented:
Ok i've done a bit of work on this and am getting close.

What I am doing is:-
Cannot set a report footer to print at the bottom of the page
Therefore need to use a Page footer and only display on the last page.
Issue is to get the right number of lines to appear on the first page so no gap left.

This can be done by first setting the page footer to a small height - say 0.7cm
Setting fields to not visible and top of 0 and hieght of less that 0.7cm.

Note you cannot set page footer to not visible and then make it visible later.

On format of the report footer (before page footer of last page) set the height on the page footer to increase it to the required height. Also reset the top and height of the fields in the page footer as required as well as making visible.

To get the report to calculate the right number of lines for first and last page you need to have a blank report footer to fill in the space of the page footers increased height. i.e. height of page footer at end less height of page footer at start. Do not print this by setting cancel = true on print of report footer. (not format)

To Print Footer that is 3cm high

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
If Me.Page = Me.Pages Then
    Cancel = True
    Me.PageFooterSection.Height = 3 * 567
    Me.Field1.Top =   1 * 567
   Me.Field1.Height = 0.8 * 567
   Me.Field1.Visible = True
   Me.Field2.Top = 2 * 567
  Me.Field2.Height = 0.9 * 567
  Me.Field2.Visible = True
End if
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
    Cancel = True
End Sub

'Note that Pages need to be displayed on the report - I did it in the report header
'-field with ="Page " & [Page] & " of " & [Pages] enough

This all works good apart from:-

When I preview the report for a report that will fit on one page it previews as one page - when I print directly to a printer with DoCmd.OpenReport stDocName, acViewNormal it works OK - 1 page.

However when I preview the report and then ctl P and print it prints a second page and page numbers are 1 of 2. I put a break in the code and it does not seem to run any other code after preview appears on screen?.

Appreciate any ideas on what could be causing this.

Hamed NasrRetired IT ProfessionalCommented:
This is a specific case, so can you attach a sample database with this report. Tell me what to do to get what you are after. (1 page, and more than 1 page).
donhannamAuthor Commented:
OK I've had another look at this and have added the following to the end of the ReportFooter_Format sub :-

Me.ReportFooter.Height = 0

This has fixed the problem.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.