Line Numbering

  • Thread starter Thread starter Connie
  • Start date Start date
C

Connie

I have a report that is first grouped by Stores & then grouped by Dates. I
know you can create a textbox that will number each line by setting the
Running Sum, but you can only choose over group or over all. If I select by
group, it resets the number back to 1 for each date under that Store, but I
need it to continue for the Store .... then reset back to 1 for the next
Store. Any way of accomplishing this?

TIA
Connie
 
Yes, you can use VBA code in the format event to reset the running sum
(over all) back to zero.

(david)
 
can you explain a little more on this ...

I put Reports!ReportName!txtbox.runningsum=2 (overall) in the Store's
GroupHeading's OnFormat & I get an error stating I can't assign a value to
this property. What am I doing wrong?

thanks
Connie
 
Hi Connie,

Place the textbox control in the Dates Footer and set Running Sum Over
Group.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| From: "Connie" <[email protected]>
| Newsgroups: microsoft.public.access.reports
| Subject: Line Numbering
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Date: Wed, 07 Apr 2004 15:52:52 GMT
| NNTP-Posting-Host: 24.92.236.185
| X-Complaints-To: (e-mail address removed)
| X-Trace: twister.nyroc.rr.com 1081353172 24.92.236.185 (Wed, 07 Apr 2004
11:52:52 EDT)
| NNTP-Posting-Date: Wed, 07 Apr 2004 11:52:52 EDT
| Organization: Road Runner
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news-rtr.nyroc.rr.com!n
ews-out.nyroc.rr.com!twister.nyroc.rr.com.POSTED!53ab2750!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.reports:135394
| X-Tomcat-NG: microsoft.public.access.reports
|
| I have a report that is first grouped by Stores & then grouped by Dates. I
| know you can create a textbox that will number each line by setting the
| Running Sum, but you can only choose over group or over all. If I select
by
| group, it resets the number back to 1 for each date under that Store, but
I
| need it to continue for the Store .... then reset back to 1 for the next
| Store. Any way of accomplishing this?
|
| TIA
| Connie
|
|
|
 
Hi Eric,

the problem is I want each record numbered sequentially for each Store not
date. When I set the txtbox (txtNumbering) running sum to Over All it
continues the numbering for entire report. If I set it to Over Group, then
it restarts the number under each separate date. That's not what I'm after.

This is the way I'd like it to look

Store#1 (number all names under this store)
1/10/04
#1 Name
#2 Name
2/10/04
#3 Name
#4 Name

Store #2 (reset the number back to zero for the next
store)
1/20/04
#1 Name
#2 Name

Hopefully this explains better what I'm trying to achieve.

thanks for your help
Connie
 
If this is impossible to accomplish, could someone tell me .... otherwise
I'm still trying to figure out a work-around.

Thanks
 
It would help if you enter a few records into a reply with the actual data,
current grouping, and desired numbering.
 
I did in one of my previous replies, but here is another example.

Store One (GroupHeader0)
1/10/04 (GroupHeader1)
#1 Detail info
#2 Detail info
#3 Detail Info
1/11/04
#4 Detail Info
#5 Detail info
1/12/04
#6 Detail info
#7 Detail Info

Store two
1/9/04
#1 Detail info
#2 Detail info
1/10/04
#3 Detail Info
1/15/04
#4 Detail Info
#5 Detail info
#6 Detail Info

I want it to continue numbering until the Store changes, however I'm
grouping by Stores & then by Dates. Is this possible to do??

TIA
Connie
 
I put Reports!ReportName!txtbox.runningsum=2 (overall) in the Store's

create an event handler for the group header section
(select [Event Procedure] for the On Format Event)

go to the code (click on the ... at the right side of the On Format
property).

Put in code like this:
If FormatCount = 1 then me.txtbox = 0

(david)
 
thanks for your reply David, but when I put that code in the group header's
OnFormat (this is the Stores group header) I get a runtime error "You can't
assign a value to this object" & the code stops on me.txtNo. I have txtNo's
ControlSource set to =1 & running sum is set to Over All. I'm obviously
doing something wrong, but darned if I can figure it out.

Thanks for everyone's time & patience with this one.
Connie


david epsom dot com dot au said:
I put Reports!ReportName!txtbox.runningsum=2 (overall) in the Store's
Click to expand...

create an event handler for the group header section
(select [Event Procedure] for the On Format Event)

go to the code (click on the ... at the right side of the On Format
property).

Put in code like this:
If FormatCount = 1 then me.txtbox = 0

