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:

Figure 3-35. rptConditionalBreaks in design view

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	Effect
Report	GrpKeepTogether	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.
Group	KeepTogether	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).
Section	KeepTogether	When set to Whole Group or With First Detail, Access attempts to keep the whole section on the same page (or column).
	ForceNewPage	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.
	RepeatSection	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).