User level security with VB in Access 2007 - Code needs to open a different form depending on the user.

I have a database where I have created:

1) tblLogin:
     UserName
     Password
     Type:  user, admin or manager
     
2) frmLogin  (2 command buttons)
       cmdLogin  (tied to code below)
       cmdClose(tied to a macro to exit database)

3) 3 Menus:
       frmMainMenuAdmin
       frmMainMenuUser
       frmMainMenuManager


What I would like is to modify my code that depending on the type of user, a different frm will pop up.
Either frmMainMenuAdmin >>>>>tied to admin (type)
           frmMainMenuUser >>>>>tied to user (type)
           frmMainMenuManager >>>>>tied to manager (type)

I cannot seem to get this to work correctly.  Can someone please help?
---------------------------------------------------------------------------------------------------------------------------
Here is my code for the login:
Private Sub cmdLogin_Click()

Dim dbs As Database
Dim rstUserPwd As Recordset
Dim bFoundMatch As Boolean

Set dbs = CurrentDb

Set rstUserPwd = dbs.OpenRecordset("qryUserPwd")

bFoundMatch = False

If rstUserPwd.RecordCount > 0 Then
    rstUserPwd.MoveFirst
    
    ' Check for matching records
    Do While rstUserPwd.EOF = False
    If rstUserPwd![UserName] = Form_frmLogin.txtUserName.Value And rstUserPwd![Password] = Form_frmLogin.txtPassword.Value Then
        bFoundMatch = True
        Exit Do
    End If
        rstUserPwd.MoveNext
        Loop
End If

If bFoundMatch = True Then
' Open the next form here and close this one
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmMainMenuAdmin"
'Change this to the switchboard form name'

Else
    MsgBox "Incorrect Username or Password"

End If

rstUserPwd.Close
    
End Sub

Open in new window

gracie1972Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Make sure that your query includes the user type, and try this - note the SELECT CASE block I added:

Private Sub cmdLogin_Click()

Dim dbs As Database
Dim rstUserPwd As Recordset
Dim bFoundMatch As Boolean

Set dbs = CurrentDb

Set rstUserPwd = dbs.OpenRecordset("qryUserPwd")

bFoundMatch = False

If rstUserPwd.RecordCount > 0 Then
    rstUserPwd.MoveFirst
    
    ' Check for matching records
    Do While rstUserPwd.EOF = False
    If rstUserPwd![UserName] = Form_frmLogin.txtUserName.Value And rstUserPwd![Password] = Form_frmLogin.txtPassword.Value Then
        bFoundMatch = True
        Exit Do
    End If
        rstUserPwd.MoveNext
        Loop
End If

If bFoundMatch = True Then
' Open the next form here and close this one
DoCmd.Close acForm, Me.Name
SELECT Case rstUserPwd!Type
    Case "admin"
           DoCmd.OpenForm "frmMainMenuAdmin"
    Case "user"
           DoCmd.OpenForm "frmMainMenuUser"
    Case "manager"
           DoCmd.OpenForm "frmMainMenuManager"
End Select

'Change this to the switchboard form name'

Else
    MsgBox "Incorrect Username or Password"

End If

rstUserPwd.Close
    
End Sub

Open in new window

0
 
gracie1972Author Commented:
I get a compile error, type mismatch.
0
 
gracie1972Author Commented:
Don/t I need to reference the field name anywhere?  [type] ?
0
 
mbizupCommented:
Yup...

I edited the code to correct that omission - probably while you were looking at it.  Give it another try.
0
 
gracie1972Author Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.