Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-07
5
Medium Priority
?
989 Views
Last Modified: 2013-01-07
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

0
Comment
Question by:gracie1972
  • 3
  • 2
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38751590
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
 

Author Comment

by:gracie1972
ID: 38751611
I get a compile error, type mismatch.
0
 

Author Comment

by:gracie1972
ID: 38751615
Don/t I need to reference the field name anywhere?  [type] ?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38751625
Yup...

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

Author Closing Comment

by:gracie1972
ID: 38751637
Thank you!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question