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

[robertmarkdudley95]

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

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



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

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?

[pbaldy]

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.

[alansidman]

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

[robertmarkdudley95]

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?

[pbaldy]

Select Case rstUserPwd!DepartmentFieldName

Look in VBA help for the structure of Select/Case.

[robertmarkdudley95]

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?

[pbaldy]

The Select/Case block would replace your OpenForm line. It basically would open a different form based on the contents of the recordset field.

[robertmarkdudley95]

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?

[pbaldy]

If you like.

[robertmarkdudley95]

OK, it doesn't matter anymore. I finally sorted it out with a couple of "IF" statements

[pbaldy]

Glad you got it sorted out.