Subreport Item Numbering

  • Thread starter Thread starter Travis Cornwell
  • Start date Start date
T

Travis Cornwell

I have several subreports embedded in a main report and I would like the
item number on one sub-report to affect the item numbering in another
sub-report. I presently have the following setup:

OnPrint for the subreport detail:
Me![txtItemNumber] = GetItemNumber (in a common function)

currItemNumber = currItemNumber + 1
GetItemNumber = currItemNumber
'currItemNumber resets as necessary

However, this will NOT work if the page is user manually prints a page
numbering instead of scrolling through. If you start on page 1, and then go
to page 5, ItemNumbering will start with the n+1 (n being the last number on
page 1), instead of starting at m+1 (where m represents the number of items
included on pages 2,3 and 4).

RunningTotal fixes this problem (even with manual scrolling) but I do not
know how to dynamically relate runningtotals on subreports such that:
RunningTotal on report A = (RunningTotal on SubReport A) + (RunningTotal on
any number of other possible SubReports)

Any suggestions/help much appreciated, thank you!
 
Travis said:
I have several subreports embedded in a main report and I would like the
item number on one sub-report to affect the item numbering in another
sub-report. I presently have the following setup:

OnPrint for the subreport detail:
Me![txtItemNumber] = GetItemNumber (in a common function)

currItemNumber = currItemNumber + 1
GetItemNumber = currItemNumber
'currItemNumber resets as necessary

However, this will NOT work if the page is user manually prints a page
numbering instead of scrolling through. If you start on page 1, and then go
to page 5, ItemNumbering will start with the n+1 (n being the last number on
page 1), instead of starting at m+1 (where m represents the number of items
included on pages 2,3 and 4).

RunningTotal fixes this problem (even with manual scrolling) but I do not
know how to dynamically relate runningtotals on subreports such that:
RunningTotal on report A = (RunningTotal on SubReport A) + (RunningTotal on
any number of other possible SubReports)
Click to expand...

As you've seen, you can not use code to do the counting.
The RunningSum property in a text box in each subreport will
number each line in the subreports.

To get each subreport to start where the other one left off,
you can use the subreport footer's Print event to save the
subreport's final number in a subreport related text box
back in the main report. (E.g. If the subreport is named
srp1, then name this text box txtsrp1.) The main report
then needs its own RunningSum text box to keep track of the
total for all the subreports in the section. This grand
running total text box (let's name it txtTotalCnt) would use
an expression =txtsrp1 + txtsrp2

Going back to the first subreport, you need another text box
to display the accumulated line numbering, use an expression
like:
=txtRunCount + Parent.txtTotalCnt - Parent.txtsrp1 -
Parent.txtsrp2
The second subreport would use the expression:
=txtRunCount + Parent.txtTotalCnt - Parent.txtsrp2

I can't explain why you have to subtract off the current
value from the previous subreports in the section, it's sort
of seems like the main report gets the value before the
subreport has calculated it.

I'm not at all sure I can understand the above explanation,
but it seemed to work in my test db. I hope you can figure
out something useful from this convoluted description.
 
Thanks, I was thinking something along this line (and have been toying with
it) since I posted :). Is it possible to set this dynamically? It seems to
presume a certain order in which the subreports will appear on the main
report. Using Me.Section(0).Controls.Item(n) seems to reference them in the
order in which they were created, not the order in which they appear in the
main report (makes sense from a programming standpoint, heh).
Umm...SOOOO...is there anyway to track the order in which they visually
appear on the main report before runtime using (X,Y) coordinates or
something similar?

(I'm not going to worry terribly about it, but if possible, I'd like
whomever takes over administration of this database to have as little
maintenance as possible should new items need to be added.)

Again, thanks, been attempting to solve this problem for awhile.


Marshall Barton said:
Travis said:
I have several subreports embedded in a main report and I would like the
item number on one sub-report to affect the item numbering in another
sub-report. I presently have the following setup:

OnPrint for the subreport detail:
Me![txtItemNumber] = GetItemNumber (in a common function)

