Running Sum in Multipage Subreport

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

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

--
Marsh
MVP [MS Access]

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)
Pictu (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)
Pictu (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" wrote:

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

--
Marsh
MVP [MS Access]

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]


bronson wrote:
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
[]


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


"Marshall Barton" wrote:
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.

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

"Marshall Barton" wrote:

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]

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

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]

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.
--
Marsh
MVP [MS Access]



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


"Marshall Barton" wrote:
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.

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

"Marshall Barton" wrote:

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.
--
Marsh
MVP [MS Access]

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

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.
--
Marsh
MVP [MS Access]

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]


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


"Marshall Barton" wrote:
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.