Need to print Cover Sheet for report.

[Dennis]

Hi,

I'm using Access via Office XP Pro.

I am working on a government report. The first page of the report is a
cover sheet / instructions and has nothing to do with the actual data other
than it has to be the first page / coversheet to the report.

Then I have the actual report which needs to have one footer at the end of
the report which has the actual totals. I do not need a footer on each page,
just the last page.

Currently, I put the cover sheet in the Report Header. The cover sheet has
our name, address, phone number, name of person submitting the report, and
goverment specified text. It takes one entire page and must be formatted as
specified by the government. At the end of the cover sheet, I want to force
a page eject, but I don't know how to do that. So currently, I've just
formatted the page to take up every line and let Access do the page eject.

I then have a page header, which prints at the top of the second and
subsequent pages.

I then have my detailed report section and then my page footer. (There is
no Report footer.)

Everything works great in print preview. It is perfect! The first page
prints without the page header or page footer, and the subsequent pages (the
actual report) print with the header and footer on the last page. However,
when click on Print, the footer prints on EVERY Page.

I have another post asking how to suppress printing the footer on all but
the last page.

Here is my questions for this post:

Is this the right approach?
I'm thinking about breaking this into two reports - 1 for the cover sheet
and then the acutal report. The actual report will call the coversheet
report in the On Open event and then proceed with the actual report. Any
thoughs on this approach?

Is there a better approach?

If there a way to force a page eject from within the code? I would like to
force a page eject after the cover sheet.

Thanks for your help.

 

[Marshall Barton]

I'm using Access via Office XP Pro.

I am working on a government report. The first page of the report is a
cover sheet / instructions and has nothing to do with the actual data other
than it has to be the first page / coversheet to the report.

Then I have the actual report which needs to have one footer at the end of
the report which has the actual totals. I do not need a footer on each page,
just the last page.

Currently, I put the cover sheet in the Report Header. The cover sheet has
our name, address, phone number, name of person submitting the report, and
goverment specified text. It takes one entire page and must be formatted as
specified by the government. At the end of the cover sheet, I want to force
a page eject, but I don't know how to do that. So currently, I've just
formatted the page to take up every line and let Access do the page eject.

To start a new page after the report header, you should only
need to set the report header section's ForceNewPage
property to After Section in design view.

I then have a page header, which prints at the top of the second and
subsequent pages.

I then have my detailed report section and then my page footer. (There is
no Report footer.)

Everything works great in print preview. It is perfect! The first page
prints without the page header or page footer, and the subsequent pages (the
actual report) print with the header and footer on the last page. However,
when click on Print, the footer prints on EVERY Page.

I have another post asking how to suppress printing the footer on all but
the last page.

Here is my questions for this post:

Is this the right approach?
I'm thinking about breaking this into two reports - 1 for the cover sheet
and then the acutal report. The actual report will call the coversheet
report in the On Open event and then proceed with the actual report. Any
thoughs on this approach?

Don't make it so complicated.

It sounds like you set the page footer section's Visible
property to No in design view and use some code to set it to
True only for the last page. If so, setting the Visible
property to No in design view is not effective. Instead you
can use a line of code in the PAGE HEADER section's Format
event to set the page footer section's Visible property for
each page:

Me.Section(4).Visible = (Me.Page = Me.Pages)

This approach requires that you have a text box somewhere in
the report that refers to Pages. A text box with an
expression something like =Page & " of " & Pages would be
sufficient.

 

[Dennis]

Marsh,

I did set my page footer section's Visible property to No in design view. I
have
“= “Page “ & [Page] & “ of “ & [Pages]†in my page heading.

I did the following:
1. Set the page footer’s Visible Property to Yes in design view.
2. Placed the following code in the PAGE HEADER section's Format event:

Me.Section(4).Visible = (Me.Page = Me.Pages)

The results:
1. I have a 2 page report, 2 pages print, but the value of Pages = 3.
2. No footer is printed on page 2.


I did the following:
1. I set the page footer’s visible property to No in designe view.
2. Placed the above code in the PAGE HEADER section’s Format event.

Results:
1. I have a 2 page report, 2 pages print, and the value of Pages = 2
2. No footer is printed on page 2.

Bottom line is no footer is printed on page 2. Any suggestions.


