Recipe 3.14 Keep a Report from Breaking at an Inappropriate Place

3.14.1 Problem

On some of your reports, you use the Keep Together property to keep a whole group together or to ensure that a group header won't print without at least one detail item. When detail items are long, you may not want to keep an entire detail item together; however, you do want to have a reasonable number of lines under the header so that the header won't be the last line on the report page. How do you make a report start a new page instead of printing the group header with just a single detail line at the bottom of a page?

3.14.2 Solution

You can use an event procedure called from a report's Format event to evaluate the length of a report page before it actually prints and take an action (in this case, activating a page break control) only if certain criteria are met. This technique uses the acbConditionalBreak function and a page break control. This solution demonstrates how to use acbConditionalBreak to force a page break if there is not enough room to print at least one line of text from the detail section under a group header.

Open 03-14.MDB and print the report rptBadBreaks. This typical business-address report, which has its detail section's KeepTogether property set to Yes, occasionally prints a page with the Category group header as the last line of the page, as shown in Figure 3-33.

Figure 3-33. Page 2 of rptBadBreaks shows an inappropriate break for New World Communications

Now print the rptConditionalBreaks report. Notice that it has avoided the bad break by moving the New World Communications record to the top of page 3 (see Figure 3-34).

Figure 3-34. rptConditionalBreaks moves New World Communications to the top of page 3

Follow these steps to avoid bad breaks in your own reports:

  1. Import the basConditionalPageBreak module from 03-14.MDB into your database.

  2. Create a new report or open an existing one in design view. Select the group header you want to keep together with some text. Insert a page break control above any other controls in this group section (you may need to move some controls down a bit). You can see the page break control above the txtCompany text box in the company header section of the sample report, rptConditionalBreaks, in design view in Figure 3-35.

Figure 3-35. rptConditionalBreaks in design view
  1. If it's not already open, open the group header properties sheet (View Properties) and set Force New Page to None and Keep Together to Yes (this ensures that the group section itself won't be broken up).

  2. Enter the following expression in the OnFormat property (substituting the name of your page break control for "PageBreak1" if it is different):

    =acbConditionalBreak ([Report], 12600, [PageBreak1])

    We used 12,600 in the previous function call to indicate that we want a break at 8.75 inches (8.75 x 1,440 = 12,600). Adjust this argument as necessary until the report breaks appropriately (see Section 3.14.3).

  3. Set the detail section's Keep Together property to No to allow it to break.

  4. Save and print the report, which should look like the sample report shown in Figure 3-35.

3.14.3 Discussion

The acbConditionalBreak function forces a page break if the section will print at or below the specified location on the page. This function takes three arguments: a report object variable, the point at which to force a new page in twips, and an object variable pointing to the page break control that you wish to make visible if the section's location is at or below the specified position.

Here is the acbConditionalBreak function:

Function acbConditionalBreak(rpt As Report, intBreak As Integer, ctl As Control)

   ctl.Visible = (rpt.Top >= intBreak)

End Function

Access evaluates the expression to the right of the equals sign ((rpt.Top >= intBreak)) as either True or False and then assigns that value to the expression to the left of the equals sign. Thus, the code makes the page break control visible or invisible, depending on whether the current page top value has gone beyond the value in intBreak. When the control is made visible, a page break is forced before the section is printed.

You may need to experiment with different numbers for the intBreak argument until you get it working right for your report. Start by measuring the amount of vertical space needed to print a group header, together with the minimum number of detail lines you want to print with it. Add to this amount the height of the page footer. If you are measuring in inches, multiply this sum by 1,440 to convert it to twips; if you are measuring in centimeters, multiply the sum by 567. Subtract the resulting amount from the total height of the page in twips (15,840 = 1,440 x 11 for a standard letter-sized sheet in portrait orientation). This will give you a starting point; adjust as necessary until the report starts a new page unless there is enough room to print the number of lines you want under a group heading.

You can determine the amount of blank space to leave between the bottom of the last address on a page and the footer by changing the twips value in the acbConditionalBreak function. The current value allows a generous amount; to save space, you can reduce the twips argument by a few hundred twips.

Several report properties affect how a page (or column in a multiple-column report) breaks. For many reports, you may be able to use some combination of these properties instead of the technique used in this solution. The properties are listed in Table 3-12.

Table 3-12. Properties that affect where a page or column breaks

Property set

Property name




Controls whether groups in a report that have their KeepTogether property set to Whole Group or With First Detail will be kept together by page or by column.



When set to Whole Group or With First Detail, Access attempts to keep all of the sections of a group (header, footer, and detail) on the same page (or column).



When set to Whole Group or With First Detail, Access attempts to keep the whole section on the same page (or column).



Tells Access to force a new page never, before, after, or before and after the section.


NewRow or NewCol

Similar to ForceNewPage, except this property tells Access to force a new row or column never, before, after, or before and after the section. If you select "Across, then Down" in the Column Layout option in the Layout tab of the Page Setup dialog, a new row is started; if you select "Down, then Across", a new column is started.



When set to Yes, Access will repeat this section at the top of the next page (or column) when the group spans more than one page (or column).