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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200705/1

prairiewind via AccessMonster.com wrote:

>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

-- 
Marsh
MVP [MS Access]

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.

Marshall Barton wrote:
>>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
>

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200705/1