Invalid use of Null when using DLookup in MS Access

I want to set the user login access in MS Access which means that if the user logs in as the admin it will show a different form.

I have tried to get the userlevel which is a string and will show things like "Admin" or "User" but it indicated:

Invalid use of Null

At this line:

UserLevel = DLookup("UserSecurity", "tblUser", "[UserLogin] = ' " & Me.txtLoginID.Value & "'")

Here is the full code:

Private Sub Command1_Click()
Dim UserLevel As String 'get the dlookup value
Dim TempPass As String

If IsNull(Me.txtLoginID) Then
   MsgBox "Please Enter Login ID", vbInformation, "Login Id Required"
   Me.txtLoginID.SetFocus

   ElseIf IsNull(Me.txtLoginPass) Then
       MsgBox "Please Enter Password", vbInformation, "Login password Required"
   Me.txtLoginPass.SetFocus
Else
'process the job
   If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin ='" & Me.txtLoginID.Value & "'"))) Or _
   (IsNull(DLookup("password", "tblUser", "Password = '" & Me.txtLoginPass.Value & "'"))) Then
       MsgBox "Incorrect Password"
   Else
     TempPass = DLookup("password", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")

     UserLevel = DLookup("UserSecurity", "tblUser", "[UserLogin] = ' " & Me.txtLoginID.Value & "'")
     'get the usersecurity whcih indicate he is admin or user

   DoCmd.Close
        If UserLevel = "Admin" Then 'if admin then open employee form else open customer form
           'MsgBox "Login ID and password correct "
           DoCmd.OpenForm "Employee"
       Else
           DoCmd.OpenForm "CustomerForm"
       End If   
   End If
End If
End Sub

I have tried to use Nz() but it gives me a null value which takes me to the customer form.

Solution

To explain the error that you are receiving: this arises when you attempt to assign a Null value to a variable whose data type is not a Variant, per the MS documentation:

A Variant is a special data type that can contain any kind of data [...] A Variant can also contain the special values Empty, Error, Nothing, and Null.

This error arises in your code because the DLookup function will return Null when no records in the domain fulfill the supplied criteria argument, and can be boiled down to the following two lines:

Dim UserLevel As String
UserLevel = DLookup("UserSecurity", "tblUser", "[UserLogin] = ' " & Me.txtLoginID.Value & "'")

I suspect that this is caused by the leading space in your criteria argument:

"[UserLogin] = ' " & Me.txtLoginID.Value & "'"
                ^--------------------------------- HERE

Which should probably be:

"[UserLogin] = '" & Me.txtLoginID.Value & "'"

However, you may still wish to account for the case in which no records meet the criteria, which can be accomplished in several ways.

You could use the Nz function and then test for an empty string, e.g.:

UserLevel = Nz(DLookup("UserSecurity", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'"), "")

Select Case UserLevel
    Case "Admin": DoCmd.OpenForm "Employee"
    Case "User" : DoCmd.OpenForm "CustomerForm"
    Case Else   : MsgBox "Invalid UserSecurity Value"
End Select

Or, you could define the UserLevel variable as a Variant (hence permitting a Null value), and test whether such variable is Null using the IsNull function:

Dim UserLevel As Variant ' Or just "Dim UserLevel" since Variant is the default type
UserLevel = DLookup("UserSecurity", "tblUser", "[UserLogin] = '" & Me.txtLoginID.Value & "'")

If IsNull(UserLevel) Then
    MsgBox "Invalid UserSecurity Value"
ElseIf UserLevel = "Admin" Then
    DoCmd.OpenForm "Employee"
Else 
    DoCmd.OpenForm "CustomerForm"
End If