ACC: How to Avoid Abandoned Group Headers in Reports (100920)

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes a routine to keep a given number of records together on a page with their group header. The article also explains how to avoid an abandoned group header at the bottom of a page and how not to print the group header beyond a certain position on the page in a single- column or multiple-column report.

NOTE: For Single columns, the KeepTogether property for groups in Microsoft Access 2.0, 7.0, and 97 gives you the ability to keep groups of similar information together. This property is available in the Sorting And Grouping dialog box for reports. However, this property only prevents page breaks from occurring within a group; it does not prevent column breaks. To prevent column breaks from occurring within a group, use one of the methods described in this article.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

The KeepTogether property is used to keep all of the controls for one record together in a particular section of a report. This property does not keep multiple records together, nor does it keep a group header and its detail records together.

Method 1 below helps you to find the location on the page beyond which you should not print a group header if you want to keep a given number of records together with that header.

Method 2 below helps you to determine the twip location by trial and error and describes two sample functions, one for a single-column report and the other for a multi-column report.

Method 1

To keep a set number of records together on a single page with the group header, you need to know approximately how many records will be printed per group, or, more importantly, how many records you want to print with a particular group header. You can preview your report to find the group that prints the most records to determine how many records to keep with the group header.

For example, say you have determined that the largest group prints 24 records. The detail section of your report is 1/4 inch in height; thus, 24 detail records take up 6 inches on a page. You must also consider the top and bottom margins, which default to 1 inch each, and the height of your group header section.

Using the Report.Top property, you can determine where on the page the next section will be printed. If enough space remains to print your group header, all of its detail records, and the page footer (if any), plus the bottom margin, your group header will be printed on the same page as its detail records. If there is not enough blank space, delay printing the report (use a combination of the MoveLayout, NextRecord, and PrintSection properties to leave the rest of the page blank) and continue the group header information on the next page.

Below are sample measurements on which this article's calculations are based:

    Page Length:    11 inches
    Detail.Height:  0.25 inch

Add the following values together:

    Top Margin:          1 inch
    Page Header.Height:  1 inch
    Group Header.Height: 0.5 inch
    Group Footer.Height: 0.5 inch
    Page Footer.Height:  1 inch
    Bottom Margin:       1 inch
    -----------------------------
    Total = 5 inches

To determine the blank space left on the page for detail records, subtract the total above from your Page Length property value (11 inches - 5 inches = 6 inches).

Divide the maximum space for detail records by the Detail.Height property value (6 inches / 0.25 inch). A total of 24 detail records will fit on a page. Force a new page if fewer than 8 records will print with the header (for example, if the formula returns either less than the sum of 8 * 0.25 inches or less than 2 inches of blank space).

To determine where the top of the 2-inch limit is, or the point beyond which you should not print the group header, add the following values together:

     Top margin 1 inch
     + Page Header.Height 1 inch
     + Remainder of detail section (6 inches - 2 inches)

     = 1 inch + 1 inch + (6 inches - 2 inches)

Final calculation for the function: = 6 inches or (6 * 1440 twips)

To implement this calculation in a module, use the following steps:

Enter the =PrintOK() function in the OnPrint property of your group header. (This function assumes a report name of MyReport.)

Type the following lines in the Declarations section of a module:

            Option Explicit

      Function PrintOK()
         Dim R As Report
         Set R = Reports!MyReport
         If R.Top > (6 * 1440) Then
            R.MoveLayout = TRUE
            R.PrintSection = FALSE
            R.NextRecord = FALSE
         End If
      End Function

Method 2

Another way to avoid an abandoned group header at the bottom of a page, or not to print the group header beyond a certain position on the page, is to replace (6*1440) in the function above with the twip location beyond which you do not want to print.

The report tests the Top property; if the condition is True, the report does not print the section. Instead, it moves down the report layout and attempts to print the section until the condition is False, which occurs at the top of the next page.

For a multi-column report, the function must be modified as follows:

      Option Compare Database
   Option Explicit
   '=====================================================================
   '
   ' Function to force to new column if at bottom of column.
   '  R is the name of the Report being formatted, as in
   '  Reports![ReportName], and
   '  Bottom is the value (in inches) at which a new column is forced.
   '  This function should be called from the OnPrint property of the Group
   '  Header.
   '====================================================================
   Function IfBottom (R As Report, Bottom As Integer)
      Dim YPos
      Static LastPos

      YPos = R.Top
      If YPos > Bottom * 1440 Then  ' Beyond this position, go to a new
                                    ' column.
         R.MoveLayout = True
         R.NextRecord = False
         R.PrintSection = (YPos = LastPos) ' Set to True when at Max
                                           ' position.
         LastPos = YPos
      End If
   End Function

NOTE: This function will not work properly if Item Layout is set to Horizontal (in Print Setup) for the columns.