Access VBA Runtime Error Code 13 Type mismatch

MHamid

Active Member
Joined
Jan 31, 2013
Messages
375
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:
Code:
 SecurityLevel = DLookup("SecurityLevel", "Employees", "UserName='" & Me.txtusername.Value & "'")


And this is the full code:
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 31, 2013
Messages
375
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 31, 2013
Messages
375
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Jan 11, 2009
Messages
1,178
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

Active Member
Joined
Jan 31, 2013
Messages
375
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 31, 2013
Messages
375
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,508
Messages
5,596,555
Members
414,077
Latest member
ammylar5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top