Force database close upon failed logon x3

[brharrii]

I'm setting up a login for my database. At this point I have succesfully implimented code to request a user name and password, validate them from a table and then open a switchboard to access the database.

I want to add a second part onto this that forces the database to close after 3 unsuccesful login attempts and displays a message.

This is the code I am using:

    intlogonattempts = intlogonattempts + 1
    If intlogonattempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
End Sub

The dialog box never shows up and the database never closes no matter how many times I use the wrong credentials to log on.

Thanks for your help!

Bruce

 

[brharrii]

This is the full code if its needed:

Private Sub command5_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(Me.Combo9) Or Me.Combo9 = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.Combo9.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.Text2) Or Me.Text2 = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.Text2.SetFocus
Exit Sub
End If
'Check value of password in tblEmployees to see if this
'matches value chosen in combo box
If Me.Text2.Value = DLookup("password", "editors", _
"[ID]=" & Me.Combo9.Value) Then
id = Me.Combo9.Value
'Close logon form and open splash screen
DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "Product identification form"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.Text2.SetFocus
End If
'If User Enters incorrect password 3 times database will shutdown
intlogonattempts = intlogonattempts + 1
If intlogonattempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub

 

[VilaRestal]

You would want to put that code in the failed password attempts:

Private Sub command5_Click()
    'Check to see if data is entered into the UserName combo box
    If Nz(Me.Combo9,"") = "" Then
        MsgBox "You must enter a User Name.", , "Required Data"
        Me.Combo9.SetFocus
        Exit Sub
    End If
    'Check to see if data is entered into the password box
    If Nz(Me.Text2,"") = "" Then
        MsgBox "You must enter a Password.", , "Required Data"
        Me.Text2.SetFocus
        Exit Sub
    End If
    'Check value of password in tblEmployees to see if this matches value chosen in combo box
    If Me.Text2 = DLookup("password", "editors", "[ID]=" & Me.Combo9) Then
        id = Me.Combo9
        'Close logon form and open splash screen
        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "Product identification form"
    ElseIf intlogonattempts > 3 Then
        MsgBox "You do not have access to this database.Please contact admin.", vbCritical, "Restricted Access!"
        Application.Quit
    Else
        MsgBox "Password Invalid. Please Try Again", , "Invalid Entry!"
        Me.Text2.SetFocus
        intlogonattempts = intlogonattempts + 1
    End If
End Sub

If intlogonattempts is declared at the form's module level then that should work.

Also, if you haven't already, change Option Compare Database to Option Compare Binary at the start of the Form's module to make the password comparison case sensitive.

 

[brharrii]

Thanks VilaRestal -

I'm a little bit new to the modules and VBA in access, so some concepts still elude me.

What would i do to declare intlogonattempts in the form's module?

Thanks again!

 

[brharrii]

Also thanks for the binary tip, I will do that now!

 

[VilaRestal]

In the form's module, at the top, just after where it now says

Option Compare Binary
Option Explicit 'Always have this

before any subs or functions, put

Private intlogonattempts As Integer

That way it will retain its value between clicks (as long as the form remains open).

What you had before, without Option Explicit I presume, when access encountered that variable undeclared it will immediately declare it for you as a variable with scope only within the sub. As soon as the sub ends it loses its value. That's partly why Option Explicit is important, it forces you to be specific about what a variable is and what its scope is and means typos will be reported by the compiler as an error rather than declared as new variables.

Likewise, I trust the id variable is declared Public in a module. Otherwise it won't retain its value when the logon form closes.

 

[brharrii]

hmmm, I'm still missing something for some reason. Everything seems to work with the exception of closing the database after the 3rd unsuccessful login attempt.

I have this code on the form:

Private Sub command5_Click()
'Check to see if data is entered into the UserName combo box
If Nz(Me.Combo9,"") = "" Then
MsgBox "You must enter a User Name.", , "Required Data"
Me.Combo9.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If Nz(Me.Text2,"") = "" Then
MsgBox "You must enter a Password.", , "Required Data"
Me.Text2.SetFocus
Exit Sub
End If
'Check value of password in tblEmployees to see if this matches value chosen in combo box
If Me.Text2 = DLookup("password", "editors", "[ID]=" & Me.Combo9) Then
id = Me.Combo9
'Close logon form and open splash screen
DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "Product identification form"
ElseIf intlogonattempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", vbCritical, "Restricted Access!"
Application.Quit
Else
MsgBox "Password Invalid. Please Try Again", , "Invalid Entry!"
Me.Text2.SetFocus
intlogonattempts = intlogonattempts + 1
End If
End Sub

and my module looks like this:

Option Compare Binary
Option Explicit
Public lngMyEmpID As Long

 

[brharrii]

Rather the module looks like this:

Option Compare Binary
Option Explicit
Private intlogonattempts As Integer

 

[AccessRube]

Are you putting something else in that will lock them out until an administrator allows access? What is to prevent them from simply opening the application again and trying three more times, and again and again, etc.?

 

[VilaRestal]

Move that code to the top of the form's module. (What you call "code on the form" is in the form's module.)

 

