Letter each record on a subreport

[Melinda Chase]

Hello,
I have a subreport that lists several projects an employee has worked on. I
need this to be displayed as a lettered list, instead of numbered. Each
project has a letter in front of it (instead of a number) that is assigned
by the following in the Detail Section's OnFormat Event.

Me.JobLetter = Chr(Asc(Nz(Me.JobLetter, "@")) + 1) & "."

This seemed like a good solution until I went to the next employee's list
and realized that the lettering continued from the previous list. This is
not what I'd like to see. I'd like the lettering to restart on each
employee's record. So each employee has a list of projects starting with
"a".

Any suggestions on how to do this?
Thanks,
Melinda

 

[Duane Hookom]

Don't use any code.

Make a running sum text box in the subreport:
Name: txtRunSum
Control Source: =1
Running Sum: Over Group
Visible: No
Then create a visible text box:
Control Source: =Chr([txtRunSum]+64)

 

[Marshall Barton]

Hello,
I have a subreport that lists several projects an employee has worked on. I
need this to be displayed as a lettered list, instead of numbered. Each
project has a letter in front of it (instead of a number) that is assigned
by the following in the Detail Section's OnFormat Event.

Me.JobLetter = Chr(Asc(Nz(Me.JobLetter, "@")) + 1) & "."

This seemed like a good solution until I went to the next employee's list
and realized that the lettering continued from the previous list. This is
not what I'd like to see. I'd like the lettering to restart on each
employee's record. So each employee has a list of projects starting with
"a".

For various reasons, that is not guaranteed to work in all
situations.

A better way is to add a hidden text box (named txtLineNum)
to the detail section. Set its Control source expression to
=1 and its RunningSum property to Over Group.

The set the JobLetter text box's expression to:

=Chr(Asc("@") + txtLineNum) & "."

 

[Melinda Chase]

Thanks Duane,
Worked a treat!
Melinda

Don't use any code.

Make a running sum text box in the subreport:
Name: txtRunSum
Control Source: =1
Running Sum: Over Group
Visible: No
Then create a visible text box:
Control Source: =Chr([txtRunSum]+64)

--
Duane Hookom
Microsoft Access MVP

Hello,
I have a subreport that lists several projects an employee has worked on.
I
need this to be displayed as a lettered list, instead of numbered. Each
project has a letter in front of it (instead of a number) that is
assigned
by the following in the Detail Section's OnFormat Event.

Me.JobLetter = Chr(Asc(Nz(Me.JobLetter, "@")) + 1) & "."

This seemed like a good solution until I went to the next employee's list
and realized that the lettering continued from the previous list. This
is
not what I'd like to see. I'd like the lettering to restart on each
employee's record. So each employee has a list of projects starting with
"a".

Any suggestions on how to do this?
Thanks,
Melinda

 

[Melinda Chase]

Thanks Marsh,
I went with Duane's solution, but have kept a note of yours for future
reference as well.
Melinda

 

[Marshall Barton]

The two "solutions" are exactly the same. You don't even
need to remember that Asc("@") is 64 because you can use the
Immediate window to get one value from the other:

?Chr(64)
@
?Asc("@")
64

 

[Melinda Chase]

Thanks for explaining that Marsh. I've added this bit of knowledge to my
notes!

The two "solutions" are exactly the same. You don't even
need to remember that Asc("@") is 64 because you can use the
Immediate window to get one value from the other:

?Chr(64)
@
?Asc("@")
64
--
Marsh
MVP [MS Access]

I went with Duane's solution, but have kept a note of yours for future
reference as well.

"Marshall Barton" wrote