Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
983 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

721 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