Footer only to appear once!

[Bob Vance]

Ok got the =1 Footer and the page break [pgEject]
But my subReport is in the Detail Section of the report!
Thanks Bob

Marshall when I add a group it always goes to "=1 Header" is that correct
and where do I find yes for the group footer, remembering I am using 2007
Thanks Bob

Marshall, Detail can grow yes
Sub report can grow No.

Well shucks, that rules out several ideas I was
contemplating.

BUT, I finally stumbled over something that works in my
tests (using A2003). It's a little strange so pay attention
;-)

First, add a group (View menu - Sorting and Grouping) using
the expression =1. Specify Yes for the Group Footer.

Add a Page Break control (named pgEject) at the top of the
group footer section. (The Height of the group footer
section doesn't seem to matter!?)

Use the View menu to display the Report Footer section and
move everything you have in the page footer (the subreport?)
to the Report Footer section. Make the page footer
invisible or set its Height to 0.

Then put this code in the report's module. (Make sure you
remove any residual code from our earlier attempts.)

Const TPI As Long = 1440
Const PAPERHEIGHT As Long = 11 * TPI
Const BOTTOMMARGIN As Long = 0.5 * TPI

Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)

Dim FooterTop As Long

FooterTop = PAPERHEIGHT - BOTTOMMARGIN _
- Me.Section(2).Height
If Me.Top >= FooterTop Then
Me.pgEject.Visible = True
Else
Me.pgEject.Visible = False
Me.GroupFooter1.Height = FooterTop - Me.Top
End If
End Sub

Double check that the group footer's OnFormat property is
set to [Event Procedure]

Do not ask me to explain why this worked. I have no idea
what Access is doing when it executes the true part of the
If when FormatCount=1 and the Else part when FormatCount=2.

 

[Bob Vance]

Marshall do you mind if I send you my db so as you could please do a fix on
my report?
Regards Bob (thanks for your help)

Ok got the =1 Footer and the page break [pgEject]
But my subReport is in the Detail Section of the report!
Thanks Bob

Marshall when I add a group it always goes to "=1 Header" is that correct
and where do I find yes for the group footer, remembering I am using 2007
Thanks Bob

Bob Vance wrote:

Marshall, Detail can grow yes
Sub report can grow No.


Well shucks, that rules out several ideas I was
contemplating.

BUT, I finally stumbled over something that works in my
tests (using A2003). It's a little strange so pay attention
;-)

First, add a group (View menu - Sorting and Grouping) using
the expression =1. Specify Yes for the Group Footer.

Add a Page Break control (named pgEject) at the top of the
group footer section. (The Height of the group footer
section doesn't seem to matter!?)

Use the View menu to display the Report Footer section and
move everything you have in the page footer (the subreport?)
to the Report Footer section. Make the page footer
invisible or set its Height to 0.

Then put this code in the report's module. (Make sure you
remove any residual code from our earlier attempts.)

Const TPI As Long = 1440
Const PAPERHEIGHT As Long = 11 * TPI
Const BOTTOMMARGIN As Long = 0.5 * TPI

Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)

Dim FooterTop As Long

FooterTop = PAPERHEIGHT - BOTTOMMARGIN _
- Me.Section(2).Height
If Me.Top >= FooterTop Then
Me.pgEject.Visible = True
Else
Me.pgEject.Visible = False
Me.GroupFooter1.Height = FooterTop - Me.Top
End If
End Sub

Double check that the group footer's OnFormat property is
set to [Event Procedure]

Do not ask me to explain why this worked. I have no idea
what Access is doing when it executes the true part of the
If when FormatCount=1 and the Else part when FormatCount=2.

 

[Marshall Barton]

Marshall do you mind if I send you my db so as you could please do a fix on
my report?

