Access VBA Runtime Error Code 13 Type mismatch

[MHamid]

Hello,

I have a code in access to open certain forms depending on the security level of the user.
However, I have been getting a run time error code 13 type mismatch. What am I doing wrong?

This is the section where the error pops up:

 SecurityLevel = DLookup("SecurityLevel", "Employees", "UserName='" & Me.txtusername.Value & "'")

And this is the full code:

Option Compare Database
Option Explicit

Private Sub btnlogin_Click()
    Dim SecurityLevel As Integer
    Dim rs As Recordset
    
    Set rs = CurrentDb.OpenRecordset("employees", dbOpenSnapshot, dbReadOnly)
    
    rs.FindFirst "UserName='" & Me.txtusername & "'"
        
    If rs.NoMatch = True Then
        Me.lblwronguser.Visible = True
        Me.txtusername.SetFocus
        Exit Sub
    End If
    Me.lblwronguser.Visible = False
                
    If rs!Password <> Me.txtpassword Then
        Me.lblwrongpassword.Visible = True
        Me.txtpassword.SetFocus
        Exit Sub
    End If
    Me.lblwrongpassword.Visible = False
    
    SecurityLevel = DLookup("SecurityLevel", "Employees", "UserName='" & Me.txtusername.Value & "'")
    DoCmd.Close acForm, Me.Name
        If SecurityLevel = 1 Then
            DoCmd.OpenForm "Admin Form"
        Else
            DoCmd.OpenForm "Management Form"
        End If
        
        If SecurityLevel = 2 Then
            DoCmd.OpenForm "Management Form"
        Else
            DoCmd.OpenForm "User Form"
        End If
                
End Sub

 

[xenou]

What do you expect to get back from this DLookup?

DLookup("SecurityLevel", "Employees", "UserName='" & Me.txtusername.Value & "'")

note that you already have the record from this search:

rs.FindFirst "UserName='" & Me.txtusername & "'"

so no need to look up again.

 

[MHamid]

Hello,

The DLookup is pulling back the security level for each user. There are 3 security levels and based on the user's security level a particular form should open after they login.

Does the security level code not work because of the recordset I have setup in the beginning of the code?

 

[xenou]

It should work I think. But it's not necessary because you have already got the record and you can use the recordset:

SecurityLevel = rs.Fields("SecurityLevel").Value

I am asking about the data because you have a datatype error. So what is the data type? What is the value?

 

[MHamid]

The security level values are admin, user, and manager.
I was looking at a youtube video yesterday and it was mentioned that "Dim SecurityLevel As Integer" should be added since the Security level is not a number and that should convert it to a number. Unless if I misunderstood.

 

[james_lankford]

if I were to open the employees table and look at the SecurityLevel field what would I see ?

you say its not a number, but you do this
If SecurityLevel = 1 Then

and you say "The security level values are admin, user, and manager"

so would I see the actual text values
'admin'
'user'
'manager'

or would I see numbers
1
2
3

 

[MHamid]

You would see the text value in the employees table.
I did create another table labeled SecurityLevel, but that only shows the text value (admin, manager, user) and the corresponding numbers (1,2,3) I am using for the code.

 

[xenou]

IF the values are text in the employees table that is the source of the error. Your code has declared SecurityLevel as an Integer and that is not compatible with text.

 

[MHamid]

HA! Yes, that was the issue. I did not link both table. I linked them by Security Level and now the code works perfectly.

Thank you!!