report footer pushed to its own page

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

there are enough records in the detail section so that my report footer is
being pushed to its own page (it looks so lonely out there all by itself).
Is there a way to distribute the details so that this doesn't happen?

Thanks
Karen
 
Karen said:
there are enough records in the detail section so that my report footer is
being pushed to its own page (it looks so lonely out there all by itself).
Is there a way to distribute the details so that this doesn't happen?
Click to expand...


That can only be done if both the detail and report footer
sections have CanGrow set to No.

In this case, you can use a page break control (named
pgBreak) at the top of the detail section and, when its the
last detail, make it visible if both sections will not fit
on the page.

Your next question will probably be: how do you know when
its the last detail? This is done by adding a hidden text
box (named txtLineNum) to the detail section. Set its
control source expression to =1 and its RunningSum
to OverAll.

Then add another hidden text box (named txtTotalLines) to
the report header section and set its control source to
=Count(*)

Then the detail section event can use code like:

If txtLineNum = txtTotalLines _
And Me.Top + Me.Section(0).Height + Me.Section(2).Height
{paper height} - {top margin} - {bottom margin} -
Click to expand...
Me.Section(4).Height Then
Me.pgBreak.Visible = True
Else
Me.pgBreak.Visible = False
End If

If you do not have a page footer section, remove the
Section(4) part of the expression.
 
Thanks, Marsh. I will try it.
However: I am a complete Novice when it comes to VBA coding. (I learned
Fortran & Pascal in the mid-1980's, so I'm somewhat familiar with the concept
of coding.)
I've pretty much taught myself Access 2007 by using this discussion site and
the program's Help screens.
Is the "Me" in "Me.Top" the name of my report?
can I do a straight copy-paste of the coding you provided and insert the
appropriate names?
Something else I've tried: using some code provided on this site to get the
Report Footer to print on the bottom of the last page. I couldn't get it to
work, so I'm just leaving the footer in it's default location.
 
another question, because I'm not sure what I'm doing:
"Top", "Section(0)", "Section(2)", etc.
is "Top" the report header?
how do I relate "Section(#)" to the sections in my report? (I have a
special header in this particular report so that when it is used as a
subreport, the page headers will still show up in the main report.)
 
"Then the detail section event can use code like:"

is this an event applied to the detail section as a whole, and if so, on
what occurrence (on click, on format, etc.)?

is this an event applied to the txtLineNum, the hidden text box within the
detail section? On what occurrence?
 
See comments inline below.
--
Marsh
MVP [MS Access]

However: I am a complete Novice when it comes to VBA coding. (I learned
Fortran & Pascal in the mid-1980's, so I'm somewhat familiar with the concept
of coding.)
I've pretty much taught myself Access 2007 by using this discussion site and
the program's Help screens.
Is the "Me" in "Me.Top" the name of my report?
Click to expand...

Me is a special built in object that refers to the
form/report/class that contains the code using Me. It's is
a name neutral, short way to code that does the same thing
as Reports("name of report"). It is not strictly required
in some cases, but it does help to disambiguate the
following property/method/control.

Top is a property of all controls that specifies where the
control appears in its section. When used as I did, the
report's Top property is a special construct that provides
the position of the section on the page.

can I do a straight copy-paste of the coding you provided and insert the
appropriate names?
Click to expand...

That was the idea, but you should always study and code you
copy to make sure you understand what it does and how it
does it. A very good way to learn about coding in Access.
VBA Help is your best friend here.

Something else I've tried: using some code provided on this site to get the
Report Footer to print on the bottom of the last page. I couldn't get it to
work, so I'm just leaving the footer in it's default location.
Click to expand...

This is an entirely different (and not always solvable)
problem. I strongly suggest that you only work one problem
at a time.
 
Karen said:
another question, because I'm not sure what I'm doing:
"Top", "Section(0)", "Section(2)", etc.
is "Top" the report header?
how do I relate "Section(#)" to the sections in my report? (I have a
special header in this particular report so that when it is used as a
subreport, the page headers will still show up in the main report.)
Click to expand...


VBA Help really is your friend.

As I said before, Top is a property name and Section refers
to specific sections in the report:

Number Section
0 Detail
1 Report Header
2 Report Footer
3 Page Header
4 Page Footer
5 First Group Header
6 First Group Footer
7 Second Group Header
. . .
 
Karen said:
"Then the detail section event can use code like:"

is this an event applied to the detail section as a whole, and if so, on
what occurrence (on click, on format, etc.)?

is this an event applied to the txtLineNum, the hidden text box within the
detail section? On what occurrence?
Click to expand...


Sorry, I should have said "... the detail section's Format
event procedure ..."