Wouldn't do any good if I minded or not. My A2007 machine
is not setup to network to anything (don't ask) so I would
have a ton of problems getting setup to look at it.

Ok got the =1 Footer and the page break [pgEject]
But my subReport is in the Detail Section of the report!

Whatever. Just move the stuff you had in the page footer to
the group footer.
No guarantees. As I said, I don't dully understand why it
worked. A different version of Access may do some part of
it differently.

 

[Bob Vance]

Marshall all I can say "BRILLIANT" and Im not going to ask you how you did
it!
Much Appreciated for your time and effort with my major problem Thanks Bob

Marshall do you mind if I send you my db so as you could please do a fix
on
my report?

Wouldn't do any good if I minded or not. My A2007 machine
is not setup to network to anything (don't ask) so I would
have a ton of problems getting setup to look at it.

Ok got the =1 Footer and the page break [pgEject]
But my subReport is in the Detail Section of the report!

Whatever. Just move the stuff you had in the page footer to
the group footer.
No guarantees. As I said, I don't dully understand why it
worked. A different version of Access may do some part of
it differently.

 

[Bob Vance]

Marshall I also have a Batch print report that is similar to my other
report, It just lets me print all Owners statements with out selecting
individual Owners
The problem here that it has a "group on OwnerID"
It also has an OwnerID Header and a OwnerID Footer with nothing in the
footer

How can I put your expression into this report.......Thanks Bob

Marshall all I can say "BRILLIANT" and Im not going to ask you how you
did it!
Much Appreciated for your time and effort with my major problem Thanks Bob

Marshall do you mind if I send you my db so as you could please do a fix
on
my report?

Wouldn't do any good if I minded or not. My A2007 machine
is not setup to network to anything (don't ask) so I would
have a ton of problems getting setup to look at it.

Ok got the =1 Footer and the page break [pgEject]
But my subReport is in the Detail Section of the report!

Whatever. Just move the stuff you had in the page footer to
the group footer.

I am using 2007

No guarantees. As I said, I don't dully understand why it
worked. A different version of Access may do some part of
it differently.

 

[Marshall Barton]

Marshall all I can say "BRILLIANT" and Im not going to ask you how you did
it!
Much Appreciated for your time and effort with my major problem Thanks Bob

You're welcome. It was a seeerrrrious challenge and quite
interesting ;-)

HOW I did it is no secret, a lot of educated guesses and
trial and error. WHY it works is a completely separate
issue that I can not pretend to fully understand ;-)

Thank you for posting back that you got it to work in A2007.
I really didn't want to reproduce the whole scenario on my
quarantined A2007 machine.

 

[Bob Vance]

Marshall I also have a Batch print report that is similar to my other
report, It just lets me print all Owners statements with out selecting
individual Owners
The problem here that it has a "group on OwnerID"
It also has an OwnerID Header and a OwnerID Footer with nothing in the
footer

How can I put your expression into this report.......Thanks Bob

 

[Bob Vance]

This might help Marshall , The last Expression is yours .........Thanks Bob
http://viewmorepics.myspace.com/ind...ID=101007937&albumID=2158448&imageID=30967536
http://viewmorepics.myspace.com/ind...dID=101007937&albumID=499066&imageID=30967846

Marshall I also have a Batch print report that is similar to my other
report, It just lets me print all Owners statements with out selecting
individual Owners
The problem here that it has a "group on OwnerID"
It also has an OwnerID Header and a OwnerID Footer with nothing in the
footer

How can I put your expression into this report.......Thanks Bob

 

[gladys lane]

Marshall, Detail can grow yes
Sub report can grow No.

Well shucks, that rules out several ideas I was
contemplating.

BUT, I finally stumbled over something that works in my
tests (using A2003). It's a little strange so pay attention
;-)

First, add a group (View menu - Sorting and Grouping) using
the expression =1. Specify Yes for the Group Footer.

Add a Page Break control (named pgEject) at the top of the
group footer section. (The Height of the group footer
section doesn't seem to matter!?)

Use the View menu to display the Report Footer section and
move everything you have in the page footer (the subreport?)
to the Report Footer section. Make the page footer
invisible or set its Height to 0.

Then put this code in the report's module. (Make sure you
remove any residual code from our earlier attempts.)

Const TPI As Long = 1440
Const PAPERHEIGHT As Long = 11 * TPI
Const BOTTOMMARGIN As Long = 0.5 * TPI

Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)

Dim FooterTop As Long

FooterTop = PAPERHEIGHT - BOTTOMMARGIN _
- Me.Section(2).Height
If Me.Top >= FooterTop Then
Me.pgEject.Visible = True
Else
Me.pgEject.Visible = False
Me.GroupFooter1.Height = FooterTop - Me.Top
End If
End Sub

