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:
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 x 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.
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.
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). |