Group Detail on page if only 3 or less records, else allow to print on new page

[prairiewind via AccessMonster.com]

In my detail section, I sometimes have 2 records under the header and
sometimes up to 30. Is there a way to make sure when there are only 2
records that they are grouped with the header on a page, but if there are
more than 2, then they are allowed to print on a new page? Keep
Together|Whole Group leaves too much blank spaces when it forces a new page
when many records under the detail section, and Keep Together|WithFirstDetail
often splits the records when only 2. I'm needing a hybred between the two.

Thanks, Jeff

 

[Marshall Barton]

In my detail section, I sometimes have 2 records under the header and
sometimes up to 30. Is there a way to make sure when there are only 2
records that they are grouped with the header on a page, but if there are
more than 2, then they are allowed to print on a new page? Keep
Together|Whole Group leaves too much blank spaces when it forces a new page
when many records under the detail section, and Keep Together|WithFirstDetail
often splits the records when only 2. I'm needing a hybred between the two.

I've been thinking about this question since it was posted
and came to these conclusions.

If either the group header or the detail section can grow,
then I don't think this is possible. The group header would
need to do something in its Format event based on the final
height of the header and the first two details, but the
final height is not known until the second detail's Print
event. This kind of situation requires a partial,
multi-pass operation (retreat) that can not be controlled
through the report design capabilities.

OTOH, if none of these sections can grow, then it can be
done by adding a page break control (named pgBreak) to the
top of the group header section. To determine the number of
details, add a text box (named txtDetailCount) with the
control source expression =Count(*) to the group header.

Then you can use this kind of code to make the page break
visible or not depending the remaining space on the page:

Private Sub GroupHeader0_Format(Cancel As Integer, _
FormatCount As Integer)
Dim intdtl As Integer

intdtl = IIf(txtDetailCount > 2, 2, txtDetailCount)

Me.pgBreak.Visible = (Me.Top + Me.Section(5).Height _
+ intdtl * Me.Section(0).Height) _

(11 * 1440 - Me.Section(4).Height _

- Me.Printer.BOTTOMMARGIN)
End Sub

* 11 is the paper height in inches
* 1440 is the number of twips per inch
* drop the - Me.Section(4).Height term if there is no page
footer section

 

[prairiewind via AccessMonster.com]

Sorry if this is a double posted reply. Thanks Marshall for your solution
which worked great. I don't like to post a question unless I've first
searched for the answer, but couldn't find where anyone else has run into
this problem. All I needed to do was to change the page size! I've exported
the report to Word for a couple of years to get the formatting correct along
with to make an index, but thanks to this forum, both problems are fixed.
Thanks for all your thought and effort.

In my detail section, I sometimes have 2 records under the header and
sometimes up to 30. Is there a way to make sure when there are only 2

[quoted text clipped - 3 lines]

when many records under the detail section, and Keep Together|WithFirstDetail
often splits the records when only 2. I'm needing a hybred between the two.

I've been thinking about this question since it was posted
and came to these conclusions.

If either the group header or the detail section can grow,
then I don't think this is possible. The group header would
need to do something in its Format event based on the final
height of the header and the first two details, but the
final height is not known until the second detail's Print
event. This kind of situation requires a partial,
multi-pass operation (retreat) that can not be controlled
through the report design capabilities.

OTOH, if none of these sections can grow, then it can be
done by adding a page break control (named pgBreak) to the
top of the group header section. To determine the number of
details, add a text box (named txtDetailCount) with the
control source expression =Count(*) to the group header.

Then you can use this kind of code to make the page break
visible or not depending the remaining space on the page:

Private Sub GroupHeader0_Format(Cancel As Integer, _
FormatCount As Integer)
Dim intdtl As Integer

intdtl = IIf(txtDetailCount > 2, 2, txtDetailCount)

Me.pgBreak.Visible = (Me.Top + Me.Section(5).Height _
+ intdtl * Me.Section(0).Height) _
> (11 * 1440 - Me.Section(4).Height _
- Me.Printer.BOTTOMMARGIN)
End Sub

* 11 is the paper height in inches
* 1440 is the number of twips per inch
* drop the - Me.Section(4).Height term if there is no page
footer section