(david)

Connie said:
can you explain a little more on this ...

I put Reports!ReportName!txtbox.runningsum=2 (overall) in the Store's
GroupHeading's OnFormat & I get an error stating I can't assign a value to
this property. What am I doing wrong?

thanks
Connie

Dates. Store,
but
Click to expand...
Click to expand...
 
I finally have it working!!!! By using the Detail's OnPrint, GroupHeader's
& GroupFooter's OnPrint in combination with another piece of code I was
using for page totals, I have it working. Using the GroupHeader's &
Footer's OnPrint idea came from a sample code from ACG Soft -
http://ourworld.compuserve.com/homepages/attac-cg , "Resetting Pg Numbers
For Each Group", I just tweaked it to apply to numbering individual detail
items, instead of page numbering.

I'd like to thank, not only ACG Soft for that piece of code, but also David,
Eric & Duane.

Connie
 
Connie said:
I have a report that is first grouped by Stores & then grouped by Dates. I
know you can create a textbox that will number each line by setting the
Running Sum, but you can only choose over group or over all. If I select by
group, it resets the number back to 1 for each date under that Store, but I
need it to continue for the Store .... then reset back to 1 for the next
Store.
Click to expand...


Connie, I see that you've managed to get something working
so this may be redundant. However, I've been playing around
with your issue and think I might have come up with a way
that's relatively(?) straightforward. Anyway, here's some
possible food for thought.

Just for explanatory purposes, I named your detail section's
Over Group running sum text box (with the expression =1),
txtRunCnt.

Then, I created another Over Group running sum text box
named txtGrpCnt in the dates footer section. The expression
for this text box is =txtRunCnt. This will accumulate the
total number of detail lines in the stores group.

Next I added still another text box (named txtRunGrp) to the
report header section as a global place to save the
accumulated number of details in the preceeding dates
groups. (You could use a module level variable for this
purpose if you prefer.)

I then added a line of code to the stores group header
section's Format event:
Me.txtRunGrp = 0
to initialize the report header text box at the start of
each stores group.

To set the value of the txtRunGrp text box, I added a line
of code to the dates footer section's Format event:
Me.txtRunGrp = Me.txtGrpCnt

Finally (whew!), I could display the line numbers the way
you want by using a detail section text box with the
expression =[txtRunCnt] + [txtRunGrp]

The reason I went all over the place with this is to avoid
using any unreliable calculations in event procedures. I
don't know what code you actually ended up with, but keep in
mind that it is not possible to use event procedure VBA code
to reliably calculate a value that depends on values from
other than the current detail.
 
Hi Marsh,

Thanks so much! This was the basic concept I started out with, but couldn't
quite get it to work. I'll definitely try this out, as mine is working
right now, however, if one of the stores ends up with more than 1 page, I
think my existing code will cause some problems.

I'll let you know how this goes. Thanks again for your time & help on this!
Connie


Marshall Barton said:
Connie said:
I have a report that is first grouped by Stores & then grouped by Dates. I
know you can create a textbox that will number each line by setting the
Running Sum, but you can only choose over group or over all. If I select by
group, it resets the number back to 1 for each date under that Store, but I
need it to continue for the Store .... then reset back to 1 for the next
Store.
Click to expand...


Connie, I see that you've managed to get something working
so this may be redundant. However, I've been playing around
with your issue and think I might have come up with a way
that's relatively(?) straightforward. Anyway, here's some
possible food for thought.

Just for explanatory purposes, I named your detail section's
Over Group running sum text box (with the expression =1),
txtRunCnt.

Then, I created another Over Group running sum text box
named txtGrpCnt in the dates footer section. The expression
for this text box is =txtRunCnt. This will accumulate the
total number of detail lines in the stores group.

Next I added still another text box (named txtRunGrp) to the
report header section as a global place to save the
accumulated number of details in the preceeding dates
groups. (You could use a module level variable for this
purpose if you prefer.)

I then added a line of code to the stores group header
section's Format event:
Me.txtRunGrp = 0
to initialize the report header text box at the start of
each stores group.

To set the value of the txtRunGrp text box, I added a line
of code to the dates footer section's Format event:
Me.txtRunGrp = Me.txtGrpCnt

Finally (whew!), I could display the line numbers the way
you want by using a detail section text box with the
expression =[txtRunCnt] + [txtRunGrp]

The reason I went all over the place with this is to avoid
using any unreliable calculations in event procedures. I
don't know what code you actually ended up with, but keep in
mind that it is not possible to use event procedure VBA code
to reliably calculate a value that depends on values from
other than the current detail.
Click to expand...
 
That works great!!! Thank you so much - it's greatly appreciated!! This
has been a thorn in my side all week & as you had mentioned, because I was
using other event procedures, I think it would have caused problems in
certain situations, whereas this appears to work in all cases.

Thanks again Marsh
Connie


Marshall Barton said:
Connie said:
I have a report that is first grouped by Stores & then grouped by Dates. I
know you can create a textbox that will number each line by setting the
Running Sum, but you can only choose over group or over all. If I select by
group, it resets the number back to 1 for each date under that Store, but I
need it to continue for the Store .... then reset back to 1 for the next
Store.
Click to expand...


Connie, I see that you've managed to get something working
so this may be redundant. However, I've been playing around
with your issue and think I might have come up with a way
that's relatively(?) straightforward. Anyway, here's some
possible food for thought.

Just for explanatory purposes, I named your detail section's
Over Group running sum text box (with the expression =1),
txtRunCnt.

Then, I created another Over Group running sum text box
named txtGrpCnt in the dates footer section. The expression
for this text box is =txtRunCnt. This will accumulate the
total number of detail lines in the stores group.

Next I added still another text box (named txtRunGrp) to the
report header section as a global place to save the
accumulated number of details in the preceeding dates
groups. (You could use a module level variable for this
purpose if you prefer.)

I then added a line of code to the stores group header
section's Format event:
Me.txtRunGrp = 0
to initialize the report header text box at the start of
each stores group.

To set the value of the txtRunGrp text box, I added a line
of code to the dates footer section's Format event:
Me.txtRunGrp = Me.txtGrpCnt

Finally (whew!), I could display the line numbers the way
you want by using a detail section text box with the
expression =[txtRunCnt] + [txtRunGrp]

The reason I went all over the place with this is to avoid
using any unreliable calculations in event procedures. I
don't know what code you actually ended up with, but keep in
mind that it is not possible to use event procedure VBA code
to reliably calculate a value that depends on values from
other than the current detail.
Click to expand...
 
oops I spoke too soon. I just entered several detail items under a store so
it would go onto another page & it resets the global counter (txtRunGrp)
back to 0 because it reads the GroupHeader's OnFormat (Stores) when I go to
next page (I stepped thru code to see what was causing the problem). Any
ideas??


Marshall Barton said:
Connie said:
I have a report that is first grouped by Stores & then grouped by Dates. I
know you can create a textbox that will number each line by setting the
Running Sum, but you can only choose over group or over all. If I select by
group, it resets the number back to 1 for each date under that Store, but I
need it to continue for the Store .... then reset back to 1 for the next
Store.
Click to expand...


Connie, I see that you've managed to get something working
so this may be redundant. However, I've been playing around
with your issue and think I might have come up with a way
that's relatively(?) straightforward. Anyway, here's some
possible food for thought.

Just for explanatory purposes, I named your detail section's
Over Group running sum text box (with the expression =1),
txtRunCnt.

Then, I created another Over Group running sum text box
named txtGrpCnt in the dates footer section. The expression
for this text box is =txtRunCnt. This will accumulate the
total number of detail lines in the stores group.

Next I added still another text box (named txtRunGrp) to the
report header section as a global place to save the
accumulated number of details in the preceeding dates
groups. (You could use a module level variable for this
purpose if you prefer.)

I then added a line of code to the stores group header
section's Format event:
Me.txtRunGrp = 0
to initialize the report header text box at the start of
each stores group.

To set the value of the txtRunGrp text box, I added a line
of code to the dates footer section's Format event:
Me.txtRunGrp = Me.txtGrpCnt

Finally (whew!), I could display the line numbers the way
you want by using a detail section text box with the
expression =[txtRunCnt] + [txtRunGrp]

The reason I went all over the place with this is to avoid
using any unreliable calculations in event procedures. I
don't know what code you actually ended up with, but keep in
mind that it is not possible to use event procedure VBA code
to reliably calculate a value that depends on values from
other than the current detail.
Click to expand...
 
Marsh, I switched the code from Store's GroupHeader OnFormat to Store's
GroupFooter OnPrint. It seems to be working, but are you aware of any
problems that may cause??
Thanks for your help & advice!



