Creating a userID Ideas?

[Rhonda728]

I've been tasked with creating a text field, all lower case, that combines to start, 4 letters, then 1st letter of First Name (field), Middle I (field) if exists, can be blank and finally using the Last Name (field) to create a new record in the UserID field that does not exceed 12 characters total. I'm looking for ideas on how to approach this. Any ideas?

 

[CJ_London]

Quick thoughts....

Is this to be a primary key to link to other data? If yes then this is not the best way to go

You need to consider the situation if the user last name is more than 6 characters in length. Potential clashes in names

Consider what happens when someone changes their name? i.e. gets married or there has been a typo that needs correcting. other data will no longer link

What are the first 4 characters? How are they relevant?

Rather than using a space, use an underscore for the middle initial if it doesn't exist.

You could create this as 4 separate fields which are all set to primary (in design view highlight all 4 fields and click on primary key) although this could give issues for linking to other data but will prevent duplicates

You would be better having a unique id set as an autonumber type as a primary key and the other data in separate fields - but it does depend on what you are trying to acheive

 

[Rhonda728]

Thanks for the quick response. Let me answer your questions. First of all, no this is not the Primary Key, I use a separate Auto Number for my Primary key. The task I've been given? The UserID field has required elements (Corporate decision, not mine). The field has to start with "uskm" Then the other info I described. Some people do not have a legal middle initial, so in those cases, no dash, no space is allowed, it's simply to be omited. Yes, I realize it will create issues when a user changes their name, thankfully, I don't have to address that. The UserID field in my table is set for no duplicates, so I'll have to make sure the record is marked in some way for that issue. I do understand I'll have to address the error if duplicates. Here's an example of what I mean (this is stumping me)

John Hasareallyreallylonglastname his Userid would need to be uskmjhasarea Another instance, Mary Q Shortname would be uskmmqshortn

 

[CJ_London]

Well, the key will be easy enough to create, just issues around duplicates etc. - and not that easy to read!

Just thinking of issues....

What happens when someone is called John O'Malley - is the apostrophe to be ignored?

Have you done an analysis of the existing names to see if you already have duplicates? Hope you don't have too many MacDonalds working for you!

One final thought, in some cultures the first and last names are reversed

Good luck

 

[Rhonda728]

As far as the O'Malley kind of names, that's already been addressed. The apostrophe is left out, it turns into Omalley as his last name. I've already checked for duplicates on existing users, so this would need to be in place for the creation of a new user. I have the necessary tables, forms, queries, relationships in place. I have the report that is generated when a button is clicked on the Create User Form, now I have to have my system generate the userid. Thanks very much for your thoughts!

 

[pr2-eugin]

Just a suggestion.. How about, writing a simple function? The following would take in three arguments, provide the UserID.. I have written a small check to change the UserID if already exists.. You can change it as you like.. This is just a suggestion.. Change the Blue Bits..

Public Function getSpecialID(firstStr As String, middleStr As Variant, lastStr As String) As String
[COLOR=Green]'-------------------------------------------------------------------------
'   A Function that will take in First Name, Middle Name and Last Name
'   returns a Specially designed UserID.
'
'   Input   : First Name, Middle Name and Last Name
'   Output  : A UserID
'   Example :
'   ? getSpecialID("Mary", "Q", "Shortname")
'     USKMMQSHORTN
'   ? getSpecialID("John", "", "Hasareallyreallylonglastname")
'     USKMJHASAREA
[/COLOR][COLOR=Green]'   ? getSpecialID("John", "", "Has[U][B]another[/B][/U]longlastname")
'     USKM1JHASARE[/COLOR]
[COLOR=Green]'--------------------------------------------------------------------------[/COLOR]
    Dim retStr As String, ctr As Long
    ctr = 0
    retStr = "USKM" & Left(firstStr, 1)
    If Len(middleStr & vbNullString) <> 0 Then retStr = retStr & Left(middleStr, 1)
    retStr = retStr & Left(lastStr, 12 - Len(retStr))
    retStr = UCase(retStr)
    While DCount("*", "[COLOR=Blue][B]theTableName[/B][/COLOR]", "[B][COLOR=Blue]fieldName[/COLOR][/B] = '" & retStr & "'") > 0
        [COLOR=Green]'How you wish to change the name?
        'Maybe:[/COLOR]
        ctr = ctr + 1
    Wend
    If ctr <> 0 Then retStr = "USKM" & ctr & Mid(retStr, 5)
    getSpecialID = Left(retStr, 12)
End Function

 

[Rhonda728]

Big thanks for the suggestion, I'll give your code a try!

 

[Rhonda728]

Paul,
You do great work, I tweaked your code to fit my situation and it worked like a charm! On to the error handling part. I can't just let it pop up a message to the person making the entry, that there is a duplicate UserID, so I have to have my system see the duplication and change the output to something unique. I'll deal with how to handle it after that point. I know how to code the standard error handling. Just not exactly sure how to tell it, for a duplicate, add 99 (or something). Mind helping me again?

Rhonda Clark

 

[pr2-eugin]

Well you can make this change to the code..

Public Function getSpecialID(firstStr As String, middleStr As Variant, lastStr As String) As String
[COLOR=Green]'-------------------------------------------------------------------------
'   A Function that will take in First Name, Middle Name and Last Name
'   returns a Specially designed UserID.
'
'   Input   : First Name, Middle Name and Last Name
'   Output  : A UserID
'   Example :
'   ? getSpecialID("Mary", "Q", "Shortname")
'     USKMMQSHORTN
'   ? getSpecialID("John", "", "Hasareallyreallylonglastname")
'     USKMJHASAREA
[/COLOR][COLOR=Green]'   ? getSpecialID("John", "", "Has[U][B]another[/B][/U]longlastname")
'     USKM1JHASARE[/COLOR]
[COLOR=Green]'--------------------------------------------------------------------------[/COLOR]
    Dim retStr As String, ctr As Long
    ctr = 0
    retStr = "USKM" & Left(firstStr, 1)
    If Len(middleStr & vbNullString) <> 0 Then retStr = retStr & Left(middleStr, 1)
    retStr = retStr & Left(lastStr, 12 - Len(retStr))
    retStr = UCase(retStr)
    While DCount("*", "[COLOR=Blue][B]theTableName[/B][/COLOR]", "[B][COLOR=Blue]fieldName[/COLOR][/B] = '" & retStr & "'") > 0
        [COLOR=Green]'How you wish to change the name?
        'Maybe:[/COLOR]
        ctr = ctr + 1
        retStr = "USKM" & ctr & Mid(retStr, 5)
    Wend
    getSpecialID = Left(retStr, 12)
End Function

This will be in my rough theoretical reasoning be a bit fool proof.. i.e. if there is more than one (lets say 5) J*** H****** in the table this would loop until a unique ID is generated.. Maybe the fifth J*** H****** would have an ID as.. USKM54321JHA..

 

[Rhonda728]

Paul, Great Idea! Unfortunately for use here, I can't add a number in the middle of the UserID. Setting a duplicate to a different UserID would simply be a marker, that would generate a Help Desk Ticket, that's when a person needs to get involved. Instead of the number, can I simply add some sort of symbol to the beginning or the end? I don't know what symbols I can get away with using that won't choke VBA.

Rhonda Clark

 

[Rhonda728]

Paul,
Thanks so much for the help you've provided so far. I used your code with a few tweaks. Now when I run the update query, using the following command,

UPDATE tblusers SET tblusers.UserID = getSpecialID([tblusers]![First Name],[tblusers]![Middle Initial],[tblusers]![Last Name])
WHERE (((tblusers.UserID) Is Null));

I get this Window, it won't let me continue until I click ok. I've checked, if I'm doing mulitple records, it pops up for each record. It says Microsoft Access at the top, the only thing in the message is ":0"

Can you assist?
Rhonda :banghead:

Public Function getSpecialID(firstStr As String, middleStr As Variant, lastStr As String) As String
'-------------------------------------------------------------------------
'   A Function that will take in First Name, Middle Name and Last Name
'   returns a Specially designed UserID.
'
'   Input   : First Name, Middle Name and Last Name
'   Output  : A UserID
'   Example :
'   ? getSpecialID("Mary", "Q", "Shortname")
'     USKMMQSHORTN
'   ? getSpecialID("John", "", "Hasareallyreallylonglastname")
'     USKMJHASAREA
'   ? getSpecialID("John", "", "Hasanotherlonglastname")
'     USKM1JHASARE
'--------------------------------------------------------------------------
    Dim retStr As String
    On Error GoTo Error_handler
    
    retStr = "USKM" & Left(firstStr, 1)
    If Len(middleStr & vbNullString) <> 0 Then retStr = retStr & Left(middleStr, 1)
    retStr = retStr & Left(lastStr, 12 - Len(retStr))
    retStr = UCase(retStr)
    While DCount("*", "tblusers", "UserID = '" & retStr & "'") > 0
    retStr = "DUPUSKM" & Mid(retStr, 5)
    Wend
    getSpecialID = Left(retStr, 12)
Error_handler:
    MsgBox Err.Number & ": " & Err.Description
    
End Function

 

[CJ_London]

You just heed to change ! for .

UPDATE tblusers SET tblusers.UserID = getSpecialID([tblusers].[First Name],[tblusers].[Middle Initial],[tblusers].[Last Name])
WHERE (((tblusers.UserID) Is Null));

 

[Rhonda728]

Thanks. I tried that, but same result. By the way, the ! is what Access put in, when I built the update field from the Expression Builder. I used the reguar Query design window when I created the Update Query. I just opened the SQL view so I could copy and paste it into my message here. Since I get the same result, with either ! or . Any other ideas?

Rhonda

 

[jdraw]

I think you need to add an Exit Function line as shown below for the message is ":0"

..........
getSpecialID = Left(retStr, 12)
    [B]Exit Function[/B]
Error_handler:
..................

You are dropping into the error handler by mistake.

 

[Rhonda728]

That fixed it! Thanks very much!

 

[jdraw]

If you are adding userId's to tblUsers, I think you need an append query, not update.

Please clarify if I missed something.

 

[Rhonda728]

Thanks for asking. No, I don't want to do an Append Query. The User record is created, my system just assigns the UserID. The person entering the other information on the form, never sees the field for UserID. It is not the primary field on the table, but is an indexed field. There are requirements on the UserID, that the end user wouldn't know. That's why we create it behind the scenes. The UserID creation has to happen after the record has been created, that's the reason for the update query.
Rhonda

 

[Rhonda728]

I think I broke it? Ok, when a duplicate userid would be created, I had thought the code would assign a userid beginning with "dupuskm", but all I get is an error back from the update query saying I have a key violation. I understand why I would see the key violation. The field is set for no duplicates. I had thought the code would keep that from happening. Below is the code, can someone look at it and tell me where I went wrong?

Option Compare Database
Public Function getSpecialID(firstStr As String, middleStr As Variant, lastStr As String) As String
'-------------------------------------------------------------------------
'   A Function that will take in First Name, Middle Name and Last Name
'   returns a Specially designed UserID.
'
'   Input   : First Name, Middle Name and Last Name
'   Output  : A UserID
'   Example :
'   ? getSpecialID("Mary", "Q", "Shortname")
'     USKMMQSHORTN
'   ? getSpecialID("John", "", "Hasareallyreallylonglastname")
'     USKMJHASAREA
'   ? getSpecialID("John", "", "Hasanotherlonglastname")
'     USKM1JHASARE
'--------------------------------------------------------------------------
    Dim retStr As String
    On Error GoTo Error_handler
    
    retStr = "uskm" & Left(firstStr, 1)
    If Len(middleStr & vbNullString) <> 0 Then retStr = retStr & Left(middleStr, 1)
    retStr = retStr & Left(lastStr, 12 - Len(retStr))
    retStr = LCase(retStr)
    While DCount("*", "tblusers", "UserID = '" & retStr & "'") > 0
    retStr = "DUPUSKM" & Mid(retStr, 5)
    Wend
    getSpecialID = Left(retStr, 12)
        Exit Function
Error_handler:
    MsgBox Err.Number & ": " & Err.Description
    
End Function

 

[pr2-eugin]

Let's take some sample data.. the names are..
John Hasslehan
Jon Hasslehof
Joan Hasslehun
Jim Hasslehin
Jack Hasslehen

So the unique ID's generated are..
USKMJHASSLEH
DUPUSKMJHASS
DUPUSKMJHASS
DUPUSKMSKMSK
DUPUSKMSKMSK

If you notice, when there are The code will generate the ID only to a limit.. Unless there is a unique 'number' value the code will always hit brick wall.. Specially with the Loop, it will be thrown into a infinite loop.. So the best is either code a unique number or try auto number..

 

[Rhonda728]

Yikes! Somehow I've created an endless loop. I'm a novice code writer and I need help here. What part of my code is causing the loop to never end?

Option Compare Database
Public Function getSpecialID(firstStr As String, middleStr As Variant, lastStr As String) As String
'-------------------------------------------------------------------------
'   A Function that will take in First Name, Middle Name and Last Name
'   returns a Specially designed UserID.
'
'   Input   : First Name, Middle Name and Last Name
'   Output  : A UserID
'   Example :
'   ? getSpecialID("Mary", "Q", "Shortname")
'     USKMMQSHORTN
'   ? getSpecialID("John", "", "Hasareallyreallylonglastname")
'     USKMJHASAREA
'   ? getSpecialID("John", "", "Hasanotherlonglastname")
'     USKM1JHASARE
'--------------------------------------------------------------------------
    Dim retStr As String
    On Error GoTo Error_handler
    
    retStr = "uskm" & Left(firstStr, 1)
    If Len(middleStr & vbNullString) <> 0 Then retStr = retStr & Left(middleStr, 1)
    retStr = retStr & Left(lastStr, 12 - Len(retStr))
    retStr = LCase(retStr)
    While DCount("*", "tblusers", "UserID = '" & retStr & "'") > 0
    ctr = ctr + 1
    Wend
    If ctr <> 0 Then retStr = "dup" & ctr & Mid(retStr, 5)
    getSpecialID = Left(retStr, 12)
        Exit Function
Error_handler:
    MsgBox Err.Number & ": " & Err.Description
    
End Function