ACC2000: How to Avoid Abandoned Group Headers in Reports

This article shows you a routine to keep a given number of records together on a page with their group header. The article also tells you 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 2000 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.

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.

In this article, Method 1 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 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 is 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: Add the following values together: 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 fit on a page. Force a new page if fewer than 8 records 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: Final calculation for the function: 6 inches or (6 * 1440 twips)

To implement this calculation in a module, follow these steps:

1. Type the following code into a new standard module:

   Option Explicit
   
      Function PrintOK(R As Access.Report)
         If R.Top > (6 * 1440) Then
            R.MoveLayout = True
            R.PrintSection = False
            R.NextRecord = False
         End If
      End Function
   					

2. Open your report in Design view.
3. View the properties for the report's group header.
4. Enter the following expression in the OnPrint property of the group header:
   =PrintOK([Report])

Method 2

Another way to avoid an abandoned group header at the bottom of a page, or to not 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: NOTE: This function does not work properly if the column layout is set to horizontal (in Print Setup).