Access Cookbook - Ken Getz [77]
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:
Import the basConditionalPageBreak module from 03-14.MDB into your database.
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
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).
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 × 1,440 = 12,600). Adjust this argument as necessary until the report breaks appropriately (see Section 3.14.3).
Set the detail section's Keep Together property to No to allow it to break.
Save and print the report, which should look like the sample report shown in Figure 3-35.
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 × 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