currItemNumber = currItemNumber + 1
GetItemNumber = currItemNumber
'currItemNumber resets as necessary

However, this will NOT work if the page is user manually prints a page
numbering instead of scrolling through. If you start on page 1, and then go
to page 5, ItemNumbering will start with the n+1 (n being the last number on
page 1), instead of starting at m+1 (where m represents the number of items
included on pages 2,3 and 4).

RunningTotal fixes this problem (even with manual scrolling) but I do not
know how to dynamically relate runningtotals on subreports such that:
RunningTotal on report A = (RunningTotal on SubReport A) + (RunningTotal on
any number of other possible SubReports)
Click to expand...

As you've seen, you can not use code to do the counting.
The RunningSum property in a text box in each subreport will
number each line in the subreports.

To get each subreport to start where the other one left off,
you can use the subreport footer's Print event to save the
subreport's final number in a subreport related text box
back in the main report. (E.g. If the subreport is named
srp1, then name this text box txtsrp1.) The main report
then needs its own RunningSum text box to keep track of the
total for all the subreports in the section. This grand
running total text box (let's name it txtTotalCnt) would use
an expression =txtsrp1 + txtsrp2

Going back to the first subreport, you need another text box
to display the accumulated line numbering, use an expression
like:
=txtRunCount + Parent.txtTotalCnt - Parent.txtsrp1 -
Parent.txtsrp2
The second subreport would use the expression:
=txtRunCount + Parent.txtTotalCnt - Parent.txtsrp2

I can't explain why you have to subtract off the current
value from the previous subreports in the section, it's sort
of seems like the main report gets the value before the
subreport has calculated it.

I'm not at all sure I can understand the above explanation,
but it seemed to work in my test db. I hope you can figure
out something useful from this convoluted description.
Click to expand...
 
Perlith said:
Thanks, I was thinking something along this line (and have been toying with
it) since I posted :). Is it possible to set this dynamically? It seems to
presume a certain order in which the subreports will appear on the main
report. Using Me.Section(0).Controls.Item(n) seems to reference them in the
order in which they were created, not the order in which they appear in the
main report (makes sense from a programming standpoint, heh).
Umm...SOOOO...is there anyway to track the order in which they visually
appear on the main report before runtime using (X,Y) coordinates or
something similar?

(I'm not going to worry terribly about it, but if possible, I'd like
whomever takes over administration of this database to have as little
maintenance as possible should new items need to be added.)
Click to expand...

You're right, this whole thing is ugly. The stuff I posted
before seems to be dependent on the order of processing,
which in turn depends on the order the subreport controls
are added to the main report. In other words, you might be
able to get it to work, but moving them around will make it
look like a mess and the only way to "fix" it would be to
not only change the expressions in the subreports, but also
delete each subreport control and readd them in the new
positional order.

At this point, my bottom line is that it's not worth it.
Either that or it'll take something more than what I can
come up with.



Travis said:
I have several subreports embedded in a main report and I would like the
item number on one sub-report to affect the item numbering in another
sub-report. I presently have the following setup:

OnPrint for the subreport detail:
Me![txtItemNumber] = GetItemNumber (in a common function)

currItemNumber = currItemNumber + 1
GetItemNumber = currItemNumber
'currItemNumber resets as necessary

However, this will NOT work if the page is user manually prints a page
numbering instead of scrolling through. If you start on page 1, and then go
to page 5, ItemNumbering will start with the n+1 (n being the last number on
page 1), instead of starting at m+1 (where m represents the number of items
included on pages 2,3 and 4).

RunningTotal fixes this problem (even with manual scrolling) but I do not
know how to dynamically relate runningtotals on subreports such that:
RunningTotal on report A = (RunningTotal on SubReport A) + (RunningTotal on
any number of other possible SubReports)
Click to expand...
"Marshall Barton" wrote
As you've seen, you can not use code to do the counting.
The RunningSum property in a text box in each subreport will
number each line in the subreports.

