Hide Page Footer except last page

[Brianwarnock]

Surely Visible/not visible is just that, it does not alter the space that ACCESS allows for the footer, therefore it should not alter the size of the report.
Edit I now know this not to be true

I also cannot understand why you cannot simply code for me.page=me.pages, I just know that I could not get it to work, I suspect that a section format cannot be changed during the life of a report, but will no doubt be proved wrong.

Brian

 

[stopher]

i still dont understand

accesses stock reports use the expression

="page " & [page] & " of " & [pages]

so you must be able to use these values.

------------
so the key is to have the pagefooter not visible normally. but make it visible on the last page. The only thing that i thought could happen with this, is that a 9 page report becomes 10 pages, because showing the footer on the "last" page, pushes the detail onto another page 10.

I couldnt replicate this - or at least i couldnt get a situation where I didnt see the final footer. So i'm not convinced that access cant resolve this on its own

As I said, i tested the page counts to make the footer visible in the page header - but perhaps you can do this test in the report footer, say

When using "pages", this forces the report to run twice - the first time is invisble to the user. It has to run twice in order to create a count and hence a value for "pages". I think we're all in agreement on this (?)

On the first run, the footers are set to visible so "pages" is calculated as 10 pages (for example).

But the footer is quite big, and on the second run, the footer disappears. This make the actual length of the report only 8 or 9 pages. So page=pages fails. Also page>=pages will never occur (except when equal).

Hence I still suggest not using "pages".

