Unique id code

[Guest]

I want to create a unique user identifier by using the first three letters of
the last name field (Lname) and three numbers starting at 100. And example
would be BLA100, BLA101. Of course I need to check to see if BLA exists, and
if not add characters and then the 100 and if it does exist check for the
maximum number and add one. I know what I am to do but need some help with
the programming.
Thank you.

 

[Guest]

Hi Bobby

you can use this code.

Use the Right and DMax function to return the numeric part of the highest
value of the field "FOO" and add one to it, concatenating the result in the
end to "REC-". You can either use the following example as the DefaultValue
of the field or assign it manually on the form using AfterUpdate event of
another control. For example, if the field name in the above example is "FOO"
and the tableName FOOTable, then the expression would be

="REC-" & right(DMax("FOO", "FOOTable"), _
Len(DMax("FOO", "FOOTable")) - _
InStr(1, DMax("FOO", "FOOTable"), "-")) + 1

also this help is in this page.

http://www.mvps.org/access/strings/str0007.htm

Thanks