By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,406 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

report footer at the bottom of the last page

P: n/a
Most suggestions on this topic recommend to use a page footer and make
it visible only on the last page. My problem is that the footer is half
of the height of a page which means the detail would show on only the
top half of each page with whitepace at the bottom of each page.

How can I get the detail to fill each page with my "half page height"
report footer at the bottom of the last page?

Any suggestions to solve this would be appreciated.

Greg

Oct 18 '06 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Greg wrote:
Most suggestions on this topic recommend to use a page footer and make
it visible only on the last page. My problem is that the footer is half
of the height of a page which means the detail would show on only the
top half of each page with whitepace at the bottom of each page.

How can I get the detail to fill each page with my "half page height"
report footer at the bottom of the last page?

Any suggestions to solve this would be appreciated.

Greg
I really don't understand your problem but I'll stick my foot in.

To make it easy for me to print multicolumn reports with a header for
each column I
1) Create a column in the query. Ex:
NewCol : "1"
2) Create a header/footer for that group

Maybe you could create a column like I did above, create a footer for
the group, and put your footer data in that band.
Oct 18 '06 #2

P: n/a

Greg wrote:
Most suggestions on this topic recommend to use a page footer and make
it visible only on the last page. My problem is that the footer is half
of the height of a page which means the detail would show on only the
top half of each page with whitepace at the bottom of each page.

How can I get the detail to fill each page with my "half page height"
report footer at the bottom of the last page?

Any suggestions to solve this would be appreciated.

Greg
I'm not sure why you wouldn't want to use a report footer to do this
rather than a page footer. By definition a report footer will only
appear at the end of the report. At any rate, whichever method you
decide to use simply set the CanShrink property of that section to
'No', size the section to the height that you want it to occupy on the
final page, and set the CanGrow property of the detail section to
'Yes'.

Bruce

Oct 18 '06 #3

P: n/a
de***************@gmail.com wrote:
Greg wrote:
>Most suggestions on this topic recommend to use a page footer and
make it visible only on the last page. My problem is that the footer
is half of the height of a page which means the detail would show on
only the top half of each page with whitepace at the bottom of each
page.

How can I get the detail to fill each page with my "half page height"
report footer at the bottom of the last page?

Any suggestions to solve this would be appreciated.

Greg

I'm not sure why you wouldn't want to use a report footer to do this
rather than a page footer. By definition a report footer will only
appear at the end of the report. At any rate, whichever method you
decide to use simply set the CanShrink property of that section to
'No', size the section to the height that you want it to occupy on the
final page, and set the CanGrow property of the detail section to
'Yes'.

Bruce
Page Header/Footers don't support CanShrink or CanGrow. That is his
dilemma. ReportFooters appear on the desired page (the last one) and they
DO support CanShrink and CanGrow, but they do not justify themselves to the
bottom of the page (which he wants).

Using a PageFooter and hiding it on all pages but the last is no good
because the blank space is still used up on all pages and he indicates that
he needs a lot of vertical space for his footer.

I don't think there is any easy way to solve the problem.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 18 '06 #4

P: n/a
I think Bruce had it right. Get rid of the page footer and your detail
will fill the main pages. Make your last page the report footer. Make the
Report
footer the size of a page with your text at the bottom and white space at
the top.
Set the force new page to true for the report footer. You'll get perhaps
several
pages filled with detailand then a new and last page showing white space on
top and your text on the bottom.

Kevin C
"Greg" <bk*****@gmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...
Most suggestions on this topic recommend to use a page footer and make
it visible only on the last page. My problem is that the footer is half
of the height of a page which means the detail would show on only the
top half of each page with whitepace at the bottom of each page.

How can I get the detail to fill each page with my "half page height"
report footer at the bottom of the last page?

Any suggestions to solve this would be appreciated.

Greg

Oct 19 '06 #5

P: n/a
As I reread this it sounds like the original poster simply wants to
insure that the bottom half of the last page of the report is blank.
Is this the case or am I misunderstanding?

