"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

    End If

End Sub

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

' Else hide page break control

         Me![pb1].Visible = False

    End If

End Sub

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.

Alex Dybenko

October 18, 1997.

Download: WO2-97.zip (111 kb)