Running Sum in Multipage Subreport

I might have a cople of questions for you on the same topic (multipage
subreports).

1) How do I force a page break on the main report after the n-th entry in
the subreport? What I'm trying to do is:

On 1st page Main rpt: subrpt entry 1-25 (25 entries max)
On each subsequent Main rpt page: max 50 entries in subrpt

2) Could you tell me at which event (Report/Header/Detail/Footer) I can
hide/shrink controls in order to minimize the number of total pages? In other
words: which events precede the one where Access sets the Pages-property for
the entire report? In some cases, the report would fit on one page but Access
has set the Pages-property to 2. This triggers the display of page totals
which fill the bottom of the page instead of the tiny little bit which is
then displayed on the next page.

Thanks & rgds

bronson

"Marshall Barton" wrote:

I am really glad that you resolved it because I still don't
have a clue what could cause that effect. I don't think(?)
you were causing the confusion, it's just that what you were
describing is so wierd that I kept trying to find a
nonexistent(?) logical explanation.
--
Marsh
MVP [MS Access]

I think your use of subreport precludes the possibility of
doing page breaks after a specified number of subreport
details. As I tried to say before, the main report is in
charge of paging activities and subreport's have little
control of how a page is laid out.

You can manipulate the size/visibility of controls in the
Format event of the section that contains the controls.
However, this is rarely needed because the CanGrow/CanShrink
properties take care of 99.99% of the situations not
involving attached labels.

The entire report is formatted once to calculate the Pages
property, then the entire report is formatted a second time
to so it can display the value of Pages on any page. If you
are getting Page 1 of 2 on a one page report or Page 3 of 2
on a two page report, then you are doing some very unusual
things to get Access that confused.

Most of what you are describing might be better dealt with
if you scrapped the subreport and did it all in the main
report using grouping. It may get rather tricky, but I
think it's doable unless the subreport uses multiple
columns.
--
Marsh
MVP [MS Access]


bronson wrote:
I might have a cople of questions for you on the same topic (multipage
subreports).

1) How do I force a page break on the main report after the n-th entry in
the subreport? What I'm trying to do is:

On 1st page Main rpt: subrpt entry 1-25 (25 entries max)
On each subsequent Main rpt page: max 50 entries in subrpt

2) Could you tell me at which event (Report/Header/Detail/Footer) I can
hide/shrink controls in order to minimize the number of total pages? In other
words: which events precede the one where Access sets the Pages-property for
the entire report? In some cases, the report would fit on one page but Access
has set the Pages-property to 2. This triggers the display of page totals
which fill the bottom of the page instead of the tiny little bit which is
then displayed on the next page.


"Marshall Barton" wrote:
I am really glad that you resolved it because I still don't
have a clue what could cause that effect. I don't think(?)
you were causing the confusion, it's just that what you were
describing is so wierd that I kept trying to find a
nonexistent(?) logical explanation.

Hi Marsh

As you suggested, I restructured my entire application in order to get rid
of the subreports (using grouping within reports instead). However there is a
new problem waiting to be solved. Let's take the following example:

Pos Amount RunTot
1 10 10
2 5 15
3 25 40
4 15 55
5 20 75

I'm seeking to get page totals of txtAmount. In order to achieve that, I
place an invisible textbox in the detail section (let's name it txtRunTot):
=[Amount], Running Sum, Over Group
in the page footer section, the text box that displays the page total is set
to =[txtRunTot]
Let's assume, only Pos 1-3 fit on the first page. The running total for the
1st page in the page footer would then have to be 40. However, this is not
the result I get. I get 55 instead (40 + the first record on the next page).
Similarly, if I want to check the value of txtPosition and txtRunTot with a
MsgBox placed in the OnPrint Event of the Footer Section, I get Pos 4, RunTot
55 (The values I would have expected are 3 and 40 respectively). For some
reason, there is a disconnect between what's actually shown on screen/page
and the performed calculation which is "one record ahead of the display".
Would you have any explanation as to why this occurs? Is this a bug or am I
missing something here?

Thanks again for your thoughts on that

bronson

"Marshall Barton" wrote:

I think your use of subreport precludes the possibility of
doing page breaks after a specified number of subreport
details. As I tried to say before, the main report is in
charge of paging activities and subreport's have little
control of how a page is laid out.

You can manipulate the size/visibility of controls in the
Format event of the section that contains the controls.
However, this is rarely needed because the CanGrow/CanShrink
properties take care of 99.99% of the situations not
involving attached labels.

The entire report is formatted once to calculate the Pages
property, then the entire report is formatted a second time
to so it can display the value of Pages on any page. If you
are getting Page 1 of 2 on a one page report or Page 3 of 2
on a two page report, then you are doing some very unusual
things to get Access that confused.

Most of what you are describing might be better dealt with
if you scrapped the subreport and did it all in the main
report using grouping. It may get rather tricky, but I
think it's doable unless the subreport uses multiple
columns.
--
Marsh
MVP [MS Access]


bronson wrote:
I might have a cople of questions for you on the same topic (multipage
subreports).

1) How do I force a page break on the main report after the n-th entry in
the subreport? What I'm trying to do is:

