Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-03-2015, 10:34 AM   #1
KpAtch3s
Newly Registered User
 
Join Date: Sep 2015
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
KpAtch3s is on a distinguished road
Creating userlevel access type mismatch error

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?

KpAtch3s is offline   Reply With Quote
Old 11-03-2015, 10:41 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,263
Thanks: 13
Thanked 4,110 Times in 4,042 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Creating userlevel access type mismatch error

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
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-03-2015, 10:52 AM   #3
KpAtch3s
Newly Registered User
 
Join Date: Sep 2015
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
KpAtch3s is on a distinguished road
Re: Creating userlevel access type mismatch error

Right, well I also Dimmed as a String, but then I got type mismatch Here:

Code:
If UserLevel = 2 Then

KpAtch3s is offline   Reply With Quote
Old 11-03-2015, 11:02 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,263
Thanks: 13
Thanked 4,110 Times in 4,042 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Creating userlevel access type mismatch error

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
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
KpAtch3s (11-03-2015)
Old 11-03-2015, 12:07 PM   #5
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Creating userlevel access type mismatch error

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 by sneuberg; 11-03-2015 at 12:10 PM. Reason: deleted red herring
sneuberg is offline   Reply With Quote
Old 11-03-2015, 12:17 PM   #6
KpAtch3s
Newly Registered User
 
Join Date: Sep 2015
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
KpAtch3s is on a distinguished road
Re: Creating userlevel access type mismatch error

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.
KpAtch3s is offline   Reply With Quote
Old 11-03-2015, 12:26 PM   #7
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Creating userlevel access type mismatch error

Provide the actual code used, not stories. Not much help can be provided if you just submit stories. Read my signature.

__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 11-03-2015, 12:29 PM   #8
KpAtch3s
Newly Registered User
 
Join Date: Sep 2015
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
KpAtch3s is on a distinguished road
Re: Creating userlevel access type mismatch error

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
KpAtch3s is offline   Reply With Quote
Old 11-03-2015, 12:31 PM   #9
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Creating userlevel access type mismatch error

Well go on then , solve it yourself.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 11-03-2015, 12:38 PM   #10
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Creating userlevel access type mismatch error

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?
sneuberg is offline   Reply With Quote
Old 11-03-2015, 12:47 PM   #11
KpAtch3s
Newly Registered User
 
Join Date: Sep 2015
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
KpAtch3s is on a distinguished road
Re: Creating userlevel access type mismatch error

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));
KpAtch3s is offline   Reply With Quote
Old 11-03-2015, 12:53 PM   #12
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Creating userlevel access type mismatch error

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.
sneuberg is offline   Reply With Quote
The Following User Says Thank You to sneuberg For This Useful Post:
KpAtch3s (11-03-2015)
Old 11-03-2015, 01:00 PM   #13
KpAtch3s
Newly Registered User
 
Join Date: Sep 2015
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
KpAtch3s is on a distinguished road
Re: Creating userlevel access type mismatch error

Thank you, sneuberg and pbaldy. Works as intended.
KpAtch3s is offline   Reply With Quote
Old 11-03-2015, 01:15 PM   #14
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Creating userlevel access type mismatch error

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/escapin...s-VBA-SQL.html for an explanation
sneuberg is offline   Reply With Quote
Old 11-03-2015, 01:43 PM   #15
KpAtch3s
Newly Registered User
 
Join Date: Sep 2015
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
KpAtch3s is on a distinguished road
Re: Creating userlevel access type mismatch error

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.

KpAtch3s is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Date import from excel to access - type mismatch error ketangarg86 General 1 09-17-2014 03:06 PM
mismatch in expression type error in a massive access query Snorky85 General 2 03-05-2013 07:42 PM
type mismatch runtime error 13 access 2007 tintinw General 1 12-17-2009 11:33 AM
Error Type Mismatch (Access 2007) coolcatkelso Modules & VBA 7 02-07-2009 11:13 AM
Type mismatch error in Access 2003 TheCatWho Modules & VBA 2 02-07-2005 12:15 PM




All times are GMT -8. The time now is 02:49 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World