Counting a value and assigning a numerical value

[wilsonncmale28]

I am working on an app, that has a field named "Name". I have everything set up with a module, which, for example, the name "John", it assigns 1. when it sees "John" again it assigns 2. Then "Jane" It assigns 1 again. I want the module to see every instance of "John" to assign the same number, 1, then all instances of "Jane" 2, etc. Here is the code in the module I am using, but it is assigning the values wrong. Any ideas would be helpful. My final plan is to use the numbers for conditional formatting, so all johns one color, all janes, a different color. I can't use the conditional formatting wizard because these names pop up at random, and the names populate at random.
Option Compare Database

Global GBL_Category As String
Global GBL_Icount As Long


Public Function Increment(ivalue As String) As Long
If Nz(GBL_Category, "zzzzzzzz") = ivalue Then
GBL_Icount = GBL_Icount + 1

' MsgBox icount
Else
GBL_Category = ivalue
GBL_Icount = 1
End If
Increment = GBL_Icount
End Function


Thanks

 

[Cronk]

You mean you have an app which has a table with a field called Name?

Firstly, Name is used as a property by Access and it's bad practice to use it as a field name because it can give you trouble down the track. Use Person, or Employee or such like.

Secondly, why add a unique number to each occurrence? Group on your user names eg "John", "Jane". And what are you going to do when you get another person with the name John?

Thirdly, depending on your version of Access, you may only have a maximum of 3 different rules to apply, letting you provide for up to 4 names. And even if the number of rules was unlimited, it's going to be harder to distinguish between so many colours.

Why not just group the names?