• Access
  • Access 97
  • SQL Server
  • Visual Basic
  • "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 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: (111 kb)

    2024 Point International Limited.  All rights reserved. Privacy Statement