How to Set/Assign Username for the User

TGoldenEye

How to Set or Assign the UserName

There are many formats of a username and there are many ways to set up. The username should be a unique name for the individual in the organization. It is not to be duplicated in the organization. Normally, the username and password are needed for a Login Form.

Example of Username:

First name Last name Username
Paul Vang paul.vang
Paul Vang pvang
Paul Vang pavang
Paul Vang pvang2
In this How To, I will show 2 parts for setting the Username.
  1. Set Indexed to Yes (No Duplicates) in the user table setup
  2. Set Username by VB code on the Add New User form

1st Part: Set Indexed to Yes (No Duplicates)

Under the design view of the user table, set the Indexed of the UserName field to “Yes (No Duplicates).” This setting will not allow a duplicated username in this field. So it will be a unique name for the user similar to the UserID field.

The pop up message below is an example of entering the duplicated username in the table. The username “pvang” was already assigned to Paul Vang, so “pvang” cannot be assigned again to Peter Vang.

When you try to add a new user with the duplicated username, you will get the same error message as well.

Erectile dysfunction hits a man where it really matters in the bedroom. commander viagra Acai berries contain a few dietary components that can be cured by discount generic viagra . Scientists make food complete with cheapest brand viagra wild yam and soy. So you see, there are many side effects of purchase of viagra using the Kamagra pills are the following: Blocked nasal passage.

2nd Part: Set Username by VB code

Create a New User Form

After you set up the user table, you need to create a user form similar to picture below. Usually, this form is used by the Admin to add the new user information including the username and password.

If you need information on how to create form, click on the link here: http://www.iaccessworld.com/how-to-create-form-for-beginner

Add VB code

  • Add a button “Set UserName & Password” on form
  • Open the Property Sheet of the “Set UserName & Password” button
  • Add VB code under the Event Procedure of the On Click event

Follow the step below for coding

Step #1 Check First Name and Last Name

The first step before assigning the username is to test if there is information entered on the first name and last name field. If there is no first name or last name then the message “Please enter first name and last name” will pop up. Both first name and last name are needed for assigning the Username.

Private Sub SetUserNameAndPassword_Click()
If IsNull(Me.First_Name) Or IsNull(Me.Last_Name) Then
      MsgBox "Please enter a first name and last name", vbInformation, "Firstname and Lastname are needed"
      Me.First_Name.SetFocus
Else
      ….  ‘go to Step #2
End If
End Sub

Step #2 Check if already set UserName

We will set up or assign the username to the user that doesn’t have the username yet. There is no change if the username has already been assigned.

Else  ‘ from Step #1
       If IsNull(Me.UserName) Or Me.UserName = "" Then
           …..‘go to Step #3            
       Else
          ‘Do nothing
       End If
End If

Step #3 Set Temp UserName from the 1st format & Assign UserName if available

The 1st format that I will use for the username is the 1st letter of first name and the whole last name. For example, we assign the username “jsmith” to John Smith.

If IsNull(Me.UserName) Or Me.UserName = "" Then  ‘ from step #2
     Dim TempUserName as String
     TempUserName = Left(Me.First_Name, 1) & Me.Last_Name           'return JSmith
     If IsNull(DLookup("userID", "tbluser", "username = '" & TempUserName & "'")) Then
         Me.UserName = TempUserName
     Else
         MsgBox "This username, " & TempUserName & ", is not available"   'JSmith is already assigned to someone else
         …… ‘Go to Step #4         
     End If
Else
     ‘Do nothing
End If

Step #4  Set Temp UserName from the 2nd format & Assign UserName if available

The 2nd format that I used for the username is the first two letters of the first name and the whole last name. For example, we assign the username “josmith” to John Smith. This format will be used if the 1st format (JSmith) is not available.

 Else ‘from step #3
       MsgBox "This username, " & TempUserName & ", is not available"  ‘jsmith is not available
       TempUserName = Left(Me.First_Name, 2) & Me.Last_Name            ‘return JoSmith
       If IsNull(DLookup("userID", "tblUser", "UserName = '" & TempUserName & "'")) Then
             Me.UserName = TempUserName
       Else
             MsgBox "This username, " & TempUserName & ", is not available"   'JoSmith is already assigned to anther user
             …’Go to Step # 5
       End If
End If

Step #5  Set Temp UserName from 3rd format & Assign UserName if available

The 3rd format that I used for the username is the first letter of the first name, the whole last name and the number at the end of last name. For example, we assign the username “jsmith2” to John Smith. This format will be used if the 1st format (JSmith) and the 2nd format (JoSmith) are not available. This format uses For …Loop to assign the number at the end of last name. The number will increment to the next number if the username with that number is not available. For example, if the username “jsmith2” is already assigned then the “jsmith3” will be assigned. If the username “jsmith3” is already assigned then “jsmith4” will be assigned and so on.

Else ‘from step #4
      MsgBox "This username, " & TempUserName & ", is not available"    ‘JoSmith is not available
      Dim i As Integer
      For i = 100 To 2 Step -1
      TempUserName = Left(Me.First_Name, 1) & Me.Last_Name & i   ‘return JSmith2, JSmith3, JSmith4,…, JSmith100
      If IsNull(DLookup("userID", "tblUser", "UserName = '" & TempUserName & "'")) Then
            Me.UserName = TempUserName
      End If
      Next i
End If

  Full Code:

Private Sub SetUserNameAndPassword_Click()
Dim TempUserName As String
Me.Refresh
If IsNull(Me.First_Name) Or IsNull(Me.Last_Name)  then ‘from Step #1
      MsgBox "Please enter first name and last name", vbInformation, "Firstname and Lastname are needed"
      Me.First_Name.SetFocus
Else
     If IsNull(Me.UserName) Or Me.UserName = "" Then  'from Step #2
         TempUserName = Left(Me.First_Name, 1) & Me.Last_Name 'from Step #3         
         If IsNull(DLookup("userID", "tblUser", "UserName = '" & TempUserName & "'")) Then
             Me.UserName = TempUserName
         Else
             MsgBox "This username, " & TempUserName & ", is not avaible" 'JoSmith is not available
             TempUserName = Left(Me.First_Name, 2) & Me.Last_Name          'from Step #4               
             If IsNull(DLookup("userID", "tblUser", "UserName = '" & TempUserName & "'")) Then
                 Me.UserName = TempUserName
             Else
                 MsgBox "This username, " & TempUserName & ", is not avaible"   'JoSmith is not available
                 Dim i As Integer      'from Step #5
                 For i = 100 To 2 Step -1
                 TempUserName = Left(Me.First_Name, 1) & Me.Last_Name & i      'return JSmith2
                 If IsNull(DLookup("userID", "tblUser", "UserName = '" & TempUserName & "'")) Then
                       Me.UserName = TempUserName
                  End If
                  Next i
             End If
         End If 
         Me.Password = "123" 'Set Temp password = 123 if the username is assigned
     Else
        'do nothing if the username is already set for the user
     End If
End If
End Sub

Related Video:

Related posts