Don't confuse the OnSomething ***property*** with the
related Something event procedure. The OnSomething property
is used to specify how Access can find the code/macro that
should be run when the Something event occures. The
property usually contains:
[Event Procedure]
which tells Access to call a specific procedure (e.g.
Report_Something, Detail_Something, etc).
 
I'm getting a run-time error - 464
Object required - on debug: points to my If statement (see below). I
didn't really understand the explanation of "Me" in "Me.Top", but here's this:

Here's my code, in the Detail / Format section of the VBA window (Private
Sub Detail_Format(Cancel As Integer, FormatCount As Integer)):

If TextLineNum = TextTotalLines And "the name of my report".Top + "the name
of my report".Section(0).Height + "the name of my report".Section(2).Height +
"the name of my report".Section(5).Height > (paperheight) - (TopMargin) -
(BottomMargin) Then
"the name of my report".PageBreak77.Visible = True
Else
"the name of my report".PageBreak77.Visible = False
End If

End Sub

TextLineNum: the hidden text box at the top of the detail; control source:
=1; running sum: Over All

TextTotalLines: the hidden text box in the report header; control source:
=Count(*)

PageBreak77: the name of the page break control at the top of the detail.

I added the height of Section(5) for my special header section.

Can you tell what I'm doing wrong?

I really appreciate your help!!

Karen
 
I THINK I GOT IT!!!

I found the VBA help and read somthing - I changed all my report names to
Me, and it looks like it worked.

You are one smart Cookie!

Marshall Barton said:
Karen said:
"Then the detail section event can use code like:"

is this an event applied to the detail section as a whole, and if so, on
what occurrence (on click, on format, etc.)?

is this an event applied to the txtLineNum, the hidden text box within the
detail section? On what occurrence?
Click to expand...


Sorry, I should have said "... the detail section's Format
event procedure ..."

Don't confuse the OnSomething ***property*** with the
related Something event procedure. The OnSomething property
is used to specify how Access can find the code/macro that
should be run when the Something event occures. The
property usually contains:
[Event Procedure]
which tells Access to call a specific procedure (e.g.
Report_Something, Detail_Something, etc).
Click to expand...
 
Karen said:
I THINK I GOT IT!!!
Click to expand...

Great! I was worried that it might be a long painful
process from your previous post to the real code.
I found the VBA help and read somthing - I changed all my report names to
Me, and it looks like it worked.
Click to expand...

I can't say this enough: VBA Help is your friend.

You can get to a topic by just typing or double clicking a
word you don't understand and then hitting the F1 key. I
learned a lot by going through Help's table of contents as
if I was reading a book and following its examples.
You are one smart Cookie!
Click to expand...

You probably say that to all the guys ;-)

Good luck on the "next" problem, and come on back whenever
you have more questions.
 
Oh, Sir, I only compliment the ones who really deserve it!

New problem: remember when I mentioned that this report that I've been
working on is to be used as a subreport?

Well, the coding doesn't work when this subreport is inserted into the main
report.

Also: I tried using the same code in the main report with the names changed
accordingly, and Section(5) for my group header changed to Section(4) for the
main report page footer. I then get a run-time error: #2428 - "You entered
an invalid argument in a domain aggregate function."

I made sure to set up the main report just like the sub, and have checked
the names. I didn't make any changes to the expressions.

Any ideas?

It's getting late here, so I may not read your answer till after the
weekend. In that case, have a good one!

Karen
 
Karen said:
Oh, Sir, I only compliment the ones who really deserve it!
;-)


New problem: remember when I mentioned that this report that I've been
working on is to be used as a subreport?
Click to expand...

No, I didn't see anything about a subreport.
Well, the coding doesn't work when this subreport is inserted into the main
report.
Click to expand...

This is a complete roadblock because the page header/footer,
page break controls and the page event are ignored in
subreports.
Also: I tried using the same code in the main report with the names changed
accordingly, and Section(5) for my group header changed to Section(4) for the
main report page footer. I then get a run-time error: #2428 - "You entered
an invalid argument in a domain aggregate function."
Click to expand...

Nothing we were working on has anything to do with domain
aggregate functions so something else must be going on.
I made sure to set up the main report just like the sub, and have checked
the names. I didn't make any changes to the expressions.
Click to expand...

Maybe you can get a little help from Access by compiling the
modules and fixing anything that generates an error message.
(Use the Debug - Compile menu item.)
 
Hi, Marsh

Did you have a nice Easter? It was rainy, cool, & windy in my area.
Couldn't take the dogs to the Dog Park - didn't want Mud Puppies!

