"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
