wanting the report footer to appear in the foot of extra pages

[graeme34 via AccessMonster.com]

Hi I have numerous reports that are working fine my only problem is if there
are numerous detail lines on the reports therefore causing an extra page, how
do I make the report footer, appear in the foot of the extra page. Currently
if there is no detail line on the second page and just the report footer it
is appearing in the upper half of the report whereas ideally it would be at
the foot of the page. Also can I dynamically alter the height of the detail
line.....for instance I have a statement report.....the detail line has a
line of debits....invoice detail then directly below the on the same detail
line I have a line of credits for that Invoice as my company allows 30 days
from end of month for payment there are a lot of detail lines that are half
empty..what I would like to do is have a condition on the controls in the
bottom half of the detail line and if the are empty reduce the height of the
detail line...is this possible??

 

[Marshall Barton]

Hi I have numerous reports that are working fine my only problem is if there
are numerous detail lines on the reports therefore causing an extra page, how
do I make the report footer, appear in the foot of the extra page. Currently
if there is no detail line on the second page and just the report footer it
is appearing in the upper half of the report whereas ideally it would be at
the foot of the page. Also can I dynamically alter the height of the detail
line.....for instance I have a statement report.....the detail line has a
line of debits....invoice detail then directly below the on the same detail
line I have a line of credits for that Invoice as my company allows 30 days
from end of month for payment there are a lot of detail lines that are half
empty..what I would like to do is have a condition on the controls in the
bottom half of the detail line and if the are empty reduce the height of the
detail line...is this possible??

You can use the CanShrink property on the controls and the
section to reclaim the space left by text boxes with a Null
or Zero Length String value. This does not apply to Label
or Line controls so you will have to make any or those
invisible in the section's Format event procedure.

Making the report footer section appear at the bottom of the
page is somewhat messy and is different in the older version
of Access than in later versions so you should tell us which
version you're using before we go into the details. It also
depends on if the footer section can grow or not so be sure
to include that information as well.

 

[graeme34 via AccessMonster.com]

Hi Marshall
Firstly thanks for the tip on the can shrink property.....obvious when you
think about it....but I'm learnig all the time thanks to this site and the
experts

Secondly on making the footer appear in at the foot.....I'm using Access 2003,
the first report (Invoice) is just the main report footer, 14 controls (7
Labels, 7 text boxes).....the other report (monthly statement) I have is
slightly different.....I have placed a sub/sub report in the footer the
sub/sub report has a label in the header and two text boxes in the detail...

If you need any more info let me know....thanks for your help!

Hi I have numerous reports that are working fine my only problem is if there
are numerous detail lines on the reports therefore causing an extra page, how

[quoted text clipped - 9 lines]

bottom half of the detail line and if the are empty reduce the height of the
detail line...is this possible??

You can use the CanShrink property on the controls and the
section to reclaim the space left by text boxes with a Null
or Zero Length String value. This does not apply to Label
or Line controls so you will have to make any or those
invisible in the section's Format event procedure.

Making the report footer section appear at the bottom of the
page is somewhat messy and is different in the older version
of Access than in later versions so you should tell us which
version you're using before we go into the details. It also
depends on if the footer section can grow or not so be sure
to include that information as well.

 

[Marshall Barton]

Secondly on making the footer appear in at the foot.....I'm using Access 2003,
the first report (Invoice) is just the main report footer, 14 controls (7
Labels, 7 text boxes).....the other report (monthly statement) I have is
slightly different.....I have placed a sub/sub report in the footer the
sub/sub report has a label in the header and two text boxes in the detail...

It doesn't matter what's in the report footer section. It
makes a big difference if it can grow or not. If its size
can change, then the problem becomes very difficult or even
impossible.

If the report footer section is a fixed height, then let's
try using this version independent approach approach. First
add a group level to the top of the Sorting and Grouping
list (View menu). Set the group's expression to =1 and its
Footer property to Yes.

Then set this group footer section's Height to a fairly
small value such as .1 inches and add a PageBreak control
named pgEject to the top of this section.

Finally, add some code to this group footer's Format event
procedure:

Private Sub GroupFooter0_Format(Cancel As Integer, _
FormatCount As Integer)
Static bolNotFirstTime As Boolean

Me.pgEject.Visible = False
If bolNotFirstTime Then
If Me.Top <= (10 * 1440 - Me.Section(2).Height _
- Me.ReportFooter.Height) Then
Me.NextRecord = False
End If
Else
If Me.Top > (10 * 1440 - Me.Section(2).Height _
- Me.ReportFooter.Height) Then
Me.pgEject.Visible = True
End If
bolNotFirstTime = True
Me.NextRecord = False
End If
End Sub

