Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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

3. 
   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).

   
   

4. 
   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).

   
   

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

   
   

6. 
   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.