Bruce

Oct 19 '06 #6

P: n/a
Bruce
What he asked for is "How can I get the detail to fill each page with my
"half page height"
report footer at the bottom of the last page?"

<de***************@gmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...
As I reread this it sounds like the original poster simply wants to
insure that the bottom half of the last page of the report is blank.
Is this the case or am I misunderstanding?

Bruce

Oct 19 '06 #7

P: n/a
Kc-Mass wrote:
Bruce
What he asked for is "How can I get the detail to fill each page with my
"half page height"
report footer at the bottom of the last page?"
I found the OP's message completely confusing. He didn't adequately
describe his problem, and what he did he did poorly, IMO.
Oct 19 '06 #8

P: n/a
Kc-Mass wrote:
Bruce
What he asked for is "How can I get the detail to fill each page with
my "half page height"
report footer at the bottom of the last page?"
Correct, the content of his footer is half a page high which is why he
doesn't want to use a PageFooter. If he uses a PageFooter and makes it
invisible on all but the last page his last page will look as desired, but
all other pages will be half blank because the hidden page footer will still
take up real estate.

Conversely, if he uses a ReportFooter then it will appear only on the last
page without affecting the appearance of the other pages (that being the
good news), but now the footer is no longer guaranteed to be at the bottom
of the last page but instead will immediately follow the last detail or
group footer.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 19 '06 #9

P: n/a
Rick's right. For the others let me explain further:

-The report is actually a Purchase Order (PO)
-We produce about 40 PO's per day
-About 35 of those are single-page PO's
-5 signatures are required on each PO (15cm high footer)
-using a page footer, the 35 PO's look great but the 5 multi-page ones
are wrong because there is a redundant footer on the first (and
second..etc) pages. I need a footer only on the last page.
-using a report footer, I get the footer only on the last page but all
PO's look strange with the footers in different positions following the
detail on the PO's
-I want all 40 PO's to have the footer at the bottom of the only(35) or
last(5) page.
-I don't want to lose whitespace (lower portion) on the first pages of
a multipage PO.

I tend to agree with Rick, I don't think there is a solution. Right now
I'm using a report footer but it doesn't look professional at all.

Thanks for any advice.

Greg

Rick Brandt wrote:
Kc-Mass wrote:
Bruce
What he asked for is "How can I get the detail to fill each page with
my "half page height"
report footer at the bottom of the last page?"

Correct, the content of his footer is half a page high which is why he
doesn't want to use a PageFooter. If he uses a PageFooter and makes it
invisible on all but the last page his last page will look as desired, but
all other pages will be half blank because the hidden page footer will still
take up real estate.

Conversely, if he uses a ReportFooter then it will appear only on the last
page without affecting the appearance of the other pages (that being the
good news), but now the footer is no longer guaranteed to be at the bottom
of the last page but instead will immediately follow the last detail or
group footer.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 20 '06 #10

P: n/a
Greg wrote:
Rick's right. For the others let me explain further:

-The report is actually a Purchase Order (PO)
-We produce about 40 PO's per day
-About 35 of those are single-page PO's
-5 signatures are required on each PO (15cm high footer)
-using a page footer, the 35 PO's look great but the 5 multi-page ones
are wrong because there is a redundant footer on the first (and
second..etc) pages. I need a footer only on the last page.
-using a report footer, I get the footer only on the last page but all
PO's look strange with the footers in different positions following the
detail on the PO's
-I want all 40 PO's to have the footer at the bottom of the only(35) or
last(5) page.
-I don't want to lose whitespace (lower portion) on the first pages of
a multipage PO.

I tend to agree with Rick, I don't think there is a solution. Right now
I'm using a report footer but it doesn't look professional at all.
I would not give up yet.

I would definitely look at MoveLayout.

You'd need to set NextRecord and Printsection to False if you
MoveLayout. When ready to print, set MoveLayout to False, NextRecord to
False and PrintSection to true

