Do while loop in VBA for a login screen - Access

[Solo712]

The code still appears in red and says there is "Compile error. Expected: go to or then". The error appears in the "If Nz(DLookup("Surname", "tblUsers", "Password ='" & Me.txtPassword) & "'")) <> Me.txtLoginID Then

Try exclamation marks:

"If Nz(DLookup("Surname", "tblUsers", "Password ='" & Me[COLOR=red]![/COLOR]txtPassword) & "'")) <> Me[COLOR=red]![/COLOR]txtLoginID Then

Jiri

 

[g.mcavoy123]

The exclamation marks did not appear to do anything, the code did not work and the same message came up.

 

[Cronk]

A simple count of left and right parentheses might show there are 2 left and 3 right. That would cause a syntax error.

Remove the bracket shown in red

If Nz(DLookup("Surname", "tblUsers", "Password ='" & Me!txtPassword[B][COLOR=Red]) [/COLOR][/B]& "'")) <> Me!txtLoginID Then

Incidentally, I would prefer if you would post the whole segment of code. That is, if I see an End Sub, I'd like to see the first statement ie Sub xyz

There is also logic inconsistency. You have an if statement to test if Me.txtLoginID is null, then after that you have the DLookup which could have a null value. I would have expected an exit from the subroutine if nothing was entered.

 

[g.mcavoy123]

I have update my code but it still doesn't appear to work, I have matched this again a code that does work and cannot see the error? Could anyone help, thanks.

Private Sub Command1_Click()
Dim intLogonAttempts As Long
    'Verify Username
    
    'Check to see if data is entered into the UserName combo box
    If IsNull(Me.txtLoginID) Or Me.txtLoginID = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.txtLoginID.SetFocus
        Exit Sub
    End If
     'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    
    'Check value of password in tblAssociateProfile to see if this matches value chosen in combo box
    If Me.txtPassword.Value = DLookup("Password", "tblUsers", "[Surname]=" & Me.txtLoginID.Value) Then
        DoCmd.Close acForm, "frmSplashScreen"
        DoCmd.OpenForm "frmMenu"
    Else
        MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword = ""
        Me.txtLoginID.SetFocus
        Me.txtPassword.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

 

[pbaldy]

Define "doesn't appear to work". For starters, since intLogonAttempts is declared within the sub, it resets to 0 every time the button is clicked. You want it declared before any subs, at the top of the form module.

 

[g.mcavoy123]

I get an error saying the expression you entered as query gave this result, the result is the username entered into the login box. Yellow text appears around the D-look up

 

[pbaldy]

Surname being text, you need the delimiters around it...like you had before.

 

[g.mcavoy123]

I'm sorry I don't understand??

 

[GinaWhipp]

Umm, are Surname and txtLoginID the same? Because Surname implies TEXT while LoginID implies numeric.

 

[pbaldy]

DLookup("Password", "tblUsers", "[Surname]='" & Me.txtLoginID.Value & "'")

 

[g.mcavoy123]

Thank you, now my database will accept the username and password combo. Now onto making it allow 3 attempts before closing the database, where exactly does the int LogonAttempts have to go? Above the "Private Sub Command1_Click()"

LogID = Surname. Stupid names for text boxes I know!!

Private Sub Command1_Click()
Dim intLogonAttempts As Long
    'Verify Username
    
    'Check to see if data is entered into the UserName combo box
    If IsNull(Me.txtLoginID) Or Me.txtLoginID = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.txtLoginID.SetFocus
        Exit Sub
    End If
     'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    
    'Check value of password in tblAssociateProfile to see if this matches value chosen in combo box
    If Me.txtPassword.Value = DLookup("Password", "tblUsers", "[Surname]='" & Me.txtLoginID.Value & "'") Then
        DoCmd.Close acForm, "frmLogin"
        DoCmd.OpenForm "frmMenu"
    Else
        MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword = ""
        Me.txtLoginID.SetFocus
        Me.txtPassword.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

 

[g.mcavoy123]

It works, thank you so much to everyone who helped on this journey. I wish you all the best for the new year

 

[pbaldy]

Was this not clear? "You want it declared before any subs, at the top of the form module.".

Like:

Option Compare Database
Option Explicit

Dim intLogonAttempts As Long

Private Sub Command1_Click()
...