Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 03-14-2009, 01:49 PM   #46
ansentry
Access amateur
 
ansentry's Avatar
 
Join Date: May 2003
Location: Melbourne, Australia
Posts: 995
Thanks: 0
Thanked 7 Times in 7 Posts
ansentry is on a distinguished road
Send a message via Skype™ to ansentry
Re: Hide Page Footer except last page

Brian,

Quote:
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.

__________________
Regards,



John A
ansentry is offline   Reply With Quote
Old 03-14-2009, 02:06 PM   #47
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,394
Thanks: 20
Thanked 307 Times in 290 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: Hide Page Footer except last page

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.

Quote:
Originally Posted by gemma-the-husky View Post
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
stopher is offline   Reply With Quote
Old 03-15-2009, 06:34 AM   #48
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,684
Thanks: 39
Thanked 535 Times in 517 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Hide Page Footer except last page

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

__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 03-15-2009, 06:36 AM   #49
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,684
Thanks: 39
Thanked 535 Times in 517 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Hide Page Footer except last page

Quote:
Originally Posted by ansentry View Post
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
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 03-15-2009, 07:14 AM   #50
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,302
Thanks: 51
Thanked 917 Times in 888 Posts
gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light
Re: Hide Page Footer except last page

[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
gemma-the-husky is offline   Reply With Quote
Old 03-15-2009, 08:11 AM   #51
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,684
Thanks: 39
Thanked 535 Times in 517 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Hide Page Footer except last page

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
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015

Last edited by Brianwarnock; 03-16-2009 at 02:40 AM. Reason: correction
Brianwarnock is offline   Reply With Quote
Old 03-19-2009, 09:38 AM   #52
Ziggy1
Newly Registered User
 
Join Date: Feb 2002
Location: Ontario, Canada
Posts: 462
Thanks: 2
Thanked 1 Time in 1 Post
Ziggy1 is on a distinguished road
Re: Hide Page Footer except last page

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!



Code:
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
Attached Files
File Type: zip SampleFooterDB.zip (67.7 KB, 85 views)

Ziggy1 is offline   Reply With Quote
Old 03-19-2009, 11:41 AM   #53
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,684
Thanks: 39
Thanked 535 Times in 517 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Hide Page Footer except last page

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
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 03-19-2009, 10:55 PM   #54
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,394
Thanks: 20
Thanked 307 Times in 290 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: Hide Page Footer except last page

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
stopher is offline   Reply With Quote
Old 03-20-2009, 06:28 AM   #55
Ziggy1
Newly Registered User
 
Join Date: Feb 2002
Location: Ontario, Canada
Posts: 462
Thanks: 2
Thanked 1 Time in 1 Post
Ziggy1 is on a distinguished road
Re: Hide Page Footer except last page

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
Ziggy1 is offline   Reply With Quote
Old 05-17-2017, 11:06 PM   #56
liamhenry21
Newly Registered User
 
Join Date: May 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
liamhenry21 is on a distinguished road
Re: Hide Page Footer except last page

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.
liamhenry21 is offline   Reply With Quote
Old 05-18-2017, 01:31 AM   #57
ridders
Registered User
 
Join Date: Jan 2017
Location: Somerset, England
Posts: 521
Thanks: 19
Thanked 117 Times in 115 Posts
ridders is on a distinguished road
Re: Hide Page Footer except last page

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:

Code:
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
Attached Images
File Type: png Capture.PNG (9.7 KB, 50 views)
File Type: png Capture2.PNG (9.5 KB, 49 views)

__________________
Colin
(Access 2010 /2016, SQL Server, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
)


If this answer has helped, please click the Thumbs up symbol OR tip the scales on the left for me!

Last edited by ridders; 05-21-2017 at 10:03 AM.
ridders is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Totals in Page Heard instead of Report Footer royalrochelle Reports 1 05-26-2007 08:28 PM
Report header Vs Page header... and footer! Help! pduran Reports 7 01-16-2007 12:37 AM
Report Footer jumps to new page itlearner Reports 4 11-23-2006 02:17 PM
Change DATA on PAGE FOOTER based on page #. Is it possible? musicmaker Reports 2 09-12-2002 08:15 AM




All times are GMT -8. The time now is 05:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World