I guarantee you that you will need to futz with this. You may have to
do this by having another group band or perhaps a calc in the detail
band, not sure, perhaps doing some calcing in some bands, etc. But...I
think it can be done. I did something similar...but in reverse...I
needed to print my detail at the bottom of the report and skip down half
a page until I got there. The problem was, depending on the number of
detail lines, I had no idea where I needed to skip to and when to start
printing. But I was able to come up with a very satisfactory solution
with those methods I listed.

1) Don't be afraid to create a dummy band
2) Don't be afraid to experiment
and you'll come out OK

>
Thanks for any advice.

Greg

Rick Brandt wrote:
>>Kc-Mass wrote:
>>>Bruce
What he asked for is "How can I get the detail to fill each page with
my "half page height"
report footer at the bottom of the last page?"

Correct, the content of his footer is half a page high which is why he
doesn't want to use a PageFooter. If he uses a PageFooter and makes it
invisible on all but the last page his last page will look as desired, but
all other pages will be half blank because the hidden page footer will still
take up real estate.

Conversely, if he uses a ReportFooter then it will appear only on the last
page without affecting the appearance of the other pages (that being the
good news), but now the footer is no longer guaranteed to be at the bottom
of the last page but instead will immediately follow the last detail or
group footer.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Oct 21 '06 #11

P: n/a
"Greg" <bk*****@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
Rick's right. For the others let me explain further:

-The report is actually a Purchase Order (PO)
-We produce about 40 PO's per day
-About 35 of those are single-page PO's
-5 signatures are required on each PO (15cm high footer)
-using a page footer, the 35 PO's look great but the 5 multi-page ones
are wrong because there is a redundant footer on the first (and
second..etc) pages. I need a footer only on the last page.
-using a report footer, I get the footer only on the last page but all
PO's look strange with the footers in different positions following the
detail on the PO's
-I want all 40 PO's to have the footer at the bottom of the only(35) or
last(5) page.
-I don't want to lose whitespace (lower portion) on the first pages of
a multipage PO.

I tend to agree with Rick, I don't think there is a solution. Right now
I'm using a report footer but it doesn't look professional at all.

Thanks for any advice.
I'm thinking you could use the print method to code the content of the footer on
the last page. Then you have absolute control over where the content is. I
just don't know how to determine whether the content has enough room left on the
last page with detail content. If you can figure out when you have room and
when you need to move to another page then that would work.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 21 '06 #12

P: n/a
"Greg" <bk*****@gmail.comwrote:
>Rick's right. For the others let me explain further:

-The report is actually a Purchase Order (PO)
-We produce about 40 PO's per day
-About 35 of those are single-page PO's
-5 signatures are required on each PO (15cm high footer)
-using a page footer, the 35 PO's look great but the 5 multi-page ones
are wrong because there is a redundant footer on the first (and
second..etc) pages. I need a footer only on the last page.
-using a report footer, I get the footer only on the last page but all
PO's look strange with the footers in different positions following the
detail on the PO's
-I want all 40 PO's to have the footer at the bottom of the only(35) or
last(5) page.
-I don't want to lose whitespace (lower portion) on the first pages of
a multipage PO.

I tend to agree with Rick, I don't think there is a solution. Right now
I'm using a report footer but it doesn't look professional at all.

Thanks for any advice.

Greg
[...]

It can be done, I have done it with a terrible report that had all
sections (report header, detail section and report footer) of variable
heights.

I cannot publish the code here "as is", but give me a couple of days,
and I will come up with a outline of what I have done. Right now it is
too late in the day for me :-)

Kind regards
Matthias Kläy
--
www.kcc.ch
Oct 21 '06 #13

P: n/a
"Greg" <bk*****@gmail.comwrote:
>Rick's right. For the others let me explain further:

