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.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:
- Coming up with/referring to a solution I'm not aware of at all
- Finding the issue with Solution 1 and helping me resolve it,
- Coming up with a workaround to allow Solution 2 to work, or
- ???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!!