[brharrii]

ok, I'm having some undeclared variable issues, I'm going to have to go back over and see what I did wrong. thank you for your help, I'll post back in a bit if I run into another road block!

 

[brharrii]

AccessRube - Right now I don't have any additional security features implemented. I will eventually be adding to what I have, but I'm still pretty new to this and just figuring out the user name and password thing has kept me pretty busy. Thank you for the suggestion though, Maybe I'll hit you up when I get closer to th at step

 

[brharrii]

Ok, so I made some changes, and sort of fixed the problem. The only thing I dont understand is The error message pops up and the database closes, but after 5 attempts instead of after 3. it's not the end of the world, but it would be nice to understand why its doing that.

This is what I have for the forms module:

Option Compare Binary
Option Explicit
Public lngMyEmpID As Long
Private intlogonattempts As Integer

Private Sub Combo9_AfterUpdate()
'After selecting user name set focus to password field
Me.Text2.SetFocus
End Sub


Private Sub command5_Click()
'Check to see if data is entered into the UserName combo box
If Nz(Me.Combo9, "") = "" Then
MsgBox "You must enter a User Name.", , "Required Data"
Me.Combo9.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If Nz(Me.Text2, "") = "" Then
MsgBox "You must enter a Password.", , "Required Data"
Me.Text2.SetFocus
Exit Sub
End If
'Check value of password in tblEmployees to see if this matches value chosen in combo box
If Me.Text2 = DLookup("password", "editors", "[ID]=" & Me.Combo9) Then
lngMyEmpID = Me.Combo9
'Close logon form and open splash screen
DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "Product identification form"
intlogonattempts = intlogonattempts + 1
ElseIf intlogonattempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", vbCritical, "Restricted Access!"
Application.Quit
Else
MsgBox "Password Invalid. Please Try Again", , "Invalid Entry!"
Me.Text2.SetFocus
intlogonattempts = intlogonattempts + 1
End If
End Sub

 

[brharrii]

I added:

Private intlogonattempts As Integer

because it got angry with me because Intlogonattempts was not declared.

I also changed:

If Me.Text2 = DLookup("password", "editors", "[ID]=" & Me.Combo9) Then
ID = Me.Combo9

to

If Me.Text2 = DLookup("password", "editors", "[ID]=" & Me.Combo9) Then
lngMyEmpID = Me.Combo9

Because it threw an error that "ID" was not valid. I'm sorry I didn't keep the exact error message.

 

[VilaRestal]

That's what Option Explicit does and why it's important to do it. The error told you those variables weren't declared.

Now create a new module and move Public lngMyEmpID As Long to that. That way lngMyEmpID will retain its value after the form closes.

The fifth attempt sounds about right.

1st attempt - intlogonattempts = 0
2nd attempt - intlogonattempts = 1
3rd attempt - intlogonattempts = 2
4th attempt - intlogonattempts = 3
5th attempt - intlogonattempts = 4 (> 3 so quits)

just reduce > 3 to > 1
(actually it would be a bit better to make it a constant at the start of the form's module that can be easily changed there rather than having to delve into the code to find it)

And AccessRube is right. There's nothing to stop the person opening the database again and trying another 3 or 5 times. And again, and again. Perhaps you should think about the options for preventing that (storing the failed attempt count for a particular computer in a table and reading that when the form loads). You're also going to need to disable shift bypass or all this 'security' is nothing of the sort. And likewise, until the database is compiled anyone can change the code to remove the security once they get into it.

 

[AccessRube]

AccessRube - Right now I don't have any additional security features implemented. I will eventually be adding to what I have, but I'm still pretty new to this and just figuring out the user name and password thing has kept me pretty busy. Thank you for the suggestion though, Maybe I'll hit you up when I get closer to th at step

I wish I could help you. I just recently put a login form into my database. I set the close button to no, hid the navigation on the front end, disabled the shift open, and set the shortcut menu to no, so they can't just right click the form and go into design view. I think I have it as secure as I can while I search for a way to do exactly what you're describing. If you have any luck, let ME know

 

[VilaRestal]

Both of you:

You can use Environ("COMPUTERNAME") to get the name of the current computer.

You could create a table that stores failed attempts to logon with datetime and name of workstation (a good record to have anyway) and may as well include the attempted username too and maybe even the windows username (Environ("USERNAME")).

You could then, when the logon form loads, check that table for failed attempts that day from that computer and quit (with msgbox) if it is too many.

 

[brharrii]

I set the close button to no, hid the navigation on the front end, disabled the shift open, and set the shortcut menu to no, so they can't just right click the form and go into design view.

I have yet to figure these things out.... sigh....

 

[AccessRube]

I have yet to figure these things out.... sigh....

On the login form's properties, set Close Button to "No", this will ghost out the red x in the right corner, thus preventing them from just closing your login form and accessing the database.

Set the Shortcut Menu to "No", this will prevent them from right clicking on your login form and opening it in design view, where they could change anything they want.

To hide the navigation bar, go to options > Current Database > and uncheck the Display Navigation Pane