-The report is actually a Purchase Order (PO)
-We produce about 40 PO's per day
-About 35 of those are single-page PO's
-5 signatures are required on each PO (15cm high footer)
-using a page footer, the 35 PO's look great but the 5 multi-page ones
are wrong because there is a redundant footer on the first (and
second..etc) pages. I need a footer only on the last page.
-using a report footer, I get the footer only on the last page but all
PO's look strange with the footers in different positions following the
detail on the PO's
-I want all 40 PO's to have the footer at the bottom of the only(35) or
last(5) page.
-I don't want to lose whitespace (lower portion) on the first pages of
a multipage PO.

I tend to agree with Rick, I don't think there is a solution. Right now
I'm using a report footer but it doesn't look professional at all.

Thanks for any advice.

Greg
So here is my proposal. This should push the report footer to the
bottom of the last page. It should work for variable height report
header, detail section and report footer. Note: If you can set the
CanGrow and CanShrink properties to False for a control, you should
use the control's height instead of the fTextWidthOrHeight function in
the calculations.
Also my thanks to Steven Lebans. Without his TextWidth-Height
functions, I would have given up on this problem.

With kind regards
Matthias Kläy

Tthe code is not formatted for the newsreader, so you will have to
correct the line breaks.

Option Explicit
Option Compare Database

' How to print the report footer at the bottom of the last page
' Matthias Kläy, Kläy Computing AG, www.kcc.ch, 2006
' This uses the TextWidth-Height functions version 4.5 from Steven
Lebans, http://www.lebans.com/textwidth-height.htm
'

Private Const mclngTwipsPerCm As Long = 567 ' All measures are in
Twips, there are 567 Twips in a centimeter
Private maDetailHeight As Variant ' Array storing the
heights of the detail sections on the last page.
' The elements of this
array are of the form Array(Counter, Height),
' Where Counter is a
unique identifier for each detail section,
' and Height is the
corresponding detail height.

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

Const clngDelta As Long = 60 ' Vertical distance between the fields
in the report footer

Dim lngRFHeight As Long
Dim lngTop As Long
Dim lngPosY As Long
Dim lngDetailHeight As Long
Dim i As Long

On Error GoTo kErrLabel

' Position the fileds at the bottom of the page
If (Me.Page = Me.Pages) Then
' Calculate the height of the report footer
lngDetailHeight = 0
For i = 1 To UBound(maDetailHeight)
lngDetailHeight = lngDetailHeight + maDetailHeight(i)(1)
Next
maDetailHeight = Array() ' This code may run twice if printed from
preview!

' Paper size A4 - Top Margin - Bottom Margin - Tolerance = 29.7cm -
1.0cm - 1.0cm - 0.2cm = 27.5cm
If Me.Pages = 1 Then
lngRFHeight = 27.5 * mclngTwipsPerCm - lngDetailHeight -
Me.Section("ReportHeader").Height -
Me.Section("PageFooterSection").Height
Else
If lngDetailHeight 0 Then
lngRFHeight = 27.5 * mclngTwipsPerCm - lngDetailHeight -
Me.Section("PageHeaderSection").Height -
Me.Section("PageFooterSection").Height
Else
lngRFHeight = 27.5 * mclngTwipsPerCm -
Me.Section("PageHeaderSection").Height -
Me.Section("PageFooterSection").Height
End If
End If

Me.Section("ReportFooter").Height = lngRFHeight

' Build up the footrt from bottom to top
' We assume three fileds in the footer:
'
' Me!txtThird
' Me!txtSecond
' Me!txtFirst
'

lngPosY = lngRFHeight - 10 ' We need some space at the bottom,
otherwise there will be errors (control too large to fit the section)

lngTop = lngPosY - Me!txtFirst.Height
Me!txtFirst.Top = lngTop
lngPosY = lngTop - clngDelta

lngTop = lngPosY - Me!txtSecond.Height
Me!txtSecond.Top = lngTop
lngPosY = lngTop - clngDelta

lngTop = lngPosY - Me!txtThird.Height
Me!txtThird.Top = lngTop
lngPosY = lngTop - clngDelta
End If

