"Widows/Orphan" control in MS Access reports
When you design a MS Access report for printing multiple data rows and totals in footer, it will be better to keep one or more row from detail section with footer section to prevent it printing on a separate page. Unfortunately MS Access reports don't have such built-in feature. You can only keep whole section on the same page. Here is a tip how to implement such "widow/orphan" control in MS Access reports.
The trick is to calculate Pages property of report, which will force MS Access to format footer section two times, so you can calculate how many rows can be printed on the current page. To calculate this property is enough to place in any text box control source =Pages. For example you can add a text box in PageFooter section of report with control source =[Page] & " of " & [Pages]. Then you need to add a Detail rows counter, simply add a text box in detail section with control source =1 and Running Sum property - Over All. Let's name it [Enum]. And at last add a Page Break control [pb1] in the upper left corner of detail section.
Once you've added those controls, just add some VB code in reports' module. Declaration section:
'A variable to keep last row number
Dim lngLastRow As Long
' A variable to indicate, that it is necessary to add new page
Dim blnForceNewPage As Integer
'How many rows will be linked to footer
Const LINKROWS = 2
Then in Report footer On Format Event Procedure:
Sub ReportFooter4_Format (Cancel As Integer, FormatCount As Integer)
' MS Access can't place footer on the same page, then FormatCount = 2
If FormatCount > 1 And (Not blnForceNewPage) Then
' Remember last row number
lngLastRow = Val(Me![Enum])
' Ready to force new page
blnForceNewPage = True
And in Report Detail section On Format Procedure:
Sub Detail1_Format (Cancel As Integer, FormatCount As Integer)
' If current row must be placed on the next page
If blnForceNewPage And (Val(Me![Enum]) + LINKROWS - 1 = lngLastRow) Then
' Make Page break visible - so force new page
Me![pb1].Visible = True
blnForceNewPage = False
' Else hide page break control
Me![pb1].Visible = False
You can play with a sample database WO.MDB to see how it works. Form frmOrders has some sample orders with different numbers of detail rows.
October 18, 1997.
Download: WO2-97.zip (111 kb)