Running Sum in Multipage Subreport

[Guest]

Hi everybody,

I've created an inventory management & invoicing application using Access
2003. Invoices consist of 2 reports: the invoice main report (rptInvoice) and
the invoice items sub report (subrptInvoice) placed in the Detail section of
rptInvoice. A running sum in the Detail section of subrptInvoice (=1; Over
Group) shows every item's position. If I open subrptInvoice on a stand-alone
basis, everything works fine (i.e. each running sum works correctly even
after page breaks).
However, if I open rptInvoice, the running sum only works on the 1st page.
So if an invoice spreads across several pages, the running sum resets to 1 on
top of every new page and starts to count again. Any idea on how to fix this
problem?

Any help is greatly appreciated

bronson

system:
Win xp home sp2
Office 2003 pro sp2
Intel P4 3,4 GHz
1 GB RAM

 

[Marshall Barton]

I've created an inventory management & invoicing application using Access
2003. Invoices consist of 2 reports: the invoice main report (rptInvoice) and
the invoice items sub report (subrptInvoice) placed in the Detail section of
rptInvoice. A running sum in the Detail section of subrptInvoice (=1; Over
Group) shows every item's position. If I open subrptInvoice on a stand-alone
basis, everything works fine (i.e. each running sum works correctly even
after page breaks).
However, if I open rptInvoice, the running sum only works on the 1st page.
So if an invoice spreads across several pages, the running sum resets to 1 on
top of every new page and starts to count again. Any idea on how to fix this
problem?

system:
Win xp home sp2
Office 2003 pro sp2
Intel P4 3,4 GHz
1 GB RAM

I have never heard of that happening without some other
setting being the cause of it. Subreports are unaware of
page boundaries so I can't see how that can have anything to
do with it.

Maybe there's something going on with grouping in the
subreport?? What happens if you set the running sum text
box to Over All?

If you need additional help, please provide more details
about how the subreport is oraganized.

 

[Guest]

Hi Marshall,

Thank you for your quick answer and apologies for the delay in responding to
you. As you suggested, I've changed the running sum to 'Over All' but
unfortunately the problem remains. I know that I must be missing something
here, but I have no idea what it might be. I’ve summarized the properties of
the items that should be of interest using the documenter. Here’s a brief
explanation on how both reports are structured and relate to each other:

On subrptInvoice, there are three group levels

Level0 (Group and Sort) is the InvoiceID (which is not the Invoice#, see
below)
Level1 (Sort only) is the ItemID
Level2 (Sort only) is the ItemName (for some items with the same ID, there
are several models/types which are then sorted alphabetically in order to
appear separately on the invoice)

The running sum is located in txtPosition and the Over Group property
relates to Level0

subrptInvoice is related to rptInvoice on the InvoiceID