Here is the report’s code:

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Len(Me.txtPostalCode) > 5 Then
Me.txtPostalCode.Format = "@@@@@-@@@@"
Else
Me.txtPostalCode.Format = "@@@@@"
End If

End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim strNoMembers As String

pintNoMembers = pintNoMembers + 1
strNoMembers = "0" & Trim(str(pintNoMembers)) & "."
txtMemCt = Right(strNoMembers, 3)

End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
txtPFNoMembers = pintNoMembers
txtPFCheckAmt = pintNoMembers * psinMemDues
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.Section(4).Visible = (Me.Page = (Me.Pages))
End Sub



Dennis

 

[Marshall Barton]

I did set my page footer section's Visible property to No in design view. I
have
“= “Page “ & [Page] & “ of “ & [Pages]” in my page heading.

I did the following:
1. Set the page footer’s Visible Property to Yes in design view.
2. Placed the following code in the PAGE HEADER section's Format event:

Me.Section(4).Visible = (Me.Page = Me.Pages)

The results:
1. I have a 2 page report, 2 pages print, but the value of Pages = 3.
2. No footer is printed on page 2.


I did the following:
1. I set the page footer’s visible property to No in designe view.
2. Placed the above code in the PAGE HEADER section’s Format event.

Results:
1. I have a 2 page report, 2 pages print, and the value of Pages = 2
2. No footer is printed on page 2.

Bottom line is no footer is printed on page 2. Any suggestions.


Here is the report’s code:

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Len(Me.txtPostalCode) > 5 Then
Me.txtPostalCode.Format = "@@@@@-@@@@"
Else
Me.txtPostalCode.Format = "@@@@@"
End If

End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim strNoMembers As String

pintNoMembers = pintNoMembers + 1
strNoMembers = "0" & Trim(str(pintNoMembers)) & "."
txtMemCt = Right(strNoMembers, 3)

End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
txtPFNoMembers = pintNoMembers
txtPFCheckAmt = pintNoMembers * psinMemDues
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.Section(4).Visible = (Me.Page = (Me.Pages))
End Sub

Very nice explanation of what you tried and the results.
I'm pretty sure that pins things down.

I was afraid that the last page might get a 3 of 2 kind of
anomaly. The problem is almost certainly that you have
exactly the wrong number of details so that the last detail
fits on the second page if there is no page footer, but
moves to the next page when there is a page footer.

I'll try to explain the background that put's the report
into that catch 22 kind of state. When a report uses Pages,
it must be formatted twice, the first time just to calculate
the number of pages. During this first pass through the
report, Pages has the value 0 so the code to display the
page footer never succeeds. The second time through, Pages
has a value and the page footer is displayed on what was the
last page the first time through. I get a headache thinking
about the ramifications of all that and don't see how
setting the page footer's Visible property in design view
causes different results. The detail section's
CanGrow/CanShrink and KeepTogether settings can add another
level of complexity to the headache.

If you do not require the space the page footer would occupy
on each page, we could try a different approach. Remove the
line of code in the page header's event and try this in the
page footer's event:
Cancel = Not (Me.Page = Me.Pages)

If you want to use the page footer space, then it gets very
messy and can not be done at all if the detail section is
allowed to grow/shrink.

 

[Dennis]

Marsh,

Did you say launching one report for the instruction page and another for
the report was complicated? Trying to suppress the page footing without
causing the print engine to reformat is also complicated. The print preview
and print engine apparently work differently.

I have asked earlier if there was any anyway to force the Report Footing
(not page footing) to print at the bottom of the current page.

Your responded:

Not easily, but it can be done. Add a top level group (View - Sorting And
Grouping) with footer using a constant expression such as =1 Don't add
anything to this group
footer.

Use code in this group footer's Format event to adjust its height to fill
the page down to where you want the report footer to be. The code to position
the report footer 9 inches from the top of the page would be something like:

Const FOOTERPOSITION As Long = 9 * TPI ' 9 inches

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If Me.Top < FOOTERPOSITION Then
Me.Section(6).Height = FOOTERPOSITION - Me.Top
End If
End Sub

I have not experimented with scenarios when there is not enough room for the
report footer on the same page as the last detail.

After reading below, I would ask which is the best option (I’m 3 days into
trying to position the footing):

