Need to print Cover Sheet for report.

Hi,

I am 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 do not know how to do that.  So currently, I have 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 am 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.


--
Dennis

Dennis wrote:To start a new page after the report header, you should onlyneed

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.


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.

--
Marsh
MVP [MS Access]

Marsh,I did set my page footer section's Visible property to No in design view.

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

Dennis wrote:Very nice explanation of what you tried and the results.

Very nice explanation of what you tried and the results.
I am 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 will 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 do not 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.

--
Marsh
MVP [MS Access]

Marsh,Did you say launching one report for the instruction page and another

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

Hello Dennis,Do you launch the report with a command button?

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
santus@penn.com

Dennis wrote:[snip invalid tests]

Dennis wrote:
[snip invalid tests]

--Get lost $teve. Go away... far away....No-one wants you here...

--
Get lost $teve.  Go away... far away....
No-one wants you here... no-one needs you here...

OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'...  we have passed 11.000 pageloads... it is a shame !!)

For those who do not 'agree' with this mail , because $teve was 'helping' with his post...
We warned him a thousand times...  Sad, but he is not willing to stop advertising...

He is just toying with these groups... advertising like hell... on and on... for years...
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...

==> We are totally 'finished' with $teve now...
==> Killfile 'StopThisAdvertising' and you will not see these mails....

Arno R

Marshall,I am sorry I did not explain things clearly.

Marshall,

I am 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

Marsh,What value should TPI have?

Marsh,

What value should TPI have?  TPI is not an Access or VBA variable.  I have
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 do not know what.

Dennis

Marsh,I think I found it TPI = 1440 twips = 1 inch--Dennis"Dennis" wrote:

Marsh,

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

--
Dennis

Dennis wrote:Right!

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

Did you get a better result with this approach?

--
Marsh
MVP [MS Access]

Dennis wrote:Right, as far as I can tell, the space for the page footeris

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.

--
Marsh
MVP [MS Access]