I am developing a database using Microsoft Access 2003. Within this database there is a login form which works perfectly fine.
What I am trying to do is to write an IF statement so that if a user enters their password wrong three times then they're account is suspended. In the Users table where the username and password are stored, there is a field called Account Suspended which is a Boolean data type.
I want a piece of code to check this box and freeze the login wizard if the password is entered incorrectly three times.
In the form's code declare a Private integer variable outside of any Sub, Function or Event - you can use it as a counter for the wrong logins and it will be accessible from within the form or form's code.
In the Form_Open event you can initialize it (e.g. set it to zero) and in the Click event of the Login button you increase this variable by 1 each time a wrong login happens. If it has reached the value of 3 you lock the user by updating the field in the table..
The only problem with doing it ehansi's way is that I would have an umlimited amount of attempts logging in, so long as I closed the application after getting it wrong twice and then went back into it.
I would have a field within your username table called Attempts with a default value of 0.
Each time a login attempt is made run 2 seperate updates to the records
The first adds 1 to the attempt number
The second changes the status of active to false where attempts is greater than 3
Dim SqlStr1 As String
Dim SqlStr2 As String
SqlStr1 = "UPDATE Table1 SET Table1.Attempts = [Table1]![Attempts]+1 WHERE ((([Table1]![Username])=[Forms]![Form1]![Text01]));"
SqlStr2 = "UPDATE Table1 SET Table1.Active = False WHERE (((Table1.Attempts)>3) AND (([Table1]![Username])=[Forms]![Form1]![Text01]));"
Once the user successfully logs in, have an update to reset the Attempts to 0
Dim SqlStr As String
SqlStr = "UPDATE Table1 SET Table1.Attempts = 0 WHERE ((([Table1]![Username])=[Forms]![Form1]![Text01]));"