Marshall Barton said:
Connie said:
I have a report that is first grouped by Stores & then grouped by Dates. I
know you can create a textbox that will number each line by setting the
Running Sum, but you can only choose over group or over all. If I select by
group, it resets the number back to 1 for each date under that Store, but I
need it to continue for the Store .... then reset back to 1 for the next
Store.
Click to expand...


Connie, I see that you've managed to get something working
so this may be redundant. However, I've been playing around
with your issue and think I might have come up with a way
that's relatively(?) straightforward. Anyway, here's some
possible food for thought.

Just for explanatory purposes, I named your detail section's
Over Group running sum text box (with the expression =1),
txtRunCnt.

Then, I created another Over Group running sum text box
named txtGrpCnt in the dates footer section. The expression
for this text box is =txtRunCnt. This will accumulate the
total number of detail lines in the stores group.

Next I added still another text box (named txtRunGrp) to the
report header section as a global place to save the
accumulated number of details in the preceeding dates
groups. (You could use a module level variable for this
purpose if you prefer.)

I then added a line of code to the stores group header
section's Format event:
Me.txtRunGrp = 0
to initialize the report header text box at the start of
each stores group.

To set the value of the txtRunGrp text box, I added a line
of code to the dates footer section's Format event:
Me.txtRunGrp = Me.txtGrpCnt

Finally (whew!), I could display the line numbers the way
you want by using a detail section text box with the
expression =[txtRunCnt] + [txtRunGrp]

The reason I went all over the place with this is to avoid
using any unreliable calculations in event procedures. I
don't know what code you actually ended up with, but keep in
mind that it is not possible to use event procedure VBA code
to reliably calculate a value that depends on values from
other than the current detail.
Click to expand...
 
Connie said:
Marsh, I switched the code from Store's GroupHeader OnFormat to Store's
GroupFooter OnPrint. It seems to be working, but are you aware of any
problems that may cause??
Click to expand...


I think the only way that value can be reset like that is if
you have the stores group header section's RepeatSection
property to Yes. Sorry, I guess that particular case
slipped past my thinking about all the various ways this
kind of calculation can go wrong.

Moving the stores initialization line of code to the stores
group footer should work. Unless I'm missing another
nuance, the only way that section's Format event can be
executed more than once would be caused by a KeepTogether
that moved the footer section to the next page, which won't
matter because no details will be finalized between the two
stores footer Format events. If I hadn't forgotten about
Repeat Section, I would have recommended this approach the
first time.
--
Marsh
MVP [MS Access]


Connie said:
I have a report that is first grouped by Stores & then grouped by Dates. I
know you can create a textbox that will number each line by setting the
Running Sum, but you can only choose over group or over all. If I select by
group, it resets the number back to 1 for each date under that Store, but I
need it to continue for the Store .... then reset back to 1 for the next
Store.
Click to expand...

"Marshall Barton" wrote
Connie, I see that you've managed to get something working
so this may be redundant. However, I've been playing around
with your issue and think I might have come up with a way
that's relatively(?) straightforward. Anyway, here's some
possible food for thought.

Just for explanatory purposes, I named your detail section's
Over Group running sum text box (with the expression =1),
txtRunCnt.

Then, I created another Over Group running sum text box
named txtGrpCnt in the dates footer section. The expression
for this text box is =txtRunCnt. This will accumulate the
total number of detail lines in the stores group.

Next I added still another text box (named txtRunGrp) to the
report header section as a global place to save the
accumulated number of details in the preceeding dates
groups. (You could use a module level variable for this
purpose if you prefer.)

I then added a line of code to the stores group header
section's Format event:
Me.txtRunGrp = 0
to initialize the report header text box at the start of
each stores group.

To set the value of the txtRunGrp text box, I added a line
of code to the dates footer section's Format event:
Me.txtRunGrp = Me.txtGrpCnt

Finally (whew!), I could display the line numbers the way
you want by using a detail section text box with the
expression =[txtRunCnt] + [txtRunGrp]

The reason I went all over the place with this is to avoid
using any unreliable calculations in event procedures. I
don't know what code you actually ended up with, but keep in
mind that it is not possible to use event procedure VBA code
to reliably calculate a value that depends on values from
other than the current detail.
Click to expand...
Click to expand...
 
Yes I do have the Repeat Section to yes for both the Store group & Date
group ... now I tell you, eh? ;-) .... my fault I should have mentioned
that earlier ... sorry. I can totally appreciate how difficult it is to try
& cover all scenarios ... because there are so many combinations of property
settings, a fairly easy piece of code to go astray .... but glad to hear
it's in the right place now.

