Creating userlevel access type mismatch error (1 Viewer)

K

KpAtch3s

Registered User.
Local time
Yesterday, 18:58
Joined
Sep 28, 2015
Messages
14
I have a form the end user logs in on. When successful it opens a menu form that grants access to other forms. I want to restrict these forms to read only or full rights based on their userlevel. If the userlevel is set to 2 it should be read only.

So I have a query that pulls their userlevel as follows on form open.

Here's my code:

Code: 
Dim UserLevel As Integer

UserLevel = "SELECT UserT.AccessLevelID FROM UserT WHERE (((UserT.Username) = " & [Forms]![CredentialsF]![txtLoginID] & "));"
    
    If UserLevel = 2 Then
         MsgBox "Access granted with limited permissions."
        'Me.AllowEdits = False
        'Me.AllowAdditions = False
        'Me.AllowDeletions = False
        'DoCmd.OpenForm "AcquiredF"
    Else
        MsgBox "Access granted."
        DoCmd.OpenForm "AcquiredF"
    End If

So, if I Dim UserLevel as Integer I get a type mismatch error on line 3 even though the query is pulling an integer. I tried using Variant, but then it skips my if statement and grants access from the else statement.

Any suggestions?
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:58
Joined
Aug 30, 2003
Messages
36,124
Well, you've declared the variable as an Integer and then tried to stuff a string into it. In any case, you can't use it that way. You'd declare the variable as String and then open a recordset on it. You may find DLookup() simpler:

http://www.mvps.org/access/general/gen0018.htm
 
K

KpAtch3s

Registered User.
Local time
Yesterday, 18:58
Joined
Sep 28, 2015
Messages
14
Right, well I also Dimmed as a String, but then I got type mismatch Here:

Code: 
If UserLevel = 2 Then
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:58
Joined
Aug 30, 2003
Messages
36,124
You can't use the SQL that way. Like I said, you'd have to open a recordset on it. That would look like this from a db I have open:

Code: 
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset

  On Error GoTo ErrorHandler

  Set db = CurrentDb()

  strSQL = "SELECT * FROM vueAllCabs " _
         & "WHERE CarNum = " & intCar

  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If rs!YourFieldName = 2 Then
 
S

sneuberg

AWF VIP
Local time
Yesterday, 18:58
Joined
Oct 17, 2014
Messages
3,506
But if you use a recordset you should check for the condition that no record was found. I think Mr pbaldy's suggestion of use dlookup would be easier.
 
Last edited:
K

KpAtch3s

Registered User.
Local time
Yesterday, 18:58
Joined
Sep 28, 2015
Messages
14
I tried using DLookup, but I was having issues trying to get it to pull the right data.

I'm trying to implement recordset as we speak, but I'm missing a parameter I'm trying to track down.
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 03:58
Joined
Nov 3, 2010
Messages
6,142
Provide the actual code used, not stories. Not much help can be provided if you just submit stories. Read my signature.
 
K

KpAtch3s

Registered User.
Local time
Yesterday, 18:58
Joined
Sep 28, 2015
Messages
14
I think I could do with less criticism from the likes of you. I am trying to solve the problem myself as much as I can so that I might learn something, but since you asked:

Code: 
Dim strSQL  As String
Dim db      As DAO.Database
Dim rs      As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT UserT.AccessLevelID FROM UserT WHERE (((UserT.Username) = " & [Forms]![CredentialsF]![txtLoginID] & "));"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    If rs!AccessLevelID = 2 Then
         MsgBox "Access granted with limited permissions."
        'Me.AllowEdits = False
        'Me.AllowAdditions = False
        'Me.AllowDeletions = False
        'DoCmd.OpenForm "AcquiredF"
    Else
        MsgBox "Access granted."
        DoCmd.OpenForm "AcquiredF"
    End If

Missing parameter on

Code: 
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 03:58
Joined
Nov 3, 2010
Messages
6,142
Well go on then , solve it yourself.
 
S

sneuberg

AWF VIP
Local time
Yesterday, 18:58
Joined
Oct 17, 2014
Messages
3,506
Suggest putting a Debug.Print strSQL after the statement

strSQL = "SELECT User .....

And checking the Immediate window after you try it. What's it look like?
 
K

KpAtch3s

Registered User.
Local time
Yesterday, 18:58
Joined
Sep 28, 2015
Messages
14
I'm getting the following:

Code: 
SELECT UserT.AccessLevelID FROM UserT WHERE (((UserT.Username) = testuser));
SELECT UserT.AccessLevelID FROM UserT WHERE (((UserT.Username) = testuser));
SELECT UserT.AccessLevelID FROM UserT WHERE (((UserT.Username) = testuser));SELECT UserT.AccessLevelID FROM UserT WHERE (((UserT.Username) = testuser));
 
S

sneuberg

AWF VIP
Local time
Yesterday, 18:58
Joined
Oct 17, 2014
Messages
3,506
That there testuser is a string. You need some single quotes like:

strSQL = "SELECT UserT.AccessLevelID FROM UserT WHERE (((UserT.Username) = '" & [Forms]![CredentialsF]![txtLoginID] & "'));"

Try that.
 
K

KpAtch3s

Registered User.
Local time
Yesterday, 18:58
Joined
Sep 28, 2015
Messages
14
Thank you, sneuberg and pbaldy. Works as intended.
 
S

sneuberg

AWF VIP
Local time
Yesterday, 18:58
Joined
Oct 17, 2014
Messages
3,506
Maybe you know this but you still need to check for no record which you can do with the condition

If Not rs.EOF AND NOT rs.BOF Then
'Whatever you are going to do for user not in the database

Also you will find if the user happens to put a single quote in the [txtLoginID] you will get a syntax error. You can use the replace function to guard against this. See
http://mikeperris.com/access/escaping-quotes-Access-VBA-SQL.html for an explanation
 
K

KpAtch3s

Registered User.
Local time
Yesterday, 18:58
Joined
Sep 28, 2015
Messages
14
Right, but the way this has been built; it's impossible for users to get here without a login and password except for me.

I'll look into your second suggestion though even though I'll probably be the one assigning usernames.
 
S

sneuberg

AWF VIP
Local time
Yesterday, 18:58
Joined
Oct 17, 2014
Messages
3,506
KpAtch3s said:
I'll look into your second suggestion though even though I'll probably be the one assigning usernames.
Click to expand...

I think you will want to escape the single quotes no matter what names are assigned. The user could accidentally type a single quote in his user name and be presented with a error message that won't make any sense at all.

PS: Could you tell us a good story. Something in the bedtime genre with lots of blood and guts. No code please.:)
 
You must log in or register to reply here.

Similar threads

R
data type mismatch at query, that should supply data to form
2
Replies
23
Views
673
ReginaF
R
EL_Gringo
Solved Set frm = Forms(FormName): Error 438
Replies
11
Views
929
EL_Gringo
EL_Gringo
C
Read stock prices from webpage
Replies
6
Views
241
cpampas
C
P
Solved Access vba Manage an Attachment field with vba
Replies
8
Views
412
Pat Hartman
P
Mylton
Solved Difficulty with login and permission
Replies
16
Views
1,008
Mylton
Mylton

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom