Results 1 to 7 of 7
  1. #1
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47

    Placing Variable Size Report Footer at Bottom of Report Without Using Extra Space

    Requirements and Information

    I have a report structured with a report header, page header, detail, group footer, and page footer. I would like this page footer to display exactly at the bottom of each page of the report. For all pages except the last page, there is a small amount of information to display, which takes up .6667". For the last page, there is a larger amount of information to display, which requires 1.5834". I want the footer to only take up that exact amount of space on each page. Additionally, I have controls in my detail section that can grow and must be able to grow. Specifically, there are three text fields which may grow, and two subreports that can grow. All of these are in the detail section of the report. Additionally, in the page footer is a control with Page and Pages values. In normal use, this report is output via the OutPutTo method, as a PDF file, without the report being opened.
    Unsuccessful Solutions
    The following two solutions definitely won't work:

    • Using a Report Footer, unmodified, for the last page of the report, and making the pagefooter's data not visible on the last page. This results in all pages except the last working correctly. However, on the last page, the Report Footer will be at a variable height on the page, depending upon the data placement.
    • Using a Page Footer and setting some data to not be visible on all pages except the last. This will result in the last page working correctly, but in all other pages, the page footer will take up too much space.

    Potential Solution 1

    I have attempted two more solutions that did not work, but perhaps could be fixed to work. This is the first of the two.
    In this attempt, I created a 'dummy' empty section footer, grouping by the entire report. I also create a Page Footer with the information I want on all pages but the last, and a Report Footer for the last page. Then, everything in the Page Footer is set to .Visible = False for the last page of the report, thusly:

    Code:
    Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    'Variable Definitions
    'Other Things Here
    If Me.Page = Me.Pages Then
        Me.Control1.Visible = False
        Me.Control2.Visible = False
        Me.Control3.Visible = False
        Me.Control4.Visible = False
        Me.Control5.Visible = False
    Else
        Me.Control1.Visible = True
        Me.Control2.Visible = True
        Me.Control3.Visible = True
        Me.Control4.Visible = True
        Me.Control5.Visible = True
    End If
    'Error Handing Here
    End Sub
    Next, I use a procedure to calculate the amount of space used on the last page of the report, thusly:

    Code:
    Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
    'More Variable Definitions Here
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rs0 As DAO.Recordset
    Dim sql1 As String
    Dim sql2 As String
    Dim sql0 As String
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer
    Dim d As Integer
    
    'Tbl1 is the main table used for the report. Foreignkey1 is used to filter the report. Thus, rs0 creates a clone of the main recordset in the report.
    sql0 = "select * from tbl1 WHERE tbl1.foreignkey1=" & Me.foreignkey1
    Set rs0 = CurrentDb.OpenRecordset(sql0)
    rs0.MoveFirst
    If Not (rs0.EOF And rs0.BOF) Then
        Do While Not rs0.EOF
    
            'tbl2 is a child table of tbl1, where ForeignKey2 is the primary key of tbl1. One-to-one relationship between tbl1 and tbl2.
            Set rs1 = CurrentDb.OpenRecordset("Select * from tbl2 where tbl2.ForeignKey2 =" & rs0("ForeignKey2"))
            rs1.MoveFirst
            If Not (rs1.EOF And rs1.BOF) Then
                Do While Not rs1.EOF
                   
                    'tbl3 is a child table of tbl2, and is the record source for SubReport1. ForeignKey3 is the primary key of tbl2, and is used as the master-child record to filter SubReport1. Thus, rs2 creates a clone of the recordset for SubReport1.
                    Set rs2 = CurrentDb.OpenRecordset("select * from tbl3 where tbl3.ForeignKey3 =" & rs1("ForeignKey3"))
                    rs2.MoveFirst
                    If Not (rs2.EOF And rs2.BOF) Then
                        rs2.MoveLast
    
                        'c Tracks the number of lines for this record in rs0. The largest individual number should be used, as records from tbl3 and tbl4 can be displayed on the same line.
                        c = rs2.RecordCount
                    End If
    
                    'tbl4 is a child table of tbl2, and is the record source for SubReport2. ForeignKey3 is used as the master-child record to filter SubReport2. Thus, this rs2 creates a clone of the recordset for SubReport2.
                    Set rs2 = CurrentDb.OpenRecordset("select * from tbl4 where tbl4.ForeignKey3 =" & rs1("ForeignKey3"))
                    If Not (rs2.EOF And rs2.BOF) Then
                        rs2.MoveLast
                        If rs2.RecordCount > c Then
                            c = rs2.RecordCount
                        End If
                    End If
                    Set rs2 = Nothing
    
    
                    'Checks the length of text1, 2, and 3. These can be displayed on the same line as each other and the records from tbl3 and tbl4, so the largest number should be used.
                    If IIf(Len(rs1("text1")) > 56, 5, IIf(Len(rs1("text1")) > 42, 4, IIf(Len(rs1("text1")) > 28, 3, IIf(Len(rs1("text1")) > 14, 2, 1)))) > c Then
                        c = IIf(Len(rs1("text1")) > 56, 5, IIf(Len(rs1("text1")) > 42, 4, IIf(Len(rs1("text1")) > 28, 3, IIf(Len(rs1("text1")) > 14, 2, 1))))
                    End If
                    If IIf(Len(rs0("text2")) > 30, 3, IIf(Len(rs0("text2")) > 13, 2, 1)) > c Then
                        c = IIf(Len(rs0("text2")) > 30, 3, IIf(Len(rs0("text2")) > 13, 2, 1))
                    End If
                    If IIf(Len(rs0("text3")) > 99, 4, IIf(Len(rs0("text3")) > 66, 3, IIf(Len(rs0("text3")) > 33, 2, 1))) > c Then
                        c = IIf(Len(rs0("text3")) > 99, 4, IIf(Len(rs0("text3")) > 66, 3, IIf(Len(rs0("text3")) > 33, 2, 1)))
                    End If
    
                    'a Tracks the total number of lines on the report. Sums the current C with each previous, then loops thru
                    a = a + c
    
                    'd Tracks the total number of records in this recordset. Used for a GroupFooter in final calculation
                    d = d + 1
                    rs1.MoveNext
                Loop
            rs0.MoveNext
            End If
            Set rs1 = Nothing
        Loop
    End If
    Set rs0 = Nothing
    
    
    'Now we have the total number of lines used on the entire report. We need to convert that to the number of lines used on the last page of the report.
    '42 Is the number of lines available on page 1. 52 lines are available on all other pages. Thus the following converts total number of lines to just the number of lines on the last page.
    If a > 42 And a < 94 Then
        a = a - 42
    ElseIf a >= 94 And a < 146 Then
        a = a - 94
    ElseIf a >= 146 And a < 198 Then
        a = a - 146
    ElseIf a >= 198 And a < 250 Then
        a = a - 198
    ElseIf a >= 250 Then
        a = a - 250
    End If
    
    
    'b Tracks the available height on the last page of the report. If the last page is also the first page, it includes the reportheader height.
    'Use a * height of each line to calculate the variable height section. Include all static sections. 400 is a static corrective factor that was found through trial and error. 
    '(Me.GroupFooter1.Height * d) is used as GRoup1 groups by the recordset tracked by d
    If me.Page = 1 then
        b = (11 * 1440) - Me.PageHeaderSection.Height - (240 * a) - Me.ReportFooter.Height - (Me.GroupFooter1.Height * d) - Me.PageFooterSection.Height - Me.Printer.TopMargin - Me.Printer.BottomMargin - 400 - Me.ReportHeader.Height
    Else
        b = (11 * 1440) - Me.PageHeaderSection.Height - (240 * a) - Me.ReportFooter.Height - (Me.GroupFooter1.Height * d) - Me.PageFooterSection.Height - Me.Printer.TopMargin - Me.Printer.BottomMargin - 400
    End If
    
    'Catches for any formula problem that results in a height of less than 0. Sets GroupFooter3 height equal to the available blank size. This effectively forces the ReportFooter to the bottom of the last page.
    If b < 0 Then
        Me.GroupFooter3.Height = 0
    Else
        Me.GroupFooter3.Height = b
    End If
    
    'More Code here
    'Error Handling Here
    End Sub
    The end result isn't terrible here, but does not come out exactly as required. Specifically, the placement of the ReportFooter is sometimes unreliable. Perhaps due to an error in my calculation above? Some variable I haven't accounted for? Also, the ReportFooter cannot go to the very bottom of the page on the last page. The space taken up by the Page Footer will displace the report footer up slightly. This second issue is not the end of the world, and might be acceptable if the first one could be resolved. However, after much trial and error and headscratching, I could not come up with a method to resolve the first issue. Perhaps one of you will fare better!

    Potential Solution 2

    The second solution I tried used no Report Footer, but only a Page Footer and a 'dummy' Group Footer, again grouped by the entire report. Here, I set heights and tops of all controls that I only want displayed on the last page to 0 for all pages except the last. Then, I set the height of the Page Footer itself to the smaller (.6667") size for those pages. When the page is the last page, I set the height of the Page Footer to the larger size (1.5834") and the heights and tops of these same controls to their desired positions. Here is the method:

    Code:
    Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    'Variable Definitions Here
    'More Code Here
    If Me.Page = Me.Pages Then
        Me.PageFooterSection.Height = 1.5834 * 1440
        Me.Control1.Height = 0.6667 * 1440
        Me.Control1.Top = 0.625 * 1440
    
        'Control2 is an attachment control, so I must set visible=false
        Me.Control2.Top = 0.55 * 1440
        Me.Control2.Height = 0.2813 * 1440
        Me.Control2Visible = True
        Me.Control3.Top = 0.7917 * 1440
        Me.Control3.Height = 1.0833 * 1440
        Me.Control4.Top = 1.2917 * 1440
        Me.Control4.Height = 0.3229 * 1440
        Me.Control5.Top = 1.2917 * 1440
        Me.Control5.Height = 0.375 * 1440
    
        'Another attachment control
        Me.Control6.Top = 1.2917 * 1440
        Me.Control6.Height = 0.375 * 1440
        Me.Control6.Visible = True
        Me.Control7.Top = 1.2917 * 1440
        Me.Control7.Height = 0.2917 * 1440
        Me.Control8.Top = 1.325 * 1440
        Me.Control8.Height = 0.2083 * 1440
    Else
        Me.Control1.Height = 0
        Me.Control1.Top = 0
        Me.Control2.Top = 0
        Me.Control2.Height = 0
        Me.Control2.Visible = False
        Me.Control3.Top = 0
        Me.Control3.Height = 0
        Me.Control4.Top = 0
        Me.Control4.Height = 0
        Me.Control5.Top = 0
        Me.Control5.Height = 0
        Me.Control6.Top = 0
        Me.Control6.Height = 0
        Me.Control6.Visible = False
        Me.Control7.Top = 0
        Me.Control7.Height = 0
        Me.Control8.Top = 0
        Me.Control8Height = 0
        Me.PageFooterSection.Height = 0.6667 * 1440
    End If
    'Error Handling Here
    End Sub
    This is still prone to one error. If the total length of the data section is long enough that there is not enough space for the larger Page Footer, but not so long that the data section goes to the next page, there is an issue. The smaller page footer will be displayed, and the Page | Pages control will show that there should be an additional page. However, that page will not generate, and the larger Page Footer does not appear. However, I came up to a solution to this issue using the dummy group footer I mentioned and similar code to that used in Solution 1. Specifically, I calculate the page space used as before, and if the amount used is in the range I described, I set the height of the dummy Group Footer to force the report to generate another page. The resulting report (as seen in Print Preview) is demonstrated in Example 1. I have removed the data from the report and placed text descriptors of the start/end of interesting sections. Note that the Page Footer is exactly at the bottom of the report, there is no data at the top of Page 2, and my dummy group footer is right at the top of Page 2. Essentially, the exact result desired.



    However, there is STILL an issue. Specifically, outputing the report , either via the OutputTo Method, the Save Object As method, or the Print method, will cause this to break. See Example 2 for the exact issue. Suddenly, the Page Footer does not shrink as desired on Page 1, forcing data to the top of Page 2, and pushing the dummy group footer down somewhat. Perhaps there is some workaround to prevent this issue and allow the report to be output exactly as displayed in the Print Preview mode? I certainly don't know of any, though.
    Conclusion/TLDR
    Example1.pdfExample2.pdf
    So, I would like to have a variable size footer at the exact bottom of every page of the report, without taking up any extra space, and with different information displayed on the last page. I have two ways that definitely do NOT work, and two ways that might, potentially, work, if resolved. I'm hoping one of you can help by either:

    1. Coming up with/referring to a solution I'm not aware of at all
    2. Finding the issue with Solution 1 and helping me resolve it,
    3. Coming up with a workaround to allow Solution 2 to work, or
    4. ???TeamLIquid???


    Apology/Thanks

    I realize this is a fairly difficult issue to parse. I do apologize for post length and any confusion. Thanks in advance to anyone that tries to help!!
    Last edited by darkwind; 05-22-2018 at 03:47 PM.

  2. #2
    AccessToGo is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2018
    Location
    UK
    Posts
    15
    Hi, sorry your question is too long and convoluted. I doubt that anybody will spend hours trying to understand/solve it for you for free. Let me try and get to the nub - Why are you fiddling about with formatting in report events? or rather, what is the original problem that caused you to start down the manual footer formatting route? e.g. control/section CanGrow/CanShrink or fixed height is usually pretty reliable, what was wrong with that standard approach which caused you to start event coding this solution? Maybe that snag ought to be your question. Regards Chris

  3. #3
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47
    Sadly, the Page Footer Section does not have a CanGrow or CanShrink property. The Report Footer of course does, but that is not really useful in this case.

    So to clarify the goal- get a report format with a small footer at the exact bottom of every page of the report, EXCEPT the last page. On the last page, get a larger footer at the exact bottom of the page. I don't need a variable size footer for either of those two areas, the height is fixed at .6667" for all pages except the last, and 1.5834" for the last page.

    A Report footer, with native functionality, is placed at the exact end of the report. That's a variable position on the page. A Page footer, due to the lack of CanGrow / CanShrink, must be the same height on all pages. That means if I use only a Page Footer with native functionality, and want the larger data on the last page, I have to have the large Page Footer size on every page.

    The result is, as far as I can tell, that I have to either come up with a way to manually force the Report Footer to the bottom of the page (what I attempted in Sol. 1), or come up with a way to re-size the Page Footer on the last page (what I attempted in Sol. 2).

    As far as the complexity/difficulty of the question - no worries, Chris. I certainly didn't expect someone to spend a ton of time on this. If someone were bored and wanted a challenge, I'd welcome their help. But someone noticing a simple oversight- like your advice would have been, if it worked- would be great. I realize the length is likely to turn people away, but for my initial post, I preferred to give more information over less. Even if it was perhaps overkill

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I also got put off by the length & complexity of the initial post.
    Anyway I'm not bored & have my own challenges to solve but luckily this is easy as you can see in the screenshot:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	20 
Size:	106.8 KB 
ID:	34187

    The example includes additional text (lblMore) on the first page footer only as well as changing the height for the last page

    Code used is:

    Code:
    Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    'note heights are in twips (567 twips/cm ; 1440 twips/inch) 
    'adjust as required
       If Me.Pages > 1 And Me.page < Me.Pages Then
            Me.PageFooterSection.Height = 500 
            Me.lblMore.visible = True
        Else
            Me.PageFooterSection.Height = 1500
            Me.lblMore.visible = False
        End If
    End Sub
    That's it - you can now bin all your complex calculations to the Access museum of tortuous coding
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK perhaps I have got too much time on my hands.
    Slightly modified version to improve layout of footer section:

    Code:
    Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)  
       If Me.Pages > 1 And Me.page < Me.Pages Then
            Me.PageFooterSection.Height = 100
            Me.txtDate.Top = 100
            Me.txtPages.Top = 100
            Me.lblMore.visible = True
            Me.lblLastPage.visible = False
        ElseIf Me.Pages > 0 Then
            Me.txtDate.Top = 1500
            Me.txtPages.Top = 1500
            Me.PageFooterSection.Height = 1500
            Me.lblMore.visible = False
            Me.lblLastPage.visible = True
        End If
        
    End Sub
    Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	107.7 KB 
ID:	34193

    Hopefully you can use the idea for your own situation
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47
    Hey Colin -

    That solution is essentially what I did in solution 2. However, it wasn't working for me on a couple fronts.

    If I simply use the .Visible property, the controls are still taking up space. That seems to prevent me from manually shrinking the size of the footer - see (VisibleProperty.pdf) for the resulting report. Here's the exact relevant code, as you described:

    Code:
    Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Page = Me.Pages Then
        Me.PageFooterSection.Height = 1.5834 * 1440
        Me.Control1.Visible = True
        Me.Control2.Visible = True
        Me.Control3.Visible = True
        Me.Control4.Visible = True
        Me.Control5.Visible = True
        Me.Control6.Visible = True
        Me.Control7.Visible = True
        Me.Control8.Visible = True
    Else
        Me.Control1.Visible = False
        Me.Control2.Visible = False
        Me.Control3.Visible = False
        Me.Control4.Visible = False
        Me.Control5.Visible = False
        Me.Control6.Visible = False
        Me.Control7.Visible = False
        Me.Control8.Visible = False
        Me.PageFooterSection.Height = 0.6667 * 1440
    End If
    End Sub

    That's why I instead:

    Code:
    If Me.Page = Me.Pages Then
        Me.Control1.Top = 0
        Me.Control1.Height = 0
        ...
    Else
        Me.Control1.Top = 1 * 1440
        Me.Control1.Height = 0.5 * 1440
        ...
    End If
    This is done with .CanGrow = False for Control1. This way, I shrink the controls to allow the footer to shrink, as there is no longer anything taking up that space.

    With that slight modification to your solution, the report looks fine in Print Preview, as here: PrintPreview.pdf. However, outputting the result to PDF or printing it will result in something significantly different- PDF Output.pdf. Note the different location of "no additional data" on the second page and the larger height of the page footer (starting just above the brown line) on page 1. So I'm not sure if this is an issue of some events firing differently when output to PDF (?) or a printer/driver issue, or something else causing that particular issue.

    I do appreciate your help!

  7. #7
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47
    I'm going to call this closed out for now.

    It's not totally resolved, but after some further testing I would call riddlers52's solution technically a resolution to the original problem as stated.

    I'll make a separate post for the problems that solution brought up... one with a little more focus ;P

    Thanks to those who worked on this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-06-2015, 11:57 AM
  2. How to force Page Footer to the bottom of report
    By nightangel73 in forum Reports
    Replies: 1
    Last Post: 06-20-2014, 11:46 AM
  3. Report printing extra pages of the same report.
    By khughes46 in forum Reports
    Replies: 5
    Last Post: 06-19-2014, 07:39 AM
  4. Replies: 1
    Last Post: 07-25-2013, 01:20 PM
  5. Replies: 7
    Last Post: 06-13-2012, 06:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums