Open different forms for different users on login

Question

I have a table with name tbl_login with fields:

UserID, FirstName, LastName, UserName, Password

I also have a form with name frm_login with textboxes:

One for Username with textbox name : txt_username

the other for passowrd with textbox name : txt_password

i have a command button with name cmd_login with the following codes behind it.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
Me.txt_username.SetFocus
Exit Sub
End If

If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
Me.txt_password.SetFocus
Exit Sub
End If

'query to check if login details are correct'
strSQL = "SELECT FirstName FROM tbl_login WHERE Username = """ & Me.txt_username.Value & """ AND Password = """ & Me.txt_password.Value & """"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
If rst.EOF Then
MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
Me.txt_username.SetFocus
Else
MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, title:="SOFTWARE"
DoCmd.OpenForm "A"

I will like to create a new field in tbl_login with the name Access_level which will contain the following user access permissions;

Administrator,Accounts,Secretary

I will like my code to be re modified such that:

when login with access_level administrator, it should open form A when login with access_level Accounts, it should open form B when login with access_level Secretary, it should open form C

Any help with this will be greatly appreciated

Answer 1

Consider using a stored query and avoid concatenated SQL strings with DAO objects.

SQL (save below as query object to be used in DLookup below)

SELECT FirstName, access_level
FROM tbl_login 
WHERE Username = Forms!frm_login!txt_username
  AND Password = Forms!frm_login!txt_password

VBA (no DAO objects or SQL string)

Dim first_name As Variant, access_level As Variant

If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", _
           buttons:=vbExclamation, title:="Username Required" _
    Me.txt_username.SetFocus
    Exit Sub
End If

If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", _
           buttons:=vbExclamation, title:="Password Required" _
    Me.txt_password.SetFocus
    Exit Sub
End If

' RETREIVE FROM SAVED QUERY
' ASSUMES EVERY USER GIVEN A NON-NULL ACCESS LEVEL
first_name = DLookUp("FirstName", "mySavedQuery")
access_level = DLookUp("access_level", "mySavedQuery")

If IsNull(first_name) = False Then
    MsgBox prompt:="Incorrect username/password. Try again.", _
           buttons:=vbCritical, title:="Login Error" _
    Me.txt_username.SetFocus
Else
   MsgBox prompt:="Hello, " & first_name & ".", _
          buttons:=vbOKOnly, title:="SOFTWARE"
   
   ' CONDITIONALLY OPEN FORMS 
   Select Case access_level
         Case "Administrator"
         DoCmd.OpenForm "A"

         Case "Accounts"
         DoCmd.OpenForm "B"

         Case "Secretary"
         DoCmd.OpenForm "C"
   End Select
End If