The difference between InvoiceID and Invoice# is the following: During the
course of a year, each Invoice is given a number which is then reset to 11 at
the beginning of the next year. So for instance, there might be several
Invoices #20, depending on the year they have been issued (2005 or 2006 so
far). Therefore, in order to call up a specific invoice, the user enters both
the invoice# as well as the year of issue, both of which are then applied as
a filter in rptInvoice.
On the other hand, the InvoiceID is a unique ID used by Access for each
invoice, but which is not suitable for the user, since it doesn’t reflect the
way invoices are identified within the firm (Invoice# and Year).

Thanks again for your help

HTH

bronson



rptInvoice:

Properties
AutoCenter: False
AutoResize: True
BorderStyle: Dashes
CloseButton: True
Container: Reports
ControlBox: True
Count: 64
DateCreated: 25.08.2005 12:30:52
DateGrouping: Use System Settings
FastLaserPrinting: True
Filter: ([ARNr] = [Please enter Invoice#:] And Year([ARDate]) = [Please
enter Invoice year:])
FilterOn: False
GridX: 10
GridY: 10
GrpKeepTogether: Per Column
GUID: {guid {CB2724D6-BA4C-4B6F-AC92-4D4834170FED}}
HasModule: True
HelpContextId: 0
Hwnd: 197276
LastUpdated: 25.08.2005 12:30:52
LayoutForPrint: True
LogicalPageWidth: 10202
MaxButton: True
MinButton: True
MinMaxButtons: Both Enabled
Modal: False
Moveable: True
NameMap: Long binary data
OnOpen: [Event Procedure]
OrderByOn: False
Orientation: Left-to-Right
Owner: admin
PageFooter: Default
PageHeader: Default
Painting: True
PaletteSource: (Default)
Picture: (none)
PictureAlignment: Center
PicturePages: All Pages
PictureSizeMode: Clip
PictureTiling: False
PictureType: 0
PopUp: False
PrtDevMode: Long binary data
PrtDevNames: Long binary data
PrtMip: Long binary data
RecordLocks: No Locks
RecordSource: quniInvoices
UserName: admin
Visible: True
Width: 10091
WindowHeight: 14385
WindowLeft: -90
WindowTop: -480
WindowWidth: 21120

Objects

Group Level 0
ControlSource: ARNr
GroupFooter: True
GroupHeader: True
GroupInterval: 1
GroupOn: Each Value
KeepTogether: Per Column
SortOrder: False


Section: Detail
BackColor: 16777215
CanGrow: True
CanShrink: False
DisplayWhen: Always
EventProcPrefix: Detail
ForceNewPage: None
HasContinued: False
Height: 4308
InSelection: False
KeepTogether: False
Name: Detail
NewRowOrCol: None
OnFormat: [Event Procedure]
SpecialEffect: Flat
Visible: True
WillContinue: False


subrptInvoice:

Properties

AutoCenter: False
AutoResize: True
BorderStyle: Dashes
CloseButton: True
Container: Reports
ControlBox: True
Count: 29
DateCreated: 25.05.2005 14:47:21
DateGrouping: Use System Settings
FastLaserPrinting: True
FilterOn: False
GridX: 10
GridY: 10
GrpKeepTogether: Per Column
GUID: {guid {49DB3995-5FFE-4DD4-BC7B-EA36E2E73E06}}
HasModule: True
HelpContextId: 0
Hwnd: 262812
LastUpdated: 25.05.2005 14:47:21
LayoutForPrint: True
LogicalPageWidth: 11184
MaxButton: True
MinButton: True
MinMaxButtons: Both Enabled
Modal: False
Moveable: True
NameMap: Long binary data
OrderByOn: False
Orientation: Left-to-Right
Owner: admin
PageFooter: Default
PageHeader: Default
Painting: True
PaletteSource: (Default)
Picture: (none)
PictureAlignment: Center
PicturePages: All Pages
PictureSizeMode: Clip
PictureTiling: False
PictureType: 0
PopUp: False
PrtDevMode: Long binary data
PrtDevNames: Long binary data
PrtMip: Long binary data
RecordLocks: No Locks
RecordSource: quniInvoicePos
UserName: admin
Visible: True
Width: 9768
WindowHeight: 14385
WindowLeft: -90
WindowTop: -480
WindowWidth: 21120

Objects

Group Level 0
ControlSource: ID
GroupFooter: True
GroupHeader: True
GroupInterval: 1
GroupOn: Each Value
KeepTogether: Per Column
SortOrder: False

Group Level 1
ControlSource: ItemID
GroupFooter: False
GroupHeader: False
GroupInterval: 1
GroupOn: Each Value
KeepTogether: Per Page
SortOrder: False

Group Level 2
ControlSource: ItemName
GroupFooter: False
GroupHeader: False
GroupInterval: 1
GroupOn: Each Value
KeepTogether: Per Page
SortOrder: False

Section: Detail
BackColor: 16777215
CanGrow: False
CanShrink: False
DisplayWhen: Always
EventProcPrefix: Detail
ForceNewPage: None
HasContinued: False
Height: 240
InSelection: False
KeepTogether: False
Name: Detail
NewRowOrCol: None
OnFormat: [Event Procedure]
SpecialEffect: Flat
Visible: True
WillContinue: False

Text Box: txtPosition
BackColor: 16777215
BackStyle: Normal
BorderColor: 0
BorderLineStyle: Solid
BorderStyle: Transparent
BorderWidth: Hairline
BottomMargin: 0
CanGrow: False
CanShrink: False
ControlSource: =1
ControlType: 109
DecimalPlaces: Auto
EventProcPrefix: txtPosition
FontBold: No
FontItalic: False
FontName: Lucida Console
FontSize: 10
FontUnderline: False
FontWeight: Normal
ForeColor: 0
Height: 220
HideDuplicates: False
IMEHold: False
IMEMode: 0
IMESentenceMode: 3
IsHyperlink: False
KeyboardLanguage:0
Left: -15
LeftMargin: 0
LineSpacing: 0
Name: txtPosition
NumeralShapes: System
OldBorderStyle: 0
ReadingOrder: Context
RightMargin: 0
RunningSum: Over Group
ScrollBarAlign: System
Section: 0
SpecialEffect: Flat
TextAlign: Right
TextFontCharSet: 0
Top: 15
TopMargin: 0
Vertical: False
Visible: True
Width: 405

 

[Marshall Barton]

This is not "something simple" and I have never heard of a
situation where a page boundary has had any effect on a
running sum text box's value.

The only odd thing I noticed is that txtPosition's Left
property is a negative value, which I think is impossible.
Based only on that thin clue, I suggest that you delete the
text box and recreate it.
--
Marsh
MVP [MS Access]

Thank you for your quick answer and apologies for the delay in responding to
you. As you suggested, I've changed the running sum to 'Over All' but
unfortunately the problem remains. I know that I must be missing something
here, but I have no idea what it might be. I’ve summarized the properties of
the items that should be of interest using the documenter. Here’s a brief
explanation on how both reports are structured and relate to each other:

On subrptInvoice, there are three group levels

Level0 (Group and Sort) is the InvoiceID (which is not the Invoice#, see
below)
Level1 (Sort only) is the ItemID
Level2 (Sort only) is the ItemName (for some items with the same ID, there
are several models/types which are then sorted alphabetically in order to
appear separately on the invoice)

The running sum is located in txtPosition and the Over Group property
relates to Level0

subrptInvoice is related to rptInvoice on the InvoiceID [snip some exrtraneous stuff]

subrptInvoice:
Objects

Group Level 0
ControlSource: ID
GroupFooter: True
GroupHeader: True
GroupInterval: 1
GroupOn: Each Value
KeepTogether: Per Column
SortOrder: False

Group Level 1
ControlSource: ItemID
GroupFooter: False
GroupHeader: False
GroupInterval: 1
GroupOn: Each Value
KeepTogether: Per Page
SortOrder: False

Group Level 2
ControlSource: ItemName
GroupFooter: False
GroupHeader: False
GroupInterval: 1
GroupOn: Each Value
KeepTogether: Per Page
SortOrder: False

Section: Detail
BackColor: 16777215
CanGrow: False
CanShrink: False
DisplayWhen: Always
EventProcPrefix: Detail
ForceNewPage: None
HasContinued: False
Height: 240
InSelection: False
KeepTogether: False
Name: Detail
NewRowOrCol: None
OnFormat: [Event Procedure]
SpecialEffect: Flat
Visible: True
WillContinue: False

Text Box: txtPosition []
ControlSource: =1 []
Height: 220 []
Left: -15 []
RunningSum: Over Group
Section: 0 []

I have never heard of that happening without some other
setting being the cause of it. Subreports are unaware of
page boundaries so I can't see how that can have anything to
do with it.

Maybe there's something going on with grouping in the
subreport?? What happens if you set the running sum text
box to Over All?

If you need additional help, please provide more details
about how the subreport is oraganized.

 

[Guest]

Hi Marsh,

I deleted and recreated txtPosition as you suggested, but this hasn't
resolved the issue. Thanks for that anyway. I keep you posted as soon as I
come across the solution (hopefully).

bronson

 

[Guest]

Hi Marsh,

As I found out so far, it seems that the reason for the running sum in
subrptInvoice to reset on each subsequent page (after the 1st) is that there
is another subreport below subrptInvoice on rptInvoice. When I remove this
second subreport, everything seems to work as it should. Now I'm exploring
ways to skip this second subreport...

rgds,

bronson

 

[Marshall Barton]

You have completely lost me now. Maybe I've missed the
whole point of your first question and we've been talking
about different things all along??

At this point it seems as if you are trying to get a running
sum in a subreport to continue across multple instances
(each main report detail). If that's the question, it gets
very tricky because each instance of a subreport is like
running the subreport separately. The subreport control's
LinkMaster/Child properties may also be an important part of
the issue because they do not come into play when you run
the subreport all by itself.

 

[Guest]

Hi Marsh,

In order to clear things up, here's the report structure that I have right
now:

rptInvoice (Detail):

controls
subrptInvoice
txtPosition (running sum) & other controls
controls
subrptOther
controls
controls

This is the structure where I can't get the running sum to work properly, if
it breaks across several pages.

However, if I remove subrptOther (see structure below), everything works fine.

rptInvoice (Detail):

controls
subrptInvoice
txtPosition (running sum) & other controls
controls

Now the question is why a subreport (subrptOther) that has nothing to do
with the running sum (on subrptInvoice) - both appear in the Detail section
of the same main report (rptInvoice) - is causing the running sum to reset
after page breaks?

rgds,

bronson

 

[Guest]

Hi Marsh,

Seems to me that I was on the wrong track. The issue has been resolved now.
In order not to get you anymore confused than you must be right now here's
what happened:

below subrptInvoice I had a series of aligned textboxes referring to the
footer section of subrptInvoice in order to get the totals. Then there was
subrptOther (for special invoiced items) and below that again another series
of textboxes for the totals. Now this second line of textboxes messed it all
up for some reason (and I still don't understand why). Anyway, I included the
totals directly in qryInvoice (the underlying query for rptInvoice) and
changed the controlsource on the report accordingly. It works! The running
sum is now working properly.

Sorry for all the confusion and thanks again for your help!

rgds,

bronson

 

[Marshall Barton]

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.

 

[Guest]

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]

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.

 

[Guest]

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

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]

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]

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.

 

[Guest]

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]

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.