report footer pushed to its own page

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

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?


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} -
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.

--
Marsh
MVP [MS Access]

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.

"Marshall Barton" wrote:

Karen wrote:

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?


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} -
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.

--
Marsh
MVP [MS Access]

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.)


"Marshall Barton" wrote:

Karen wrote:

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?


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} -
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.

--
Marsh
MVP [MS Access]

"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]


Karen wrote:
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?

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?

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.

This is an entirely different (and not always solvable)
problem. I strongly suggest that you only work one problem
at a time.


"Marshall Barton" wrote:

Karen wrote:

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?


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} -
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.

Karen wrote:

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.)


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
. . .

--
Marsh
MVP [MS Access]

Karen wrote:

"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?


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).

--
Marsh
MVP [MS Access]

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

"Marshall Barton" wrote:



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).

--
Marsh
MVP [MS Access]

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" wrote:

Karen wrote:

"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?


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).

--
Marsh
MVP [MS Access]