Creating Employee ID Codes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Where can I learn how to create unique alphanumeric employee ID codes for an
Access database? I want it to be two initials and a four digit counter ie
LL0000 where the counter advances with like initials and it starts at 100.
 
Where can I learn how to create unique alphanumeric employee ID codes
for an Access database? I want it to be two initials and a four digit
counter ie LL0000 where the counter advances with like initials and it
starts at 100.
Click to expand...

First some assumptions about your design:-

Is the LL part constant, or will it change? If it's constant, don't store
it at all; just use a Format property to put it on the form/ report.

If the LL part has some information content, then it needs to be in its
own field. First NF says one piece of information in one field.

Access on its own cannot enforce global insert triggers, so you'll need
to make sure that all record insertion happens through a form that you
can program. At that stage it's quite easy to allocate a new serial
number:

Dim varHighestSoFar as Variant ' might be null
Dim dwNextNum as Long '

Dim strWhere as String

' create a criterion string
strWhere = "Initials = """ & txtIntials & """"

varHighestSoFar = DMax("SerialNum", "MyTable", strWhere)
' check if there are any so far

If IsNull(varHighestSoFar) Then
' this is the first one with these initials
dwNextNum = 1 ' or zero according to taste

Else
' there is one there, so we want the next one
dwNextNum = varHighestSoFar + 1

End If


All this can be condensed into a single statement, but it helps to be
able to see the logic. Also, please note that this is not safe in a
multi-user setup.

Hope that helps


Tim F
 
Where can I learn how to create unique alphanumeric employee ID codes for an
Access database? I want it to be two initials and a four digit counter ie
LL0000 where the counter advances with like initials and it starts at 100.
Click to expand...

Tim's got some excellent code - but allow me to raise an objection to
this "intelligent key".

Suppose you have an employee Joelynn Smith, JS0132.

She marries a Mr. Wilson and she also decides that she prefers to be
known by her middle name Susan. She's now Susan Wilson.

Do you want to change her code to SW0132? Oops, that's Steve Wallace.
SW0498, the next available SW? Do you want to make this change in
every other table - AND on every sheet of paper which has SW0132
printed on it, or scribbled on it? how about every mind in which the
code SW0132 is memorized? Or, do you want to leave it SW0132, making
the initials meaningless with respect to the employee's name?

Storing name data in a key is almost NEVER a good idea!

John W. Vinson[MVP]
 
Back
Top