MS Access using TempVar for Login and permissions?-CodePudding

I'm attempting to setup a simple login with permissions level in my Access Database. I am trying to use TempVars but for some reason it seems to be just picking the first records permissions rather than the username from the login.

The table "Memberstbl" has the fields "Call Sign" (which is the user name), "Password" and "Permissions" (Numbers 1-3) set for user permissions.

There is a separate table for the Permissions where each number is identified

and a final table that dictates which PermissionID has access to which form, in this case just the "Admin Menu" form

The issue is when I login with the admin account it seems to keep picking up the Permission "1" which is what the first user in the Memberstbl is set to. I don't think it's connecting "Call Sign" with the "Permissions" level, and I'm not sure what I'm missing. I've never used TempVars before so I'm not sure if I'm using it correctly.

Any help would be greatly appreciated!

Code for Login:

Private Sub LogonBtn_Click()
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("Memberstbl")
    
'Username and Password Check
If IsNull(Me.Username) Then
    MsgBox "Please Enter Login ID", vbInformation, "Login Required"
    Me.Username.SetFocus
ElseIf IsNull(Me.Password) Then
    MsgBox "Please Enter Password", vbInformation, "Password Required"
    Me.Password.SetFocus
Else
    If (IsNull(DLookup("[Call Sign]", "Memberstbl", "[Call Sign]='" & Me.Username.Value & "'"))) Or _
       (IsNull(DLookup("Password", "Memberstbl", "Password='" & Me.Password.Value & "'"))) Then
            MsgBox "Incorrect Username or Password"
    Else
        'DoCmd.OpenForm "MainMenu"
        'DoCmd.Close acForm, "Logonfrm"

    TempVars("Memberstbl") = rs!Permissions
        
    End If
End If
    
End Sub

CodePudding user response：

Code is opening an unfiltered recordset then later sets TempVar to value of field in first record so of course you always see the same value. Why open a recordset when all you want is a single value? Use another DLookup. Suggest you not use the table name as a TempVar name.

TempVars("PermCode") = DLookup("Permissions", "Memberstbl", "[Call Sign]= '" & Me.Username & "'")

If you really want to use recordset, still need to apply filter criteria with the user inputs. If recordset is empty then user inputs were not valid. If there is a record, then can reference recordset field for the Permissions value. None of the DLookups would be used with this approach.