Count User Entry Errors on User Form Command Click

Hi all,

I am having an issue counting the number of times a user has incorrectly entered a pin number on a user login form. The following code consists of my procedure attached to the Login command button.

Please note lines 89 onwards for the counting ->

Private Sub cmdLogin_Click()
'On Error GoTo Err_Handler

Dim vUserID As Long, vActive As Boolean, vTempPin As Boolean, _
    sct As Section, blnExpanded As Boolean, intLoginAttempts As Integer

'validation of combo selection ->
If IsNull((Me.cboUserName) = True) Or Not (((Me.cboUserName) <> "") = True) Then 'validate username field
    msg = "Please select a valid username."
    title = "Username not selected"
    style = vbOKOnly + vbInformation
    response = MsgBox(msg, style, title)
    Exit Sub

'validation of pin entry ->
ElseIf IsNull((Me.txtPIN) = True) Or Not (((Me.txtPIN) <> "") = True) Then 'validate PIN field
    msg = "Please enter a valid PIN Code." & vbCrLf & "" & vbCrLf & _
    "If you have forgotten your access PIN, you can request a new one by clicking the Forgot Pin link below."
    title = "Wrong PIN entered"
    style = vbOKOnly + vbInformation
    response = MsgBox(msg, style, title)
    Exit Sub

'validation of combined entry ->
ElseIf (((Me.cboUserName) <> "") = True) And (((Me.txtPIN) <> "") = True) Then 'checking the validation on user and pin code ->
    
'declaring DAO recorset ->
    Dim rs As DAO.Recordset
    Dim db As DAO.Database

    'set databse object and open recordset
    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rs = db.OpenRecordset("SELECT * FROM tbl_user WHERE (((UserID)=" & Me.cboUserName.Column(0) & ") AND ((PIN)=" & Me.txtPIN & "))")   
    
    'checking record counts with a match ->
    If rs.RecordCount > 0 Then
        
        'check active account
        vActive = rs.Fields("Active").Value
        vTempPin = rs.Fields("TemporaryPIN").Value
        vUserID = Me.cboUserName.Column(0)
        
        Me.lblIncorrectPin.Caption = ""
        Me.lblIncorrectPin.Visible = False
        
        'check active user ->
        If vActive = False Then
            msg = "User profile: " & Me.cboUserName.Column(1) & " is inactive." & vbCrLf & "" & vbCrLf & _
            "Please login as an administrator to reactivate this user profile."
            title = "Inactive profile"
            style = vbOKOnly + vbInformation
            response = MsgBox(msg, style, title)
            Application.Quit acQuitSaveNone
        Else
            'check temp pin and direct to change passcode ->>
            If vTempPin = True Then
                
                APPUser = Nz(Me.cboUserName.Column(1), "")
                APPUserID = Nz(Me.cboUserName.Column(0), "")
                AppUserType = Nz(Me.cboUserName.Column(6), "")
        
                DoCmd.Close 2, "frm_login"
                DoCmd.OpenForm "frm_user_profile"
                
                'populate the entity details ->
                Call populate_entity_settings("frm_user_profile", 1)
                    
                Set sct = Forms("frm_user_profile").Section(acFooter)
                blnExpanded = sct.Visible
                If Not blnExpanded Then Forms("frm_user_profile").Painting = False
                
                'expand form if currently un-expanded and vice versa
                sct.Visible = Not blnExpanded
                DoCmd.RunCommand acCmdSizeToFitForm
                
                'use the settings for the user to acces and update their password ->
                Call populateUserProfile(APPUserID)
                Forms("frm_user_profile").Controls("txtOldPW").SetFocus
            Else
                'access is okay then update current username and store global variable
                DoCmd.Close 2, "frm_login"
                DoCmd.OpenForm "frm_dashboard"
                'populate the entity details ->
                Call populate_entity_settings("frm_dashboard", 1)
                Forms("frm_dashboard").Repaint
            End If
        End If
       
    Else 'record count check > 0
        'wrong entry prompting user and counting incorrect entry attempts >
        intLoginAttempts = intLoginAttempts + 1
        If intLoginAttempts = 1 Then
            msg = "Wrong PIN entered. Please try again."
            title = "Wrong PIN"
            style = vbOKOnly + vbInformation
            response = MsgBox(msg, style, title)
            Me.txtPIN = Null
            Exit Sub
        ElseIf intLoginAttempts = 2 Then
            msg = "Wrong PIN entered. Please try again."
            title = "Wrong PIN"
            style = vbOKOnly + vbInformation
            response = MsgBox(msg, style, title)
            Me.lblIncorrectPin.Visible = True
            Me.lblIncorrectPin.Caption = "(2 attempts remaining!)"
            Me.txtPIN = Null
            Exit Sub
        ElseIf intLoginAttempts = 3 Then
            msg = "Please enter a valid PIN Code." & vbCrLf & "" & vbCrLf & _
            "If you have forgotten your access PIN, you can request a new one by clicking the Forgot Pin link below."
            title = "Wrong PIN"
            style = vbOKOnly + vbInformation
            response = MsgBox(msg, style, title)
            Me.lblIncorrectPin.Visible = True
            Me.lblIncorrectPin.Caption = "(1 attempts remaining!)"
            Me.txtPIN = Null
            Exit Sub
        ElseIf intLoginAttempts > 3 Then
            msg = "You have exceeded the number of failed login attempts." & vbCrLf & "" & vbCrLf & _
            "For security reasons,the application will now close."
            title = "Failed login attempts exceeded"
            style = vbOKOnly + vbInformation
            response = MsgBox(msg, style, title)
            Application.Quit acQuitSaveNone
        End If
    End If
End If

Err_Handler_Exit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

Err_Handler:
    If Err.Number = 0 Then
        Resume Err_Handler_Exit
    Else        
        Call addDBLog(6, "Error | Trace code and description is: " & Err.Number & " - " & Err.Description & "")
        Resume Err_Handler_Exit
    End If
End Sub

Open in new window


Can anyone shed some light on how I can get it to work?

Thanks
Laila JacksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Looks to me like you need to declare intLoginAttempts at a higher scope, perhaps in the General Declarations section of the form. Or, you can try to declare it as Static:

Static intLoginAttempts As Integer

Variables declared as Static don't lose their value when the procedure ends.

With either of these methods, you should "reset" the variable upon successful login, so when that happens:

intLoginAttempts = 0
Rey Obrero (Capricorn1)Commented:
first, declare this variable " intLoginAttempts As Integer" outside the button click event .
reason, everytime you click the button, the variable is reset to 0.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Laila JacksonAuthor Commented:
Knew it had to be something simple... I declared it as as a public variable and now it works!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.