Chris (might be talking drivel as it's late on friday )

 

[ansentry]

In Access 2003 this works:

Me.Section([B]acPageFooter[/B]).Visible = [Page] = [Pages]

The code goes in the OnFormat of the PageFooter

 

[gemma-the-husky]

stopher put

On the first run, the footers are set to visible so "pages" is calculated as 10 pages (for example

no - thats the point - set this section to not visible in the properties section

just set it to visible for the last page only

 

[Brianwarnock]

John it doesn't work in 2002 even with me.page=me.pages, and it was not the technique you used in the link I posted.


Brian

 

[ansentry]

Brian,

John it doesn't work in 2002 even with me.page=me.pages, and it was not the technique you used in the link I posted.


Brian

If you were replying to my post, I don't understand the bold section.

 

[stopher]

I agree with Brian that the link he provided gives less than satisfactory result (you end up with space at the bottom of your pages except the last which won't look great with a big footer).

Setting pagefooter visible to false does indeed free up that space (I checked). I think we agree this is desirabe.

stopher put

no - thats the point - set this section to not visible in the properties section

just set it to visible for the last page only

I agree. I'd considered this before but dismissed it. No idea why. I agree what you suggest should work.

I have to say I'm getting lots of different results depending on the length of the detail (number of records). I can certainly get the desired effect under certain circumstances. But I can also get it to fail.

My latest thinking is this. Suppose Access is about to start the last page of the report. At this point it knows to print a page header but is not expecting to print the page footer (visible=false). So it knows the remaining detail will fit on that page.

Then, at some point after the detail we set the pager footer visible to true. But sometimes the footer will no longer fit on the bottom. It can't print the page footer on the next page on it's own. That would be wrong since it would mean that some of the detail should have spilled over.

So we can have a catch 22 situation. Suppose the detail extends into where the page footer should be on page 2, then we put the footer on page 2 - but this forces some detail to spill into page 3 (in which case putting a footer on page 2 would be wrong). Or we try to get the page footer to sit on Page 3 on its own - but how? - as far as the report is concern, it's finished - page footers don't sit on blank pages.

Actually my tests only partially agree with the above. For instance I'm getting different results depending on whether I have 1 record on the final page or two. 1 record prints a footer, 2 does not.

Chris

 

[Brianwarnock]

I agree with Stopher, especially that results are confusing,
I also think that althougth you can set visible=false for the page you are on you can only set visible =true for the next.

me.page=me.pages-1 works in all cases except the 1 page scenario.
I did get a system working in all cases except if the page detail was between the numbers for no footer and a footer.
A test with aset number of pages and testing for page = gave the interesting result that the first page footer was dependent on what I set the last page to.

I would give up on using page= unless somebody produces a working example.

Brian

 

[Brianwarnock]

Brian,

If you were replying to my post, I don't understand the bold section.

I posted a link in which you advised the op to use the technique of always printing the footer but setting the visibility of the contents therein.

Brian

 

[gemma-the-husky]

[this is a slightly modified version of my earlier post]

i still dont understand where the problem arises

access standard reports use the expression

="page " & [page] & " of " & [pages]

so you must be able to use these values - [page] and [pages]. I am sure access reads every report twice anyway - this first time, just to count the pages - eg just put a msgbox in reportfooter format, and you will see this happening - it needs to know the total number of pages, in order to let you select individual pages to print, which you get in the normal print... dialog

so the key is to have the pagefooter not visible normally. but make it visible on the last page only. The only thing that i thought could happen with this, is that a 9 page report becomes 10 pages, because the very act of showing the footer on the "last" page, reduces the space avaialble for the details section. and pushes some detail onto another page.

I couldnt replicate this behaviour - or at least i couldnt get a situation where I didnt see the final footer. So i'm not convinced that access can't resolve this on its own

As I said, i was testing the page counts to make the footer visible in the format event of the page header - and i think it is probably significant where you underttake this test

 

[Brianwarnock]

Your code is executed on both passes of the report creation, I don't think the setting in properties has any impact because the first pass through your code will set the visibility one way or the other.

I can always get the page footer onto the last page of a multi page report, the trick is to always get it onto the last page if there is a single page or multipage report, no matter how many detail lines there are on the page.

I do not have 2007 but I await a working DB that prints it on the last page using Me.page=Me.pages as my tests show that you have to use Me.page=Me.pages-1 in the footer section, ie it cannot turn visiblity on for the page it is currently building, with the exception of page 1 if you use me.page=1. If you put the code in the header then me.page=me.pages works immediately except for page 1. So I tried
If Me.page=me.pages and me.pages = 1 but still could not get the footer on a single first page.

Brian

 

[Ziggy1]

Hi guys,

I had a few days off so I wasn't thinking about work, but one of our Access Developers was able to help me out with this code, and is is working!

Option Compare Database
Option Explicit
'******************************************************************************
'* Program / Report Description;
'******************************************************************************
Private intReply        As Integer
Private strMessage      As String

'******************************************************************************
'* Report Footer Format:
'******************************************************************************
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

DoCmd.Echo False
DoCmd.SetWarnings False
On Error GoTo ErrorRoutine

    Me.pftrLine2.Visible = True
    If Me.Page = Me.Pages Then
        Me.Section(acPageFooter).Height = 4320    '***(3 * 1440 twips = 3 inches)
        Me.Text172.Visible = True
        Me.pftrLine1.Top = Me.Text172.Top + Me.Text172.Height + 360
    Else
        '*** Best to use the Else clause here to specify the normal value. Sometimes when
        '*** reports are viewed in Preview mode, you go back and forth between pages and
        '*** various events can again be triggered all of a sudden altering the report and
        '*** putting it into the wrong format.
        
        Me.Section(acPageFooter).Height = 1080    '***(.75 * 1440 twips = 3/4 inch)
    End If

EndOfProcedure:
    DoCmd.Echo True
    DoCmd.SetWarnings True
    Exit Sub

ErrorRoutine:
    '*** This little error handler isn't fancy but it's very valuable. It displays
    '*** the error code and description on screen when you're trying to debug.
    '*** Very helpful. Should be considered mandatory for any production program.
    
    strMessage = Err.Number & Chr(13) & Chr(13) & Err.Description
    intReply = MsgBox(strMessage, vbOKOnly, "Test Msg")
    Resume EndOfProcedure
End Sub

'******************************************************************************
'* Page Header Format:
'******************************************************************************
' this sub is unchanged...

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

Dim rs As DAO.Recordset ' set library reference 3.6
Dim mySQL As String
Dim RemStr  As String
Dim BolMsg As String
Dim BolMsg1 As String
Dim X As Integer 'msg counter
Dim LG1 As String
Dim rs2 As DAO.Recordset ' set library reference 3.6

'Me.txtREM = ""
  
'Set rs = CurrentDb.OpenRecordset("tblorderRemarks-Appended")
' EDI Remarks
' opens the table filtered by the order number
mySQL = "SELECT * FROM [tblorderRemarks-Appended] where [oeoo5_002] = " & "'" & Me.ord_hdr001 & "'"


LG1 = "Select SECT1,Sect2,SECT3 from tbllegal"
Set rs2 = CurrentDb.OpenRecordset(LG1)


Me.Text168 = Trim(rs2!Sect1)
Me.Text172 = Trim(rs2!Sect3)

rs2.Close


'Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblorderRemarks-Appended] where [oeoo5_002] = '596025'")

'MsgBox mySQL

Set rs = CurrentDb.OpenRecordset(mySQL)
  
        With rs
  
                Do Until .EOF
                    ' loops through records and appends to variable
                    RemStr = RemStr & " " & Trim(rs!oeoo5_006)
                    'Me.txtREM = Trim(RemStr)
            .MoveNext
  
                Loop
  
        .Close

        End With

' Cr 16 message
    mySQL = "SELECT * FROM [qryclientMessage] where [messag_cod] = 'C'"

    Set rs = CurrentDb.OpenRecordset(mySQL)
  
        With rs
    
                Do Until .EOF
  
                    For X = 1 To 10

                    BolMsg1 = rs("message_" & X)
                    BolMsg = BolMsg & " " & Trim(BolMsg1)

                    Next X

                Me.txtREM = Trim(RemStr) & vbNewLine & vbNewLine & Trim(BolMsg)
    
        .MoveNext
  
                Loop

          .Close

        End With

End Sub

 

[Brianwarnock]

Just a couple of points as it was difficult to do much testing
1 The page footer seems to be high up the page
2 If you add 12 more articles to the list the page footer and report footer print on top of each other.

Perhaps more would be revealed with more testing.

As you are going with the idea of keeping the page footer section always visible and just altering the visibility of the textbox , as suggested way back,it seems a very contrived solution.

Brian

 

[stopher]

I reckon this needs to be solved using the report footer. There's at least one situation where page footer cannot possibly work given the definition of a page footer.

My thinking is, work out how far down the page the report footer prints (on the first run), then add padding (or adjust the height) accordingly on the second run. I just haven't thought how to do this yet

Chris

 

[Ziggy1]

Brian/Stopher,


The general idea seems to be working with what my coworker gave me, but I will have to play around with it.

Thanks

 

[liamhenry21]

If you need to display a label in the page footer area for even pages and avoid blank (unused) space in the footer area on other pages, I'm afraid there is no appropriate way to accomplish this task immediately. The problem is that the ability to determine the current page number within the Beverprint event handler is currently not supported.

 

[isladogs]

This is a very old thread which has just been resurrected from the dead by @liamhenry21 ...

In all the many & expert responses to the original question, I don't think anyone suggested using conditional formatting to fulfil this.

This works for me
a) set all items in the footer as visible to prevent issues with total number of pages being adjusted

b) use conditional formatting to set each item's forecolor as white (paper colour) if [Page]<[Pages]

c) If you have a line in your footer, you can't use CF wizard on that
Instead manage the line using code to adjust the border colour or style:

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If [Page] < [Pages] Then
        Me.Line14.BorderStyle = 0
      '  Me.Line14.BorderColor = vbWhite
    Else
        Me.Line14.BorderStyle = 1
      '  Me.Line14.BorderColor = vbBlack
    End If
End Sub

If preferred, do the label colours in the PageFooterSection_Format event as well

Obviously you'd need to alter that setting if you use different colour paper!

I've just tried this on a report with 418 pages (& no, I would never actually print that myself...) & it works without noticeably slowing down the loading time.

As for the issue raised by @liamhenry21, you can achieve this for odd/even pages in the CF wizard using Mod 2 as follows:

This sets the footer text as white on ODD pages only

BTW:
I've tested & it all works for me when printed as well as in preview mode

It would be good if some of you could test these ideas on a variety of different reports