To get each subreport to start where the other one left off,
you can use the subreport footer's Print event to save the
subreport's final number in a subreport related text box
back in the main report. (E.g. If the subreport is named
srp1, then name this text box txtsrp1.) The main report
then needs its own RunningSum text box to keep track of the
total for all the subreports in the section. This grand
running total text box (let's name it txtTotalCnt) would use
an expression =txtsrp1 + txtsrp2

Going back to the first subreport, you need another text box
to display the accumulated line numbering, use an expression
like:
=txtRunCount + Parent.txtTotalCnt - Parent.txtsrp1 -
Parent.txtsrp2
The second subreport would use the expression:
=txtRunCount + Parent.txtTotalCnt - Parent.txtsrp2

I can't explain why you have to subtract off the current
value from the previous subreports in the section, it's sort
of seems like the main report gets the value before the
subreport has calculated it.

I'm not at all sure I can understand the above explanation,
but it seemed to work in my test db. I hope you can figure
out something useful from this convoluted description.
Click to expand...
Click to expand...
 
Ok, new thought process here:

English:
We know presently that Detail.Controls.Item(n) is populated depending on
what order they are created, not what order they appear visually on the
report. However, using the Detail.Controls.Item(n).Top property, we can
determine that. I would like to sort Detail.Controls.Item(n) such that the
smallest Top property is at n=0, the second smallest at n=1, etc. etc.
(All of this references the main report's detail section).

Technical:
An event procedure for OnPrint for the detail section in the main report is
known to occur before ANY of the actual objects embedded in the detail
section are printed. Ok, if Access prints by referencing
Detail.Controls.Item(n) (which I'm pretty sure is the case), is it possible
to use a sorting algorithm for Detail.Controls.Item(n)? I'm not very
familiar with how memory/object cloning works on VB, so I don't know if
something as simple as a selection sort comparing the top property would
work during OnPrint. If it does, what data type do I declare the swapper
variable so the following will work?

swapper = Item(i)
Item(i) = Item(j)
Item(i) = swapper

*At this point, its more of a personal challenge to try to figure this out.
I'm having fun in the process and learning a whole bunch of stuff ;)*
 
Perlith said:
Ok, new thought process here:

English:
We know presently that Detail.Controls.Item(n) is populated depending on
what order they are created, not what order they appear visually on the
report. However, using the Detail.Controls.Item(n).Top property, we can
determine that. I would like to sort Detail.Controls.Item(n) such that the
smallest Top property is at n=0, the second smallest at n=1, etc. etc.
(All of this references the main report's detail section).

Technical:
An event procedure for OnPrint for the detail section in the main report is
known to occur before ANY of the actual objects embedded in the detail
section are printed. Ok, if Access prints by referencing
Detail.Controls.Item(n) (which I'm pretty sure is the case), is it possible
to use a sorting algorithm for Detail.Controls.Item(n)? I'm not very
familiar with how memory/object cloning works on VB, so I don't know if
something as simple as a selection sort comparing the top property would
work during OnPrint. If it does, what data type do I declare the swapper
variable so the following will work?

swapper = Item(i)
Item(i) = Item(j)
Item(i) = swapper

*At this point, its more of a personal challenge to try to figure this out.
I'm having fun in the process and learning a whole bunch of stuff ;)*
Click to expand...

Well, since controls are members of the Controls collection,
you would use a variable object of type Control ;-)

BUT, the problem with this is that you can not change the
order of the items in the Controls collection (it's not an
array). Actually, the Controls collection is read only and
can only be affected indirectly at design time.
 
Ok, I just wanted to make sure that it wasn't a collection inside a
collection or some other mess. Using TypeName kinda clarified that as I
kept playing with it. Read-only, blahness, I think I'll be back to my
original plan and just document everything thoroughly enough that if
anything changes, the next administrator knows how to make changes. That
and I'll presume for 99% of usability purposes, the users will not try to
manually go to pages.

Thank you for all of your help. If I think of anything "clever" and get it
to work *doubtful, heh* over the next few weeks, I'll post it here.
 
Back
Top