On 1st page Main rpt: subrpt entry 1-25 (25 entries max)
On each subsequent Main rpt page: max 50 entries in subrpt

2) Could you tell me at which event (Report/Header/Detail/Footer) I can
hide/shrink controls in order to minimize the number of total pages? In other
words: which events precede the one where Access sets the Pages-property for
the entire report? In some cases, the report would fit on one page but Access
has set the Pages-property to 2. This triggers the display of page totals
which fill the bottom of the page instead of the tiny little bit which is
then displayed on the next page.


"Marshall Barton" wrote:
I am really glad that you resolved it because I still don't
have a clue what could cause that effect. I don't think(?)
you were causing the confusion, it's just that what you were
describing is so wierd that I kept trying to find a
nonexistent(?) logical explanation.

bronson wrote:
As you suggested, I restructured my entire application in order to get rid
of the subreports (using grouping within reports instead). However there is a
new problem waiting to be solved. Let's take the following example:

Pos Amount RunTot
1 10 10
2 5 15
3 25 40
4 15 55
5 20 75

I'm seeking to get page totals of txtAmount. In order to achieve that, I
place an invisible textbox in the detail section (let's name it txtRunTot):
=[Amount], Running Sum, Over Group
in the page footer section, the text box that displays the page total is set
to =[txtRunTot]
Let's assume, only Pos 1-3 fit on the first page. The running total for the
1st page in the page footer would then have to be 40. However, this is not
the result I get. I get 55 instead (40 + the first record on the next page).
Similarly, if I want to check the value of txtPosition and txtRunTot with a
MsgBox placed in the OnPrint Event of the Footer Section, I get Pos 4, RunTot
55 (The values I would have expected are 3 and 40 respectively). For some
reason, there is a disconnect between what's actually shown on screen/page
and the performed calculation which is "one record ahead of the display".
Would you have any explanation as to why this occurs? Is this a bug or am I
missing something here?


There are too many variables in your scenario for me to set
up and test, but it sounds like a fairly standard situation.
At this point, I can only come up with the suggestion that
you make sure you have set the detail section's KeepTogether
property to Yes. Without this, the fourth record may
actually be partially on the first page.

It appears you are doing things the right way, but double
check to make sure you are not using code in the detail
section's Format or Print events to manipulate the total.

The MsgBox in the Page Footer section's Print event is a
good way of verifying the value in the last detail on the
page so leave that in there until this problem is resolved.

--
Marsh
MVP [MS Access]

Hi Marsh

You did it! The problem was the Keep Together property which was set to No.
Everything works fine. Now, I've definitely learned to limit the use of
subreports wherever possible. I've only been using Access for 1 year so far
and when I originally built the application, I created subreports like
subforms. This turned out to be a capital error of judgement. Correcting it
was quite painful but it would certainly have been impossible without your
kind assistance. Thanks again!

bronson

"Marshall Barton" wrote:

There are too many variables in your scenario for me to set
up and test, but it sounds like a fairly standard situation.
At this point, I can only come up with the suggestion that
you make sure you have set the detail section's KeepTogether
property to Yes. Without this, the fourth record may
actually be partially on the first page.

It appears you are doing things the right way, but double
check to make sure you are not using code in the detail
section's Format or Print events to manipulate the total.

The MsgBox in the Page Footer section's Print event is a
good way of verifying the value in the last detail on the
page so leave that in there until this problem is resolved.

--
Marsh
MVP [MS Access]

Good to hear that you're up and running.

Subreports definitely have their place, but, compared to
forms, a report's sorting and grouping features go a long
way towards reducing their need.
--
Marsh
MVP [MS Access]


bronson wrote:
You did it! The problem was the Keep Together property which was set to No.
Everything works fine. Now, I've definitely learned to limit the use of
subreports wherever possible. I've only been using Access for 1 year so far
and when I originally built the application, I created subreports like
subforms. This turned out to be a capital error of judgement. Correcting it
was quite painful but it would certainly have been impossible without your
kind assistance. Thanks again!


"Marshall Barton" wrote:
There are too many variables in your scenario for me to set
up and test, but it sounds like a fairly standard situation.
At this point, I can only come up with the suggestion that
you make sure you have set the detail section's KeepTogether
property to Yes. Without this, the fourth record may
actually be partially on the first page.

It appears you are doing things the right way, but double
check to make sure you are not using code in the detail
section's Format or Print events to manipulate the total.

The MsgBox in the Page Footer section's Print event is a
good way of verifying the value in the last detail on the
page so leave that in there until this problem is resolved.