Link to home
Create AccountLog in
Avatar of gracie1972
gracie1972Flag for United States of America

asked on

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:
     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:

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
    ' 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
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'

    MsgBox "Incorrect Username or Password"

End If

End Sub

Open in new window

Avatar of mbizup
Flag of Kazakhstan image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of gracie1972


I get a compile error, type mismatch.
Don/t I need to reference the field name anywhere?  [type] ?

I edited the code to correct that omission - probably while you were looking at it.  Give it another try.
Thank you!