Exit Sub
kErrLabel:
If Err.Number = 2100 Then ' The control or subform control is too
large for this location.
Err.Clear
lngRFHeight = lngRFHeight + 0.1 * mclngTwipsPerCm
On Error Resume Next
Me.Section("ReportFooter").Height = lngRFHeight
If Err.Number = 0 Then ' It may happen that the error 2100 occurs
again here, in this case, we quit the processing.
Resume
End If
Else
' Standard error handling here
End If
End Sub

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

Const clngDelta As Long = 75 ' Vertikaler Abstand zwischen Controls

Dim lngPosY As Long
Dim lngHeight As Long

On Error GoTo kErrLabel

' We assume two fields in the Report Header
'
' Me!txtName
' Me!txtAddress

' Explicitely position all fields and calculate their effective height
lngPosY = 4 * mclngTwipsPerCm ' we start 4 cm below the top margin of
the paper

Me!txtName.Top = lngPosY
lngPosY = lngPosY + fTextHeight(Me!txtName) + clngDelta
Me!txtAddress.Top = lngPosY
lngHeight = fTextHeight(Me!txtAddress)
Me!txtAddress.Height = lngHeight
lngPosY = lngPosY + lngHeight + 0.2 * mclngTwipsPerCm
' etc

Me.Section("ReportHeader").Height = lngPosY + 5

Exit Sub
kErrLabel:
If Err.Number = 2100 Then ' The control or subform control is too
large for this location.
Me.Section("ReportHeader").Height =
Me.Section("ReportHeader").Height + 0.5 * mclngTwipsPerCm
Resume
Else
' Standard error handling here
End If
End Sub

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

Dim lngLinesA As Long
Dim lngLinesB As Long
Dim lngCtlGap As Long
Dim i As Long
Dim lngUB As Long
Dim lngDetailHeight As Long
Dim lExist As Boolean

If Me.Page = Me.Pages Then
' You will need a counter in your recordset that uniquely
determines the detail section.
' We assume that this counter is in the filed Me!txtCounter

lngUB = UBound(maDetailHeight)
lExist = False
For i = 1 To lngUB
lExist = maDetailHeight(i)(0) = Me!txtCounter.Value
If lExist Then Exit For
Next

' We assume that there are two controls in the detail section above
each other
'
' Me!txtA
' Me!txtB

lngLinesA = GetVisibleLines(Me!txtA)
lngLinesB = GetVisibleLines(Me!txtB)

lngCtlGap = 0
If lngLinesA 0 Then
lngCtlGap = 1
End If
If lngLinesB 0 Then
lngCtlGap = lngCtlGap + 1
End If

lngDetailHeight = (lngLinesA + lngLinesB) * Me!txtA.Height +
lngCtlGap * 10 ' 10 = vertical gap between controls

If Not lExist Then
If lngUB = -1 Then
lngUB = 1
ReDim maDetailHeight(1)
Else
lngUB = lngUB + 1
ReDim Preserve maDetailHeight(lngUB)
End If
maDetailHeight(lngUB) = Array(Me!txtCounter.Value,
lngDetailHeight)
Else
maDetailHeight(i)(1) = lngDetailHeight
End If
End If

End Sub

Private Sub Report_Open(Cancel As Integer)

' If you get a "bad" result with normal printing , open the report
with OpenArg "ForceNewPage"; this will force the report footer on a
new page
If Nz(Me.OpenArgs, vbNullString) = "ForceNewPage" Then
Me.Section("ReportFooter").ForceNewPage = 1 ' Before Section
End If

maDetailHeight = Array()

End Sub

Private Function GetVisibleLines(ctl As Control) As Long

Dim lngLines As Long

If (Trim$(Nz(ctl.Value, vbNullString)) = vbNullString) Or (ctl.Visible
= False) Then
GetVisibleLines = 0
Else
Call fTextWidthOrHeight(ctl, True, , , , lngLines)
GetVisibleLines = lngLines
End If

End Function
Oct 22 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.