ACC: How to Avoid Abandoned Group Headers in Reports

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

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

    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

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)

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

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

2. 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

Keywords          : kbusage
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto