Login Access

'Main Class

Option Compare Database

Const formEmployee = "form_employee"
Const menuMain = "menu_main"
Const menuAdmin = "menu_admin"
Const subformLoginMask = "subform_loginMask"
Const subformAutostart = "subform_autostart"

Public Sub checkLoginInputs(ByVal formName As String)

    Dim userNickname As String
    Dim userPassword As String
    Dim userPasswordSql As String
    Dim userPositionSql As String

    Dim userInputNickname As String
    Dim userInputPassword As String

    'Assign nickname and password from the entered information
    userInputNickname = Forms(formName).btnNickname.value
    userInputPassword = Forms(formName).btnPassword.value

    'Prepare sql query
    userPasswordSql = "SELECT Nickname, Password FROM tbl_employee" _
    & " WHERE " & vbDoubleQuote & userInputNickname & vbDoubleQuote & "=Nickname;"

    On Error GoTo exceptionNoNicknameFound
    'Get values from query
    Set queryRecords = CurrentDb.OpenRecordset(userPasswordSql, dbOpenDynaset)

    'Query information assign to variable
    userNickname = queryRecords(0)
    userPassword = queryRecords(1)

    If (userPassword = userInputPassword) Then
        'Find out what kind of position does got the employee
        Dim userPositionPermissionSql As String

        userPositionPermissionSql = "SELECT Positionname FROM tbl_employee WHERE Nickname = " & vbDoubleQuote & userNickname & vbDoubleQuote
        Set queryRecords = CurrentDb.OpenRecordset(userPositionPermissionSql, dbOpenDynaset)

        On Error GoTo exceptionNoPositionSelected
        Call arrangeVisibility(queryRecords(0))
        DoCmd.Close acForm, subformLoginMask, acSaveYes
        MsgBox "You logged in as " & queryRecords(0), vbOKOnly
    Else
        GoTo exceptionPasswordWrong
        Forms(menuMain).adminLoggedIn.value = "false"
    End If

Exit Sub
exceptionNoPositionSelected:
    exceptionHandler ("NoPositionSelected")
Exit Sub
exceptionPasswordWrong:
    exceptionHandler ("PasswordWrong")
Exit Sub
exceptionNoNicknameFound:
    exceptionHandler ("NoNicknameFound")

End Sub

Private Sub setStateElement(ByVal formName As String, ByVal element As String, ByVal property As String, ByVal value As String)

    DoCmd.OpenForm formName:=formName

    Set formObj = Forms(formName)
    Call propertyWrapper(formObj.Controls(element), property, value)

    DoCmd.Close acForm, formName, acSaveYes

    'Reset refs
    Set formObj = Nothing
End Sub

Private Function propertyWrapper(ByRef contl As Control, ByVal property As String, ByVal value As String)
    'On Error GoTo exceptionPropertyDoesNotExists
    Debug.Print TypeName(contl)
    Select Case property
        Case "value"
            contl.value = value
        Case "visible"
            contl.visible = value
        Case "Caption"
            contl.Caption = value
    End Select

    'propertyWrapper = contl
    Exit Function
exceptionPropertyDoesNotExists:
    exceptionHandler ("PropertyDoesNotExists")
End Function

Private Sub arrangeVisibility(ByVal position As String)
    Dim userSecurityLevel As String

    'Get security level from the position
    userSecurityLevel = "SELECT UserSecurityLevel FROM tbl_employeePosition WHERE PositionName = " & vbDoubleQuote & position & vbDoubleQuote & ";"
    Set queryRecords = CurrentDb.OpenRecordset(userSecurityLevel, dbOpenDynaset)

    'Depend on the security level we enable or disable forms and possibilities
    Select Case queryRecords(0)
        Case "Admin"
            Call setStateElement(menuMain, "adminLoggedIn", "Caption", "true")
            Call showPanels
            'Switch form to admin menu
            DoCmd.OpenForm formName:=menuAdmin
        Case Else
            Call setStateElement(menuMain, "adminLoggedIn", "Caption", "false")
            Call hidePanels
            DoCmd.OpenForm formName:=menuMain
    End Select
End Sub

Private Sub hidePanels()
    DoCmd.RunCommand acCmdWindowHide
    DoCmd.ShowToolbar "Ribbon", acToolbarNo
    DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide
End Sub

Private Sub showPanels()
    'DoCmd.RunCommand acCmdWindowUnhide
    DoCmd.ShowToolbar "Ribbon", acToolbarYes
    DoCmd.SelectObject acForm, , True
End Sub

Private Sub formVisibility(ByVal formName As String, ByVal visible As Boolean)
    'Open form then set the visibility and close it otherwise a error occurs
    DoCmd.OpenForm formName:=formName
    Forms(formName).visible = visible
    DoCmd.Close acForm, formName
End Sub

'Check if the label which is hidden in the main menu form got a value to indicate whether an admin is logged in
Public Function isAdmin() As Boolean
    Dim adminTextValue As String

    DoCmd.OpenForm formName:=menuMain
    adminTextValue = Forms(menuMain).adminLoggedIn.Caption

    If (adminTextValue = "true") Then
        DoCmd.Close acForm, menuMain
        isAdmin = True
    Else
        isAdmin = False
    End If
End Function

'Will invoke if the button access to admin area is performed
Public Sub accessAdminArea()
    Dim adminLoggedIn As Boolean
    adminLoggedIn = isAdmin

    If (adminLoggedIn) Then
        DoCmd.OpenForm formName:=menuAdmin
    Else
        MsgBox "You are not an admin", vbCritical, "Log in as admin"
    End If
End Sub

'Will invoke if the database is opened
Public Sub defaultCondition()
    DoCmd.OpenForm formName:=menuMain
    Forms(menuMain).adminLoggedIn.Caption = "false"
    DoCmd.Close acForm, menuMain
    Call hidePanels
End Sub

Public Function normalizeDatabase()
    Call defaultCondition
    DoCmd.Close acForm, subformAutostart
    DoCmd.OpenForm formName:=subformLoginMask
End Function



'Exceptions

Option Compare Database

'Exceptionhandler - processes all received messages
Public Sub exceptionHandler(ByVal exceptionIdentifier As String)
    Select Case (exceptionIdentifier)
        Case "NoEntry"
            MsgBox "Add first the calculation to the database", vbCritical, "No Record found"
        Case "NoForm"
            MsgBox "No Form was found", vbCritical, "No Form found"
        Case "NoOrderId"
            MsgBox "No Order Id was found", vbCritical, "No Order Id found"
        Case "SQLSyntax"
            MsgBox "Sql syntax is wrong", vbCritical, "Sql syntax error"
        Case "NoNicknameFound"
            MsgBox "Your login name doesent exists", vbCritical, "Login name not exist"
        Case "NoPositionSelected"
            MsgBox "You entered employee has no position. Please inform the admin"
        Case "PropertyDoesNotExists"
            MsgBox "Property is not available on the transfered object", vbCritical
        Case "PasswordWrong"
            MsgBox "Your entered password is wrong", vbCritical, "Password invalid"
    End Select
End Sub