Create unique username from Lastname, Firstname Fields

[startup]

Anyone have the VB Code to create a unique username from the Firstname/Lastname fields in my table? It needs to have the following criteria:

1) Basic username = 1st Initial + 1st 7 characters of last name

2)If username is a duplicate, a number starting with # 1 will be added to the end (bsmith1, bsmith2, etc.)

3)Username cannot be shorter than 5 characters. If it is shorter than a number starting with # 1 will added to the end to make the username 5 characters (ie jcox1 or jho11)

I did see a rudimentary beginning to the type of username I need on thread181-86590, but it didn't generate unique usernames.

Thanks for all your help.

 

[GeekGirlau]

This function assumes that the user names are stored in a field in the user table. The DLookup function can then check to see if that user name exists.


Public Function CreateUserName(strFirst As String, strSurname As String) As String
Dim strUser As String
Dim i As Integer
Dim varExists As Variant


strUser = Left(strFirst, 1) & Left(strSurname, 7)

Do Until Len(strUser) >= 5
i = i + 1
strUser = strUser & i
Loop

i = 0

CheckName:
varExists = DLookup("[UserNameField]", "tblUsers", "[UserNameField]='" & strUser & "'&quot

If Not IsNull(varExists) Then
i = i + 1
strUser = strUser & i
GoTo CheckName

Else
CreateUserName = strUser
End If
End Function

 

[startup]

GreekGirlau,

Thanks for the function, this is right on track. I just need a few more pointers to get it working perfectly.

First, this function creates a multiple userIDs in this format, "bsmith","bsmith1","bsmith12". Up to the the "bsmith12" it's just what I want but "bsmith12" should be "bsmith2". I know I need some sort of loop to do this, but I always get confused by loops.

Second, I need to check for the existance of the UserID in 2 different tables, the main table and an archive one and again this requires a little logic which is beyond me.

Thanks for your help.