I'm working on applying the "lonely report footer" problem to my Main
Report. I've gotten the "TextTotalLines" in the report header figured out so
that it counts the total number of records (equal to 24 at the moment) in my
subreports (of which there are 4), but not the "TextLineNum" in the Detail
section (getting the main report to know what record it is currently working
with; i.e., if it's working with record 12 of 24, 9 of 24, etc.). The hidden
text box with control source set to =1 and running sum set to Over All.

On Friday I was getting a compile error relating to domain aggregate, but
getting the header count (total number of records) figured out took care of
that. Now I'm getting runtime error 13 - type mismatch. This might be
solved once I can get the Detail to know which record it is currently on.

Any suggestions? I've been looking around in this Discussion area and
haven't found it.
 
Karen said:
Did you have a nice Easter? It was rainy, cool, & windy in my area.
Couldn't take the dogs to the Dog Park - didn't want Mud Puppies!
Click to expand...

Yes it was a noce Easter. Most of my back yard (~60x120 ft)
is covered in playground wood chips just to avoid the mud
issue when exercising the dogs. The frisbee action can get
a little tricky as they dodge over and around all the
agility equipment.
I'm working on applying the "lonely report footer" problem to my Main
Report. I've gotten the "TextTotalLines" in the report header figured out so
that it counts the total number of records (equal to 24 at the moment) in my
subreports (of which there are 4), but not the "TextLineNum" in the Detail
section (getting the main report to know what record it is currently working
with; i.e., if it's working with record 12 of 24, 9 of 24, etc.). The hidden
text box with control source set to =1 and running sum set to Over All.
Click to expand...

I have no idea how you can deal with your subreport footer
issue by doing anything in the main report.
On Friday I was getting a compile error relating to domain aggregate, but
getting the header count (total number of records) figured out took care of
that. Now I'm getting runtime error 13 - type mismatch. This might be
solved once I can get the Detail to know which record it is currently on.
Click to expand...

You completely lost me here,
 
OK, I lost you.

I'm trying to get the Report Footer to have at least one detail record
showing on the page on which it prints (preventing the lonely footer). Same
situation as we were working on last week, EXCEPT I'm now working with a
Main Report that contains four subreports.

The coding that worked for a single report (no subs) uses "=Count(*)"
(hidden text box) in the report header, which tells us how many records are
in the detail section. We then use another hidden text box in the detail
section to tell us which record we are currently dealing with. The coding
for the Detail - Format event then checks to see if we are at the final
record, then checks the paper size, etc., and inserts the page break before
that last record so the footer will not end up by itself.

"=Count(*)" in the Main Report Header section doesn't work if there are
multiple subreports. But I did build an expression that results in the total
number of records from all the subreports, but I haven't been able to
discover the expression needed in the Detail section that determines what
record is being dealt with. Any ideas?

I'm also wondering if the calculations of Me.Top + page size and margins and
so forth will work in this particular case.

I hope I explained this a LITTLE better.

Thanks!

Karen
 
Karen said:
I'm trying to get the Report Footer to have at least one detail record
showing on the page on which it prints (preventing the lonely footer). Same
situation as we were working on last week, EXCEPT I'm now working with a
Main Report that contains four subreports.
Click to expand...

Which report footer, the main report's or one of the
subreport's?
The coding that worked for a single report (no subs) uses "=Count(*)"
(hidden text box) in the report header, which tells us how many records are
in the detail section. We then use another hidden text box in the detail
section to tell us which record we are currently dealing with. The coding
for the Detail - Format event then checks to see if we are at the final
record, then checks the paper size, etc., and inserts the page break before
that last record so the footer will not end up by itself.

"=Count(*)" in the Main Report Header section doesn't work if there are
multiple subreports. But I did build an expression that results in the total
number of records from all the subreports, but I haven't been able to
discover the expression needed in the Detail section that determines what
record is being dealt with. Any ideas?
Click to expand...

In a report header, =Count(*) counts the detail records in
the same report. It has no knowledge of anything going on
in any subreport.

Using a RunningSum text box in a report's detail section
numbers the details in that same report. It too has no idea
of anything in any subreports.
I'm also wondering if the calculations of Me.Top + page size and margins and
so forth will work in this particular case.
Click to expand...

Well, they work, but since I am not clear on whether you are
trying to do lonesome footer for the main report or one of
the subreports, I can't say if can do what you want. Don't
forget what I said about none of that doing any good if
either the detail or footer sections can grow. If you try
to do this in the main report, there is no way to influence
a subreport so that's out. OTOH, if the main report is
trying to do this to itself, the subreports most likely will
grow so, as far I know, that's a useless exercise too.
 
Back
Top