Thanks again for all your help, advice & explanations ... I greatly
appreciate it all & learned a lot!! Now I can finally put this problem to
rest ... thanks to you! :)

Have a great day!
Connie


Marshall Barton said:
Connie said:
Marsh, I switched the code from Store's GroupHeader OnFormat to Store's
GroupFooter OnPrint. It seems to be working, but are you aware of any
problems that may cause??
Click to expand...


I think the only way that value can be reset like that is if
you have the stores group header section's RepeatSection
property to Yes. Sorry, I guess that particular case
slipped past my thinking about all the various ways this
kind of calculation can go wrong.

Moving the stores initialization line of code to the stores
group footer should work. Unless I'm missing another
nuance, the only way that section's Format event can be
executed more than once would be caused by a KeepTogether
that moved the footer section to the next page, which won't
matter because no details will be finalized between the two
stores footer Format events. If I hadn't forgotten about
Repeat Section, I would have recommended this approach the
first time.
--
Marsh
MVP [MS Access]


Connie wrote:
I have a report that is first grouped by Stores & then grouped by
Click to expand...
Dates.
I
know you can create a textbox that will number each line by setting the
Running Sum, but you can only choose over group or over all. If I
Click to expand...
select
by
group, it resets the number back to 1 for each date under that Store,
Click to expand...
but
I
need it to continue for the Store .... then reset back to 1 for the next
Store.

"Marshall Barton" wrote
Connie, I see that you've managed to get something working
so this may be redundant. However, I've been playing around
with your issue and think I might have come up with a way
that's relatively(?) straightforward. Anyway, here's some
possible food for thought.

Just for explanatory purposes, I named your detail section's
Over Group running sum text box (with the expression =1),
txtRunCnt.

Then, I created another Over Group running sum text box
named txtGrpCnt in the dates footer section. The expression
for this text box is =txtRunCnt. This will accumulate the
total number of detail lines in the stores group.

Next I added still another text box (named txtRunGrp) to the
report header section as a global place to save the
accumulated number of details in the preceeding dates
groups. (You could use a module level variable for this
purpose if you prefer.)

I then added a line of code to the stores group header
section's Format event:
Me.txtRunGrp = 0
to initialize the report header text box at the start of
each stores group.

To set the value of the txtRunGrp text box, I added a line
of code to the dates footer section's Format event:
Me.txtRunGrp = Me.txtGrpCnt

Finally (whew!), I could display the line numbers the way
you want by using a detail section text box with the
expression =[txtRunCnt] + [txtRunGrp]

The reason I went all over the place with this is to avoid
using any unreliable calculations in event procedures. I
don't know what code you actually ended up with, but keep in
mind that it is not possible to use event procedure VBA code
to reliably calculate a value that depends on values from
other than the current detail.
Click to expand...
Click to expand...
Click to expand...
 
That was totally my fault -- I can't think why I led you astray like that.

If you can, put a running total over the group in a group footer.
This should reset only when the group changes.

If that is not suitable, you can either build a query that calculates a
running sum over the group:
ACC: How to Create Running Totals in a Query (95/97)
http://support.microsoft.com/default.aspx?scid=KB;en-us;138911&

or use a VBA function as the control source

=StoreSum()

function fnStoreSum()
fnStoreSum = SumForStore
endif

and track the SumForStore by adding in the detail format:
SumForStore = SumForStore + me.Amount

and clearing in the group event:
SumForStore = 0


(david)

Connie said:
thanks for your reply David, but when I put that code in the group header's
OnFormat (this is the Stores group header) I get a runtime error "You can't
assign a value to this object" & the code stops on me.txtNo. I have txtNo's
ControlSource set to =1 & running sum is set to Over All. I'm obviously
doing something wrong, but darned if I can figure it out.

Thanks for everyone's time & patience with this one.
Connie


david epsom dot com dot au said:
I put Reports!ReportName!txtbox.runningsum=2 (overall) in the Store's
Click to expand...

create an event handler for the group header section
(select [Event Procedure] for the On Format Event)

go to the code (click on the ... at the right side of the On Format
property).

Put in code like this:
If FormatCount = 1 then me.txtbox = 0

(david)

Connie said:
can you explain a little more on this ...

I put Reports!ReportName!txtbox.runningsum=2 (overall) in the Store's
GroupHeading's OnFormat & I get an error stating I can't assign a
Click to expand...
Click to expand...
value
to setting
the the
next
Click to expand...
Click to expand...
 
Back
Top