Double check that the group footer's OnFormat property is
set to [Event Procedure]

Do not ask me to explain why this worked. I have no idea
what Access is doing when it executes the true part of the
If when FormatCount=1 and the Else part when FormatCount=2.

 

[Marshall Barton]

Marshall I also have a Batch print report that is similar to my other
report, It just lets me print all Owners statements with out selecting
individual Owners
The problem here that it has a "group on OwnerID"
It also has an OwnerID Header and a OwnerID Footer with nothing in the
footer

I am having trouble finding time to set up a test for that
scenario. Rather than just leave you hanging I'll throw out
an idea that I would try first.

Start with a copy of the report you got to work earlier.
Then make all changes to the copy and, if we can get it to
work for this new report, I expect that you should be able
to use this new report for both scenarios.

Move the report footer stuff to your OwnerID group footer
and get rid of the report footer. Set this group footer
section's Name to OwnerFooter.

Add a second group with footer on the OwnerID field and set
this footer section's Name to Spacer. Move the code and
page break control we had in the =1 group in the other
report to this new group's footer. Get rid of the =1 group.

Change the code in the Spacer footer section to use thenew
section names instead of the generic names Access supplied:

Const TPI As Long = 1440
Const PAPERHEIGHT As Long = 11 * TPI
Const BOTTOMMARGIN As Long = 0.5 * TPI

Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
Dim FooterTop As Long
FooterTop = PAPERHEIGHT - BOTTOMMARGIN _
- Me.OwnerFooter.Height
If Me.Top >= FooterTop Then
Me.pgEject.Visible = True
Else
Me.pgEject.Visible = False
Me.Spacer.Height = FooterTop - Me.Top
End If
End Sub

 

[Bob Vance]

Marshall, My Batch Print Statements have a record source to a query where my
single print Statement does not so I don't think using my single print for
batch will work, maybe I should do a work around my batch Print.......Thanks
Bob

 

[Marshall Barton]

For testing purposes set this new report's record source to
your batch query.

After we get this new report working, we can tackle the
question of why you think you need two reports to do what is
essentially a filtering issue.

 

[Bob Vance]

Marshall got it working using my Batch report, but are getting a Error
,Enter Parameter Value on Spacer what should it be representing
.............Thanks Bob

 

[Bob Vance]

Ok I am changing my report that works and not my batch, BUT the report I
have on MyWeb page was the Batch print so I dont have a group OwnerID on the
Report that is working Only added the =1 Group..................Thanks Bob

Marshall got it working using my Batch report, but are getting a Error
,Enter Parameter Value on Spacer what should it be representing
............Thanks Bob

 

[Marshall Barton]

Spacer is just a name for the footer **section**. It sound
like you tried to use it in Sorting and Grouping as a field
name when you need to have two groups on the OwnerID field.

If that's what you did, then I don't see how it's possible
to get anything "working".

 

[Marshall Barton]

Ok I am changing my report that works and not my batch, BUT the report I
have on MyWeb page was the Batch print so I dont have a group OwnerID on the
Report that is working Only added the =1 Group

You lost me.

The web page is extremely difficult to make out and it did
not provide me with any useful information, so I have no
idea what you are trying to communicate here.

Start by adding a OwnerID group and then follow the steps in
my earlier posts.

 

[Bob Vance]

Thanks Marshall , I copied my Report that was working and made Record Source
to my query like in batch print , made your changes , I am getting 8 reports
for the same Client all the same! also I am getting Enter parameter for
tbPreviousInvoiceAmount and SubChildOwnerPaidAmount
Apart from that all works Good............Regards Bob

Private Sub Spacer_Format(Cancel As Integer, FormatCount As Integer)


Dim FooterTop As Long
FooterTop = PAPERHEIGHT - BOTTOMMARGIN _
- Me.OwnerFooter.Height
If Me.Top >= FooterTop Then
Me.pgEject.Visible = True
Else
Me.pgEject.Visible = False
Me.Spacer.Height = FooterTop - Me.Top
End If
End Sub

Spacer is just a name for the footer **section**. It sound
like you tried to use it in Sorting and Grouping as a field
name when you need to have two groups on the OwnerID field.

If that's what you did, then I don't see how it's possible
to get anything "working".
--
Marsh
MVP [MS Access]

Marshall got it working using my Batch report, but are getting a Error
,Enter Parameter Value on Spacer what should it be representing

"Marshall Barton" wrote

 

[Bob Vance]

Actually tbPreviousInvoiceAmount is not a Parameter Value error
now....Thanks Bob

Thanks Marshall , I copied my Report that was working and made Record
Source to my query like in batch print , made your changes , I am getting
8 reports for the same Client all the same! also I am getting Enter
parameter for tbPreviousInvoiceAmount and SubChildOwnerPaidAmount
Apart from that all works Good............Regards Bob

Private Sub Spacer_Format(Cancel As Integer, FormatCount As Integer)


Dim FooterTop As Long
FooterTop = PAPERHEIGHT - BOTTOMMARGIN _
- Me.OwnerFooter.Height
If Me.Top >= FooterTop Then
Me.pgEject.Visible = True
Else
Me.pgEject.Visible = False
Me.Spacer.Height = FooterTop - Me.Top
End If
End Sub

Spacer is just a name for the footer **section**. It sound
like you tried to use it in Sorting and Grouping as a field
name when you need to have two groups on the OwnerID field.

If that's what you did, then I don't see how it's possible
to get anything "working".
--
Marsh
MVP [MS Access]

Marshall got it working using my Batch report, but are getting a Error
,Enter Parameter Value on Spacer what should it be representing

Bob Vance wrote:

Marshall I also have a Batch print report that is similar to my other
report, It just lets me print all Owners statements with out selecting
individual Owners
The problem here that it has a "group on OwnerID"
It also has an OwnerID Header and a OwnerID Footer with nothing in the
footer


I am having trouble finding time to set up a test for that
scenario. Rather than just leave you hanging I'll throw out
an idea that I would try first.

Start with a copy of the report you got to work earlier.
Then make all changes to the copy and, if we can get it to
work for this new report, I expect that you should be able
to use this new report for both scenarios.

Move the report footer stuff to your OwnerID group footer
and get rid of the report footer. Set this group footer
section's Name to OwnerFooter.

Add a second group with footer on the OwnerID field and set
this footer section's Name to Spacer. Move the code and
page break control we had in the =1 group in the other
report to this new group's footer. Get rid of the =1 group.

Change the code in the Spacer footer section to use thenew
section names instead of the generic names Access supplied:

Const TPI As Long = 1440
Const PAPERHEIGHT As Long = 11 * TPI
Const BOTTOMMARGIN As Long = 0.5 * TPI

Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
Dim FooterTop As Long
FooterTop = PAPERHEIGHT - BOTTOMMARGIN _
- Me.OwnerFooter.Height
If Me.Top >= FooterTop Then
Me.pgEject.Visible = True
Else
Me.pgEject.Visible = False
Me.Spacer.Height = FooterTop - Me.Top
End If
End Sub

 

[Marshall Barton]

Thanks Marshall , I copied my Report that was working and made Record Source
to my query like in batch print , made your changes , I am getting 8 reports
for the same Client all the same! also I am getting Enter parameter for
tbPreviousInvoiceAmount and SubChildOwnerPaidAmount
Apart from that all works Good............Regards Bob

Private Sub Spacer_Format(Cancel As Integer, FormatCount As Integer)


Dim FooterTop As Long
FooterTop = PAPERHEIGHT - BOTTOMMARGIN _
- Me.OwnerFooter.Height
If Me.Top >= FooterTop Then
Me.pgEject.Visible = True
Else
Me.pgEject.Visible = False
Me.Spacer.Height = FooterTop - Me.Top
End If
End Sub

Parameter prompts come from your use of a field name that is
not in the report's record source. The unknown field name
might be in the report's record source query, Sorting and
Grouping or in a text box's control source. This issue has
nothing to do with the code that positions the footer.

 

[Bob Vance]

Thanks Marshall, just added tha sub reportto that name and made it
Invisible, But why would I be getting 8 copies of each
report!.................Thanks Bob