1. Keep working on the page footing
2. Work on positioning the Report Footing
3. Launch report from within this report to print government page and the
let this report print the detail?


Response to your response:


Regarding your comment “I was afraid that the last page might get a 3 of 2
kind of anomaly. The problem is almost certainly that you have exactly the
wrong number of details so that the last detail fits on the second page if
there is no page footer, but moves to the next page when there is a page
footer.â€

Response: I don’t believe this is the issue yet as I have only 1 detail.

I removed the code in the Page Header and putting in the Page Footer Format
and no footing appeared.

Below is the different code I tried (one at a time) in Page Footer Format
event, the results and a debug.print statement that I put in Page Footer
Format. The debug print statement is:

“Debug.Print "Cancel =" & Cancel, "Page = " & Me.Page, "Pages = " &
Me.Pages, "footing Vis = " & Me.Section(4).Visible:

First try:
“Cancel = Not (Me.Page = Me.Pages)â€

Print Preview
Report Open
Report Activate
Footing Visible set to False
Report Header Format
Page Footer Format Cancel =0 Page = 1 Pages = 0
footing Vis = False
Page Header Format
Detail Format
Page Footer Format Cancel =0 Page = 2 Pages = 0
footing Vis = False
Report Header Format
Page Header Print
Page Footer Format Cancel =0 Page = 1 Pages = 2
footing Vis = True

Preview Page 2
Page Header Format
Detail Format
Detail Print
Page Footer Format Cancel =0 Page = 2 Pages = 2
footing Vis = True


Print Run
Report Header Format
Page Footer Format Print Run
Report Header Format
Page Footer Format Cancel =0 Page = 1 Pages = 2
footing Vis = True

Report Header Format
Page Footer Format Cancel =0 Page = 2 Pages = 2
footing Vis = True
Page Header Format
Detail Format
Page Footer Format Cancel =0 Page = 3 Pages = 2
footing Vis = True

Report Header Format
Page Header Print
Page Footer Format Cancel =0 Page = 1 Pages = 3
footing Vis = True

Report Header Format
Page Header Print
Page Footer Format Cancel =0 Page = 2 Pages = 3
footing Vis = True
Page Header Format
Detail Format
Detail Print
Page Footer Format Cancel =0 Page = 3 Pages = 3
footing Vis = True

It reserves the footing space on page 1 which causes original page 1 to fall
on to 2, and 2 to fall on to three. Does not print footing on pg 1 or 2, but
does on page 3. Since this did not work, I did not try it in Print mode.


Second try:

Footing Visible Property = False design view
If (me.page = (Me.pages) – 1) then
Me.section(4).visibile = True
end

PRINT PREVIEW - PAGE 1
Report Open
Report Activate

Report Header Format Pass # 1
Page Footer Format Cancel =0 Page = 1 Pages = 0
footing Vis = False
Page Header Format
Detail Format
Page Footer Format Cancel =0 Page = 2 Pages = 0
footing Vis = False

Report Header Format Pass # 2
Page Header Print
Page Footer Format Cancel =0 Page = 1 Pages = 2
footing Vis = True

PREVIEW - PAGE 2
Page Header Format
Detail Format
Detail Print
Page Footer Format Cancel =0 Page = 2 Pages = 2
footing Vis = True
Page Footer Print Page Footer Visible = False


PRINT REPORT
Report Header Format Pass # 1
Page Footer Format Cancel =0 Page = 1 Pages = 2
footing Vis = False
Page Header Format
Detail Format
Page Footer Format Cancel =0 Page = 2 Pages = 2
footing Vis = True

Report Header Format Pass # 2
Page Header Print
Page Footer Format Cancel =0 Page = 1 Pages = 2
footing Vis = True
Page Footer Print Page Footer Visible = False

Report Header Format Pass # 1
Page Header Print
Page Footer Format Cancel =0 Page = 2 Pages = 2
footing Vis = True
Page Header Format
Detail Format
Detail Print
Page Footer Format Cancel =0 Page = 3 Pages = 2
footing Vis = True
Page Footer Print Page Footer Visible = False

The problem is in Print mode, turning on Footing appears to cause Access to
reformat starting at page 1. (Pass 2’s page is -= 1).


Here is the strip down code for the report:

Option Compare Database
Option Explicit