Where the 10 is the page height minus the bottom margin in
inches. Change this to Whatever your page and margin values
you are using.

 

[graeme34 via AccessMonster.com]

Hi Marshall...
It seems to work (slightly) ......it just need a few adjustments probably....
If you dont mind could you talk me through the procedure....
Me.Top....what part of the report is this ?
What is the argument i.e(2) after the Me.Section is this the margin size you
mentioned ?
If I require the footer to appear further down what do I have to alter...
Thanks again for your help Marshall...its v.much appreciated..

 

[graeme34 via AccessMonster.com]

Its Ok Marshall............I assumed the argument was the margin size...
altered it to mine(.8).....it working fine.......Thanks......one last
question if you dont mind..??
How can I cause the report to page break after say 8 lines of detail.

Hi Marshall...
It seems to work (slightly) ......it just need a few adjustments probably....
If you dont mind could you talk me through the procedure....
Me.Top....what part of the report is this ?
What is the argument i.e(2) after the Me.Section is this the margin size you
mentioned ?
If I require the footer to appear further down what do I have to alter...
Thanks again for your help Marshall...its v.much appreciated..
[quoted text clipped - 43 lines]

inches. Change this to Whatever your page and margin values
you are using.

 

[Marshall Barton]

Its Ok Marshall............I assumed the argument was the margin size...
altered it to mine(.8).....it working fine.......Thanks......one last
question if you dont mind..??
How can I cause the report to page break after say 8 lines of detail.

To break every N details you need to know which detail you
are working on. This is done by adding a text box (name it
txtDetail) to the detail section. Set its Control source to
=1 and set its RunningSum property to Over All or Over Group
as appropriate to your report.

Now add a page break control (name it pgEject) at the bery
bottom of the detail section.

Then use this code in the detail section Format event:
Me.pgEject.Visible = (Me.txtDetail Mod 8 = 0)

 

[graeme34 via AccessMonster.com]

Hi Marshall....
Thanks for trying but afraid I had no luck....when you mention detail section,
this is the subreport detail section isnt it......this is where I placed the
txtDetail, this did increment as I had the visible property to true and could
see it adding each time, but the page break didnt seem to kick in at 8 lines..
...also tried (Me.txtDetail Mod 4 = 0) I am right in guessing this should
only show 4 lines ??

Is this the only code required, or I am missing something??

Me.pgEject.Visible = (Me.txtDetail Mod 8 = 0)

Thank you.

 

[Marshall Barton]

I hadn't realized that you wanted to do that in a subreport.
Since the main report is in charge of paging actions, you
can not use a PageBreak control in a subreport (subreport's
do not display their page header/footer sections or trigger
their Page event either).

If the data in the subreport has a unique sort order, then
you can create a (slow?) query that calculates a sequential
number for each record. The query might look something
like:

SELECT f1, f2, ...,
DCount("*","thetable", "sortfield<=" & sortfield) As Seq
FROM thetable

Then use the query as the subreport's Record Source.

Now, you can add a group level on the Seq field with
interval of 8. Set this group header's ForceNewPage
property to Before Section (or use the group footer set to
After Section).

 

[graeme34 via AccessMonster.com]

Hi Marshall......sorry I didnt point out it was as subreport...and thank you
for your time in trying to help......the report will be to print out monthly
statements for customers at the end of the month....100+ reports.....dont
think a 'slow' query would ideal, just have to live with what the report
prints...but your time and help...greatly appreciated
Graeme..

I hadn't realized that you wanted to do that in a subreport.
Since the main report is in charge of paging actions, you
can not use a PageBreak control in a subreport (subreport's
do not display their page header/footer sections or trigger
their Page event either).

If the data in the subreport has a unique sort order, then
you can create a (slow?) query that calculates a sequential
number for each record. The query might look something
like:

SELECT f1, f2, ...,
DCount("*","thetable", "sortfield<=" & sortfield) As Seq
FROM thetable

Then use the query as the subreport's Record Source.

Now, you can add a group level on the Seq field with
interval of 8. Set this group header's ForceNewPage
property to Before Section (or use the group footer set to
After Section).

Thanks for trying but afraid I had no luck....when you mention detail section,
this is the subreport detail section isnt it......this is where I placed the

[quoted text clipped - 23 lines]