Results 1 to 11 of 11
  1. #1
    robertmarkdudley95 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    17

    Exclamation Creating query-based login form that directs different users to different departments

    Hi, I'm new to Access so go easy on me!

    I have a logon screen in an Access database as follows:

    Click image for larger version. 

Name:	Capture.JPG 
Views:	36 
Size:	29.8 KB 
ID:	6444

    I already have the code for the "Login" button, which is:

    Code:
    Private Sub Command0_Click()
    
    
    Dim dbs As DAO.Database
    Dim rstUserPwd As Recordset
    Dim bFondMatch 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_frmLogon.txtUsername.Value And rstUserPwd![Password] = Form_frmLogon.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 "frmSwitchboard2"
    
    
    Else
    '
    MsgBox "Incorrect username or password!", vbCritical
    
    
    End If
    
    
        intLogonAttempts = intLogonAttempts + 1
        If intLogonAttempts > 3 Then
          MsgBox "You do not have access to this database.Please contact admin.", _
                   vbCritical, "Restricted Access!"
            Application.Quit
        End If
    
    
    rstUserPwd.Close
    End Sub
    However, what I'm wanting to do is - based on the department that the user belongs to - redirect each different user to their own department. For example, jdoe might go to frmSwitchboard_Finance, whereas the database should redirect jsmith to frmSwitchboard_Management​. How would I do this?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Presuming you have the department in the recordset, grab it and use a Select/Case structure to open the appropriate form.

    That's an inefficient way to get the user by the way. I'd open the recordset on an SQL statement with a criteria of the user and password. If they don't match, it will be EOF, otherwise you have your validated user's info.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Here is another solution that uses the the users login to Windows to make screens available.

    http://www.mrexcel.com/forum/showthread.php?t=248191

  4. #4
    robertmarkdudley95 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    17
    Quote Originally Posted by pbaldy View Post
    Presuming you have the department in the recordset, grab it and use a Select/Case structure to open the appropriate form.

    That's an inefficient way to get the user by the way. I'd open the recordset on an SQL statement with a criteria of the user and password. If they don't match, it will be EOF, otherwise you have your validated user's info.
    Um... how would I go about doing either of these?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Select Case rstUserPwd!DepartmentFieldName

    Look in VBA help for the structure of Select/Case.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    robertmarkdudley95 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    17
    Quote Originally Posted by pbaldy View Post
    Select Case rstUserPwd!DepartmentFieldName

    Look in VBA help for the structure of Select/Case.
    Thanks for this . Whereabouts in the code would I place this?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The Select/Case block would replace your OpenForm line. It basically would open a different form based on the contents of the recordset field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    robertmarkdudley95 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    17
    Quote Originally Posted by pbaldy View Post
    The Select/Case block would replace your OpenForm line. It basically would open a different form based on the contents of the recordset field.
    Oh dear. I'm still confused! Shall I upload the accdb​ file and let you diagnose it from there?

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you like.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    robertmarkdudley95 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    17
    OK, it doesn't matter anymore. I finally sorted it out with a couple of "IF" statements

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-19-2011, 12:16 PM
  2. Replies: 6
    Last Post: 12-12-2011, 01:28 PM
  3. Replies: 7
    Last Post: 06-24-2011, 10:42 PM
  4. Replies: 0
    Last Post: 08-31-2009, 10:50 AM
  5. Replies: 1
    Last Post: 01-04-2008, 11:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums