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
923 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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now