Creating userlevel access type mismatch error

[KpAtch3s]

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:

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]

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

 

[KpAtch3s]

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

If UserLevel = 2 Then

 

[pbaldy]

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:

  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

 

[sneuberg]

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.

 

[KpAtch3s]

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]

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

 

[KpAtch3s]

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:

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

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset

 

[spikepl]

Well go on then , solve it yourself.

 

[sneuberg]

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?

 

[KpAtch3s]

I'm getting the following:

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));

 

[sneuberg]

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.

 

[KpAtch3s]

Thank you, sneuberg and pbaldy. Works as intended.

 

[sneuberg]

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

 

[KpAtch3s]

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.

 

[sneuberg]

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

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.

 

[KpAtch3s]

I'll just leave this right here:

https://youtu.be/n-wUdetAAlY