Storing failed login attempts

[LukeD1994]

Hello all, this is my first post so please don't go harsh on me
I did as much research as possible prior to this...

So basically, I have a database with user log in form (complete with user levels etc.)

My form, asks for the username and password from the person and checks it with the "tblUsers" table which stores the Id, username, password and level.

Before I used a simple method of integers, counting the number of failed logins.
Exactly the same as some other guys post about Forcing a database to close after 3x logins (can't link it as don't have 10 posts)

It basically, checked for 3 failed attempts and then closed the application.

Recently however I chose to expand this to a more secure system.

What I have inside tblUsers is a new field "LogAttempts".
Where I want to store the login attempts each time, per user.
From this I can then use DLookup to check this value and prevent further access (thus stopping the user closing the application and starting their 3 strikes all over again)

What I need to know is, how do I cause the code to write to the table's exact field for the user trying to login? And of course save it.

Could someone please point me in the right direction (preferable with snippets of code so I can see what you are on about, I'm a visual learner)

My Code

private LogAttempt as Integer

Public Sub Login()
        
On Error GoTo ErrorHandler:

    If IsNull([cboUser]) = True Then 'Check UserName
        MsgBox "Username is required"
        
    ElseIf IsNull([txtPassword]) = True Then 'Check Password
        MsgBox "Password is required"
        
    Else
    
        'Compare value of txtPassword with the saved Password in tblUser
        If Me.txtPassword.Value = DLookup("Password", "tblUsers", "[UserName]='" & Me.cboUser.Value & "'") Then
            strUser = Me.cboUser.Value 'Set the value of strUser declared as Global Variable
            strLevel = DLookup("Level", "tblUsers", "[UserName]='" & Me.cboUser.Value & "'") 'set the value of strLevel declared as Global Variable
            DoCmd.Close acForm, "frmLogin", acSaveNo
            MsgBox "The login was successful! Welcome Back, " & strUser, vbOKOnly, "Welcome"
            DoCmd.OpenForm "frmMenu", acNormal, "", "", , acNormal
            
        Else
            MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Password"
            LogAttempt = LogAttempt + 1
            txtPassword.SetFocus
        End If
    
    End If
    
    'Check if the user has 3 wrong log-in attempts and close the application
    If LogAttempt = 3 Then
        MsgBox "Your account has been locked due to failed login, please contact an admin." & vbCrLf & vbCrLf & _
        "Application will quit.", vbCritical, "Restricted Access!"
        Application.Quit
               
    End If
    
ErrorHandler:

End Sub

Many thanks in advance.

PS. My coding knowledge is all self taught so I probably picked up bad habits, or I may lack certain knowledge, if this is the case, please kindly correct me rather than getting irritated, its much more constructive and helpful for me. Thank you.

 

[jdraw]

See if this helps INSERT INTO to add a record to table

http://www.techonthenet.com/sql/insert.php

 

[LukeD1994]

Thanks, but unfortunately it doesn't work.

I should of mentioned the database is solely Access, not SQL.
I cannot get this to work no matter how I arrange it.
I'm not trying to update or insert a new record by the way.

My table has 3 users (at the moment) I don't want the login box to add a new user which is what that insert method is showing.
I want it to read the username who is attempting to login, and then update THEIR field, and not any other.

 

[jdraw]

Luke,
You may want to research Normalization.
http://xa.yimg.com/kq/groups/20697041/1514891951/name/UnderstandingNormalization.pdf

For clarity for the readers, can you describe how you added the 3 users? And how and where you record "login attempts"?

My table has 3 users (at the moment) I don't want the login box to add a new user which is what that insert method is showing.

What are you going to do when/if you get a new user?
Perhaps you could tell us more about your login approach step by step so we all understand.

 

[LukeD1994]

Sure.

I have the table "tblUsers" which is viewable by the admin account.
This table can be edited straight up (so if I needed to add a new user I would open it, type in their desired username and password, set their rank and save it.
I do intend to create a "Create user" form which will allow a person to add themselves to the database, but limit their rank to "guest" until I change it. (guest = default value in the table)

All log in attempts in the field "LogAttempts" are default to 0 (no failed logins yet).

So the process is as follows:

1. User opens and enters the database password (the encryption option)
2. If done right, login form appears by default. User enters username and password and hits "login"
3. At this point the code check the values of "cboUser" and "txtPassword" (used to be a combo box for user, hence its name, is a text box now) and if they match the tblUsers, then it grants them access.
4. If the values do not match, then it tells them using a msgbox "incorrect password".

Prior to my new method attempts, I had a counter, setting a private integer which was LogAttempts = LogAttempts +1
This would then check to see if LogAttempts = 3. And would show an error message ("failed 3 times etc."), closing the application after clicking ok.

I NOW want it instead of being a private integer, to store this value "LogAttempts" into the field in tblUsers. Which would prevent the user from closing, reloading and leaving the integer to be magically reset on start-up otherwise they practically have infinite attempts.
If the code looks up the field and see's the user has 3 in "LogAttempts" it prevents the login straight away. (until I reset it)

Is there any easier way to do this perhaps? I'm thinking my method is becoming complicated.

EDIT: I should also add, that once this is done I intend to create an admin form which will display things such as locked users, password recovery requests, account level upgrade ect. rather than using the table.

 

[LukeD1994]

Sorry for the double post but this is more of an update on my situation rather than my requirements like the post above ^^^

Ok so I managed to get a system working, with it storing data to a table using a hidden text box and control source.
Where every failed login was writing +1 to the textbox, which was causing the table to add +1 too. (this is what I want)

However I can't get it to write the +1 for the user. Instead it is writing the +1 for all users, just putting it in the top user on the table.

 

[boblarson]

However I can't get it to write the +1 for the user. Instead it is writing the +1 for all users, just putting it in the top user on the table.

How are you modifying or appending to this table?

 

[LukeD1994]

How are you modifying or appending to this table?

The hidden text box on the log in form. Its control source is the Field in the table. So if the text box receives information then it puts it in the table.
But its putting the information in the first empty space in the field in that table, rather than for the specific user.

Me.txtLogAttempt.Value = txtLogAttempt.Value + 1

This line I feel needs to change so that it points to the user logging in, but I don't know how lol.

 

[boblarson]

What is the record source of that form? It sounds like you haven't used a query which narrows it down to the person logging in.

 

[LukeD1994]

What is the record source of that form? It sounds like you haven't used a query which narrows it down to the person logging in.

I haven't used a query you are quite right. To be honest with you I wouldn't know how to go about integrating one for this. :/
The form just uses DLookup to pull the data from the tblUsers Table

 

[boblarson]

I haven't used a query you are quite right. To be honest with you I wouldn't know how to go about integrating one for this. :/
The form just uses DLookup to pull the data from the tblUsers Table

That would be your problem. You are adding one to the first record that Access pulls. So, the better way would be to append entries in the table like

Dim strSQL As String
 
strSQL = "INSERT INTO TableNameHere ([EmployeeIDFieldName], [LogInAttempt]) VALUES (" & Me.EmployeeID & ", 1)"
CurrentDb.Execute strSQL, dbFailOnError

And then you would get the count from the table for the employee

If DCount("*", "TableNameHere", "[EmployeeID]=" & Me.EmployeeID) >=3 Then
   ' don't let in 
End If

 

[LukeD1994]

Ok tried that:

        'Compare value of txtPassword with the saved Password in tblUser
        If Me.txtPassword.Value = DLookup("Password", "tblUsers", "[UserName]='" & Me.cboUser.Value & "'") Then
            strUser = Me.cboUser.Value 'Set the value of strUser declared as Global Variable
            strLevel = DLookup("Level", "tblUsers", "[UserName]='" & Me.cboUser.Value & "'") 'set the value of strLevel declared as Global Variable
            DoCmd.Close acForm, "frmLogin", acSaveNo
            MsgBox "The login was successful! Welcome Back, " & strUser, vbOKOnly, "Welcome"
            DoCmd.OpenForm "frmMenu", acNormal, "", "", , acNormal
            
        Else
            MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Password"
            Dim strSQL As String
 
            strSQL = "INSERT INTO tblUsers ([UserName], [intLogAttempt]) VALUES (" & Me.cboUser & ", 1)"
            CurrentDb.Execute strSQL, dbFailOnError
            txtPassword.SetFocus
        End If
    
    End If
    
    'Check if the user has 3 wrong log-in attempts and close the application
    If DCount("*", "tblUsers", "[UserName]=" & Me.cboUser) >= 3 Then
        MsgBox "Your account has been locked for failed log in. Please contact admin." & vbCrLf & vbCrLf & _
        "Application will exit.", vbCritical, "Restricted Access!"
        Application.Quit
    End If

Isn't even registering the value now. I've gone back to it just letting me spam the login button, it won't even hit the "3 wrong attempts" section.

Sorry for being a noob, but you will have to talk me through this :/

 

[boblarson]

Because you are using username instead of ID, I assume it is text. So you would have to modify the query to add quotes.

strSQL = "INSERT INTO tblUsers ([UserName], [intLogAttempt]) VALUES (" & Chr(34) & Me.cboUser & Chr(34) & ", 1)"

Also, are you storing a record for EACH login attempt?

 

[LukeD1994]

Because you are using username instead of ID, I assume it is text. So you would have to modify the query to add quotes.

strSQL = "INSERT INTO tblUsers ([UserName], [intLogAttempt]) VALUES (" & Chr(34) & Me.cboUser & Chr(34) & ", 1)"

Also, are you storing a record for EACH login attempt?

Ok its writing to the table now.
But instead of adding +1 to the user logging in it adds a new user, with the same username, no rank or password. Just a username and LogAttempt 1.

What I want it to do is store the number of incorrect logins for the already registered user, not create a new one.

 

[boblarson]

What I want it to do is store the number of incorrect logins for the already registered user, not create a new one.

My suggestion is to let it create a record for each failed login attempt. It is quite easy to use what I gave you, including the DCount to get the number of failed attempts. You could also log the date/time just to have more info about it. But if you insist, just change the code to this:

[COLOR=darkgreen]' First check to see if the employee exists in that table and, if so, [/COLOR]
[COLOR=darkgreen]' update their record otherwise create one[/COLOR]
If  DCount("*", "tblUsers", "[UserName]=" & Chr(34) & Me.cboUser & Chr(34)) = 0 Then
[COLOR=darkgreen]' sets the string to an update query[/COLOR]
   strSQL = "UPDATE tblUsers SET [intLogAttempt]=[intLogAttempt]+1 WHERE [UserName]=" & Chr(34) & Me.cboUser & Chr(34)
Else
[COLOR=darkgreen]' sets the string to be an append query[/COLOR]
   strSQL = "INSERT INTO tblUsers ([UserName], [intLogAttempt]) VALUES (" & [COLOR=red][COLOR=black]Chr(34) &[/COLOR][/COLOR]Me.cboUser [COLOR=black]& Chr(34)[/COLOR] & ", 1)"
End If
 If strSQL <> vbNullString Then
    CurrentDb.Execute strSQL, dbFailOnError
End If

 

[LukeD1994]

Perfect! Thank you very much

I don't want it to create a new record mind, I will handle that at a later date because I'm building an admin control panel later to deal with managing accounts