Dim pintNoMembers As Integer
Dim psinMemDues As Single
Dim pblnPrtFlag As Boolean
Dim pintNoPasses As Integer

Const cpintDefNationalDues As Integer = 11
Const cpintDefDeptDues As Integer = 11


Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
txtPFNoMembers = pintNoMembers
txtPFCheckAmt = pintNoMembers * psinMemDues
If pblnPrtFlag Then
If (Me.Page = Me.Pages) Then
Me.Section(4).Visible = True
End If
Else
If (Me.Page = (Me.Pages) - 1) Then
Me.Section(4).Visible = True
End If
End If
Debug.Print "Page Footer Format"
Debug.Print "Cancel =" & Cancel, "Page = " & Me.Page, "Pages = " &
Me.Pages, "footing Vis = " & Me.Section(4).Visible
End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)

pblnPrtFlag = True
pintNoPasses = 0
Me.Section(4).Visible = False ‘ reset to invisible for print pass
Debug.Print "Page Footer Print", , " Page Footer Visible = " &
Me.Section(4).Visible
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Debug.Print "Page Header Format"
End Sub

Private Sub Report_Activate()
Debug.Print "Report Activate"

End Sub

Private Sub Report_Open(Cancel As Integer)

Debug.Print "Report Open"
pintNoMembers = 0
psinMemDues = 0
pblnPrtFlag = False
pintNoPasses = 0
Me.Section(4).Visible = False ' Turn off page
footer visibilty

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
pintNoPasses = pintNoPasses + 1
Debug.Print vbCrLf & "Report Header Format", " Pass # " &
pintNoPasses
End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
Debug.Print "Page Header Print"
End Sub



Thanks

Dennis

 

[Steve]

Hello Dennis,

Do you launch the report with a command button? Have you considered creating
the cover sheet/instructions in Word and formatting it as needed? Then in
your code in the click event of the button, start by opening Word and
printing the cover sheet/instructions, Close Word then print the Access
report/

Steve
(e-mail address removed)

 

[Marshall Barton]

Did you say launching one report for the instruction page and another for
the report was complicated? Trying to suppress the page footing without
causing the print engine to reformat is also complicated. The print preview
and print engine apparently work differently.

I have asked earlier if there was any anyway to force the Report Footing
(not page footing) to print at the bottom of the current page.

Your responded:

Not easily, but it can be done. Add a top level group (View - Sorting And
Grouping) with footer using a constant expression such as =1 Don't add
anything to this group
footer.

Use code in this group footer's Format event to adjust its height to fill
the page down to where you want the report footer to be. The code to position
the report footer 9 inches from the top of the page would be something like:

Const FOOTERPOSITION As Long = 9 * TPI ' 9 inches

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If Me.Top < FOOTERPOSITION Then
Me.Section(6).Height = FOOTERPOSITION - Me.Top
End If
End Sub

I have not experimented with scenarios when there is not enough room for the
report footer on the same page as the last detail.

After reading below, I would ask which is the best option (I’m 3 days into
trying to position the footing):

1. Keep working on the page footing
2. Work on positioning the Report Footing
3. Launch report from within this report to print government page and the
let this report print the detail?


Response to your response:


Regarding your comment “I was afraid that the last page might get a 3 of 2
kind of anomaly. The problem is almost certainly that you have exactly the
wrong number of details so that the last detail fits on the second page if
there is no page footer, but moves to the next page when there is a page
footer.”

Response: I don’t believe this is the issue yet as I have only 1 detail.

I removed the code in the Page Header and putting in the Page Footer Format
and no footing appeared.

Below is the different code I tried (one at a time) in Page Footer Format
event, the results and a debug.print statement that I put in Page Footer
Format. The debug print statement is:

“Debug.Print "Cancel =" & Cancel, "Page = " & Me.Page, "Pages = " &
Me.Pages, "footing Vis = " & Me.Section(4).Visible:

First try: [snip invalid tests]
The problem is in Print mode, turning on Footing appears to cause Access to
reformat starting at page 1. (Pass 2’s page is -= 1).

Here is the strip down code for the report:

Option Compare Database
Option Explicit

Dim pintNoMembers As Integer
Dim psinMemDues As Single
Dim pblnPrtFlag As Boolean
Dim pintNoPasses As Integer

Const cpintDefNationalDues As Integer = 11
Const cpintDefDeptDues As Integer = 11


Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
txtPFNoMembers = pintNoMembers
txtPFCheckAmt = pintNoMembers * psinMemDues
If pblnPrtFlag Then
If (Me.Page = Me.Pages) Then
Me.Section(4).Visible = True
End If
Else
If (Me.Page = (Me.Pages) - 1) Then
Me.Section(4).Visible = True
End If
End If
Debug.Print "Page Footer Format"
Debug.Print "Cancel =" & Cancel, "Page = " & Me.Page, "Pages = " &
Me.Pages, "footing Vis = " & Me.Section(4).Visible
End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)

pblnPrtFlag = True
pintNoPasses = 0
Me.Section(4).Visible = False ‘ reset to invisible for print pass
Debug.Print "Page Footer Print", , " Page Footer Visible = " &
Me.Section(4).Visible
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Debug.Print "Page Header Format"
End Sub

Private Sub Report_Activate()
Debug.Print "Report Activate"

End Sub

Private Sub Report_Open(Cancel As Integer)

Debug.Print "Report Open"
pintNoMembers = 0
psinMemDues = 0
pblnPrtFlag = False
pintNoPasses = 0
Me.Section(4).Visible = False ' Turn off page
footer visibilty

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
pintNoPasses = pintNoPasses + 1
Debug.Print vbCrLf & "Report Header Format", " Pass # " &
pintNoPasses
End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
Debug.Print "Page Header Print"
End Sub

You are now mixing the visible approach (that has the last
page problem) with the Cancel approach so I can not get any
useful information from those tests.

You mentioned another thread where I tried to explain how to
get the report footer at the bottom of the page. I'm pretty
sure that idea should work, whereas your requirements go
beyond the restrictions of messing with the page footer.
Let's start over and try that approach to see how it does.

 

[Dennis]

Marshall,

I'm sorry I did not explain things clearly. Before I tried the combined
approach, I did try putting
Cancel = Not(Me.Page = Pages)
in the PageFooter Format event with the visibilty property set to true. The
only place in the code that altered that is the above line of code.

The problem with this approach is Access appears to reserved space for the
footing on the first page. This caused the last paragraph in the Report
Heading to print at the top of the second page instead of the bottom of the
first page. The actual report then start of page 3.

The good news is the footing does not print on page 1 or page 2 but does
print on page 3 as it should.

The bad news is last paragraph of the Report Heading is printed on page 2.
Is there any way to prevent Access from allocating the page footer space on
the pages where the footer is not printed.

I only tried the other approached because I was trying to resolve the
problem without bothering you anymore. I GREATLY appreciate your assitance
and am trying very hard not to abuse that priviledge.

I will try to change the position where the Report Footing prints.

--
Dennis

Did you say launching one report for the instruction page and another for
the report was complicated? Trying to suppress the page footing without
causing the print engine to reformat is also complicated. The print preview
and print engine apparently work differently.

I have asked earlier if there was any anyway to force the Report Footing
(not page footing) to print at the bottom of the current page.

Your responded:

Not easily, but it can be done. Add a top level group (View - Sorting And
Grouping) with footer using a constant expression such as =1 Don't add
anything to this group
footer.

Use code in this group footer's Format event to adjust its height to fill
the page down to where you want the report footer to be. The code to position
the report footer 9 inches from the top of the page would be something like:

Const FOOTERPOSITION As Long = 9 * TPI ' 9 inches

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If Me.Top < FOOTERPOSITION Then
Me.Section(6).Height = FOOTERPOSITION - Me.Top
End If
End Sub

I have not experimented with scenarios when there is not enough room for the
report footer on the same page as the last detail.

After reading below, I would ask which is the best option (I’m 3 days into
trying to position the footing):

1. Keep working on the page footing
2. Work on positioning the Report Footing
3. Launch report from within this report to print government page and the
let this report print the detail?


Response to your response:


Regarding your comment “I was afraid that the last page might get a 3 of 2
kind of anomaly. The problem is almost certainly that you have exactly the
wrong number of details so that the last detail fits on the second page if
there is no page footer, but moves to the next page when there is a page
footer.â€

Response: I don’t believe this is the issue yet as I have only 1 detail.

I removed the code in the Page Header and putting in the Page Footer Format
and no footing appeared.

Below is the different code I tried (one at a time) in Page Footer Format
event, the results and a debug.print statement that I put in Page Footer
Format. The debug print statement is:

“Debug.Print "Cancel =" & Cancel, "Page = " & Me.Page, "Pages = " &
Me.Pages, "footing Vis = " & Me.Section(4).Visible:

First try: [snip invalid tests]
The problem is in Print mode, turning on Footing appears to cause Access to
reformat starting at page 1. (Pass 2’s page is -= 1).

Here is the strip down code for the report:

Option Compare Database
Option Explicit

Dim pintNoMembers As Integer
Dim psinMemDues As Single
Dim pblnPrtFlag As Boolean
Dim pintNoPasses As Integer

Const cpintDefNationalDues As Integer = 11
Const cpintDefDeptDues As Integer = 11


Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
txtPFNoMembers = pintNoMembers
txtPFCheckAmt = pintNoMembers * psinMemDues
If pblnPrtFlag Then
If (Me.Page = Me.Pages) Then
Me.Section(4).Visible = True
End If
Else
If (Me.Page = (Me.Pages) - 1) Then
Me.Section(4).Visible = True
End If
End If
Debug.Print "Page Footer Format"
Debug.Print "Cancel =" & Cancel, "Page = " & Me.Page, "Pages = " &
Me.Pages, "footing Vis = " & Me.Section(4).Visible
End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)

pblnPrtFlag = True
pintNoPasses = 0
Me.Section(4).Visible = False ‘ reset to invisible for print pass
Debug.Print "Page Footer Print", , " Page Footer Visible = " &
Me.Section(4).Visible
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Debug.Print "Page Header Format"
End Sub

Private Sub Report_Activate()
Debug.Print "Report Activate"

End Sub

Private Sub Report_Open(Cancel As Integer)

Debug.Print "Report Open"
pintNoMembers = 0
psinMemDues = 0
pblnPrtFlag = False
pintNoPasses = 0
Me.Section(4).Visible = False ' Turn off page
footer visibilty

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
pintNoPasses = pintNoPasses + 1
Debug.Print vbCrLf & "Report Header Format", " Pass # " &
pintNoPasses
End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
Debug.Print "Page Header Print"
End Sub

You are now mixing the visible approach (that has the last
page problem) with the Cancel approach so I can not get any
useful information from those tests.

You mentioned another thread where I tried to explain how to
get the report footer at the bottom of the page. I'm pretty
sure that idea should work, whereas your requirements go
beyond the restrictions of messing with the page footer.
Let's start over and try that approach to see how it does.

 

[Dennis]

Marsh,

What value should TPI have? TPI is not an Access or VBA variable. I've
search for Access & TPI on Google and could not find anything. I also looked
in Access 2007 VBA, Access Prog by Example, and Inside Access 2003 and there
is no reference to TPI. I know it something per inch, but don't know what.

Dennis

 

[Dennis]

Marsh,

I think I found it TPI = 1440 twips = 1 inch

 

[Marshall Barton]

I think I found it TPI = 1440 twips = 1 inch

Right! Sorry, my Copy/Paste missed the line:
Const TPI As Long = 1440

Did you get a better result with this approach?

 

[Marshall Barton]

I'm sorry I did not explain things clearly. Before I tried the combined
approach, I did try putting
Cancel = Not(Me.Page = Pages)
in the PageFooter Format event with the visibilty property set to true. The
only place in the code that altered that is the above line of code.

The problem with this approach is Access appears to reserved space for the
footing on the first page. This caused the last paragraph in the Report
Heading to print at the top of the second page instead of the bottom of the
first page. The actual report then start of page 3.

The good news is the footing does not print on page 1 or page 2 but does
print on page 3 as it should.

The bad news is last paragraph of the Report Heading is printed on page 2.
Is there any way to prevent Access from allocating the page footer space on
the pages where the footer is not printed.

Right, as far as I can tell, the space for the page footer
is reserved right after the page header is done, so when we
tried to mess with it later we open the door to the page 3
of 2 kind of issue.

If worse comes to worse, you could try setting the page
footer visibility to No in the report header and making it
visible in the second page's page header, but that will
probably invite some other issue to the party.