Access Report - Group footer showing up at top of page with no details.
jason
I'm having an Access 2000 problem that I haven't had any
luck solving.
Here's a little background info:
My report contains 3 sections.
1) A group header with a row of column headings
2) The detail section, which can have a few as one record
or row per group or many records that span multiple pages
per group.
3) A group footer that contains at least 1 and as many as
3 rows of data.
More than one group can appear on a page if the number of
records in the detail section warrant it.
The group header section must repeat at the top of each
page if the number of records causes the detail section to
spill over onto the next page. The group footer just has
to appear after the last record in the detail section of
that group.
The problem:
Sometimes there is enough room at the bottom of the page
to fit the group header and all of the records in the
detail section but not the group footer. In this case I
get one page with the group header and all the detail
records but no group footer. The group footer shows up
beneath the group header at the top of the following page.
I've played with the sorting and grouping feature in the
design view. Setting "Keep Together" to "Whole Group"
fixes this problem but creates a lot of white space.
Setting "Keep Together" to "With first detail" doesn't
appear to make a difference either.
I imagine playing with the VB code could get the result
I'm looking for. However, I'm an intermediate user that
has had little experience with VB.
Does anyone have an idea how to fix this via VB or in the
design view or can someone point me in the right direction?
Thanks much,
Jason
Marshall Barton
I'm sure you want to use the group's KeepTogether With First
Detail to take care of the top end of the group.
Unfortunately there is no corresponding With Last Detail,
which is what I think you really want.
The only way I can think of approaching this issue is **IF**
you know how tall the detail and the group footer are (i.e.
both of them have CanGrow set to No, or at least know their
maximum Height).
If you can live with that restriction, then you can check
for the last detail in the group and force a new page using
code. First, add a Page Break control at the very top of
the detail section, name it pgBreak. Next add a text box to
the group header section, name it txtGroupCount and set its
control source expression to =Count(*). Also add a text box
to the detail section, name this one txtLineCount, set its
control source expression to =1 and its RunningSum to Over
Group.
With all that in place, add code to the detail section's
Format event to make the page control visible or not
depending on if the last detail and the footer will fit on
the page:
Me.pgBreak.Visible = False
' Check if on last detail
If txtLineCount = txtGroupCount Then
'Check if there's no room for detail and footer
If Me.Top + Me.Section(0).Height _
+ Me.Section(6).Height > 10 * 1440 Then
Me.pgBreak.Visible = True
End If
End If
The 10 is the inches from the top of the paper to the bottom
margin, e.g. 8.5 x 11 inch paper and the bottom margin is 1
inch. Change this to match your arrangement.
--
Marsh
MVP [MS Access]
jason
Your fix did the trick.
Allow me to confirm the theory behind what you suggested:
At the last record in the detail section (i.e. where
txtLineCount=txtGroupCount) check to see if the sum of the
distance from the top of the page to the top of the detail
section, the detail section height, and the group footer
height is greater than the distance from the top of the
page to the bottom margin. If it is greater then execute
a page break.
Thanks a LOT!
Jason
>.
>
Marshall Barton
>Excellent work Marshall,
>
>Your fix did the trick.
All accolades gleefully accepted ;-)
>Allow me to confirm the theory behind what you suggested:
>
>At the last record in the detail section (i.e. where
>txtLineCount=txtGroupCount) check to see if the sum of the
>distance from the top of the page to the top of the detail
>section, the detail section height, and the group footer
>height is greater than the distance from the top of the
>page to the bottom margin. If it is greater then execute
>a page break.
Excellent, you got it in one!
>Thanks a LOT!
You're welcome, it was fun.