You are missing 7 "End If" lines. For every ELSE, you need an "End If":
Code:
<snip>
If rs.RecordCount > 0 Then
If rs.Fields(0) = "Admin" Then
Me.Visible = False
DoCmd.OpenForm "frmAhome", acNormal
End If
Else
If rs.RecordCount > 0 Then
If rs.Fields(0) = "Teaching" Then
Me.Visible = False
DoCmd.OpenForm "frmThome", acNormal
End If
Else
If rs.RecordCount > 0 Then
If rs.Fields(0) = "Compliance" Then
Me.Visible = False
DoCmd.OpenForm "frmComhome", acNormal
End If
Else
If rs.RecordCount > 0 Then
If rs.Fields(0) = "Mgmt" Then
Me.Visible = False
DoCmd.OpenForm "frmMhome", acNormal
End If
Else
If rs.RecordCount > 0 Then
If rs.Fields(0) = "Accounting" Then
Me.Visible = False
DoCmd.OpenForm "frmAcchome", acNormal
End If
Else
If rs.RecordCount > 0 Then
If rs.Fields(0) = "Student" Then
Me.Visible = False
DoCmd.OpenForm "frmShome", acNormal
End If
Else
If rs.RecordCount > 0 Then
If rs.Fields(0) = "Faculty" Then
Me.Visible = False
DoCmd.OpenForm "frmFhome", acNormal
End If
Else
If rs.RecordCount = 0 Then
MsgBox "You do not have access, see Admin.", vbOKCancel, "Warning!"
DoCmd.CloseDatabase
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Add the BLUE lines...
However, you don't need a lot of the code as written. You only need to check if "rs.RecordCount > 0" one time.
I would try this code:
Code:
Option Compare Database
Option Explicit
Private Sub Form_Load() '// process ui style
Call UISetRoundRect(Me, 16, False)
Dim db As DAO.Database 'added
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strDept As String
Dim strUserName As String
On Error Resume Next
strUserName = UserName.Value
Set db = CurrentDb
strSQL = "SELECT fldDept FROM tblUsers WHERE fldUsername='" & strUserName & "'"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
strDept = NZ(rs.Fields(0) ,"") 'in case of Nulls
Me.Visible = False 'common to ALL IFs
Select Case strDept
Case "Admin"
DoCmd.OpenForm "frmAhome"' ' acNormal is the default view
Case "Teaching"
DoCmd.OpenForm "frmThome"
Case "Compliance"
DoCmd.OpenForm "frmComhome"
Case "Mgmt"
DoCmd.OpenForm "frmMhome"
Case "Accounting"
DoCmd.OpenForm "frmAcchome"
Case "Student"
DoCmd.OpenForm "frmShome"
Case "Faculty"
DoCmd.OpenForm "frmFhome"
Case Else
MsgBox "You do not have access, see Admin.", vbOKCancel, "Warning!"
' DoCmd.CloseDatabase
'clean up
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.Quit
End Select
End If
'clean up
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub