Do while loop in VBA for a login screen - Access

[g.mcavoy123]

I have been trying to create a login screen that allows the user 3 password attempts, then it will close the database. However my loop does not work correctly and the value for the users attempts does not seem to change. Please Could someone help.

Code:
Option Compare Database
Public intLogonAttempts As Long



Private Sub Command1_Click()
intLogonAttempts = 0

Do While intLogonAttemptDone <= 3

Loop


If IsNull(Me.txtLoginID) Then
MsgBox "Please enter LoginID", vbInformation, "LoginID required"
Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter password", vbInformation, "Password required"
Me.txtPassword.SetFocus
Else
'process the job


If (IsNull(DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID.Value & "'"))) Or _
(IsNull(DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword.Value & "'"))) Then
Me.txtPassword = ""

intLogonAttemptDone = intLogonAttempts + 1
MsgBox "Incorrect LoginID or Password" & intLogonAttempts


Else
MsgBox "LoginID and password correct"
DoCmd.Close acForm, "frmLogin", acSaveNo
DoCmd.OpenForm "frmMenu"




End If

 

[GinaWhipp]

Welcome to the Forum!


Looks like you modified the code which is why it is misbehaving. Change the field names in the below to match yours and you should be fine...

     Dim intLogonAttempts As Long
    'Verify Username
    
    'Check to see if data is entered into the UserName combo box
    If IsNull(Me.cboAssociateID) Or Me.cboAssociateID = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboAssociateID.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 = DLookup("apPassword", "tblAssociateProfile", "apAssociateID =" & Me.cboAssociateID) Then
        DoCmd.Close acForm, "frmLogOn"
        DoCmd.OpenForm "frmMainSwitchBoard"
    Else
        MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword = Null
        Me.cboAssociateID.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

 

[g.mcavoy123]

This is what I've got it says the passwords value is 1? any help. Thank you for the welcome!

Code:
Private Sub Command1_Click()
intLogonAttempts = 0




If IsNull(Me.txtLoginID) Then
MsgBox "Please enter LoginID", vbInformation, "LoginID required"
Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter password", vbInformation, "Password required"
Me.txtPassword.SetFocus
Else
'process the job
End If


If Me.txtPassword = DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword) Then
Me.txtLoginID = DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID)

DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmMenu"



Else
MsgBox "LoginID and password incorrect"


End If





End Sub

 

[pbaldy]

This line needs to be:

If Me.txtPassword = DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword & "'") Then

This one

Me.txtLoginID = DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID)

makes no sense. You're looking up a value you already have ("look up the surname where the surname equals whatever"). In any case, it would also need the quotes at the end presuming the field is text.

 

[GinaWhipp]

In the code section above you have left out the bottom portion which counts how many attempts have been made. Was there a reason for that? Because what you have posted will not close the database...

 

[Solo712]

If Me.txtPassword = DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword) Then
Me.txtLoginID = DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID)

Should be:

If Nz(DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword) & "'")) = ""  Or _
 Nz(DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID & "'")) = ""  Then ....
    ' logon failed .... handle it 
Else 
   DoCmd.Close acForm, "frmLogin"
   DoCmd.OpenForm "frmMenu"
End If

Best,
Jiri

 

[g.mcavoy123]

I am very confused.
I need to program the check whether the username and password combination entered is correct: IF not add 1 onto intLogonAttempts

Loop until count = 3, closing the database

OR

the user has entered the username and password correctly, opening the database

The main problem with my original code is that it would not increment.

Thanks for any help that has been given but I am ultimately confused

 

[g.mcavoy123]

Code:
Private Sub Command1_Click()
intLogonAttempts = 0


If IsNull(Me.txtLoginID) Then
MsgBox "Please enter LoginID", vbInformation, "LoginID required"
Me.txtLoginID.SetFocus

ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter password", vbInformation, "Password required"
Me.txtPassword.SetFocus

Else
'process the job
End If

If Me.txtPassword = DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword) HOW TO DO AND FUNCTION>
Me.txtLoginID = DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID)
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmMenu"

Else
MsgBox "LoginID and password incorrect"
intLogonAttempts = intLogonAttempts + 1
End If
If intLogonAttempts >= 3 Then
DoCmd.Quit acQuitPrompt



End Sub

 

[Cronk]

For a start, your code will not compile because it has a missing endif statement just before the End Sub

Where is the loop where you allow the user to try multiple times?

In any case, every time your Command1_click code executes, it sets the number of attempts as 0 so remove this line.

Add the line
Option Explicit
as the first line in the module for your form and define the variable ie
Dim intLogonAttempts as integer

Put a breakpoint on the first line of Sub Command1_click and then follow the code executing line by line to sort out your logic.

 

[g.mcavoy123]

I have altered my code to your guys advice, the part in red still does not work correctly. I have deleted the Command_Click1 to ensure the intLogonAttempts is not equal to 0 every run.

Code:


intLogonAttempts = 0

If IsNull(Me.txtLoginID) Then
MsgBox "Please enter LoginID", vbInformation, "LoginID required"
Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter password", vbInformation, "Password required"
Me.txtPassword.SetFocus
Else
'process the job
End If
If Nz(DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword) & "'")) = "" Or
Nz(DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID & "'")) = "" then
MsgBox "LoginID and password incorrect"
intLogonAttempts = intLogonAttempts + 1
Else
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmMenu"
End If
If intLogonAttempts >= 3 Then
DoCmd.Quit acQuitPrompt
End If

End Sub

 

[GinaWhipp]

There is no part in RED and please use Code Tags (the pound button), it's easier for us to read.

 

[g.mcavoy123]

I have altered my code to your guys advice, the part surrounded by code tages still does not work correctly. I have deleted the Command_Click1 to ensure the intLogonAttempts is not equal to 0 every run.

Code:


intLogonAttempts = 0

If IsNull(Me.txtLoginID) Then
MsgBox "Please enter LoginID", vbInformation, "LoginID required"
Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter password", vbInformation, "Password required"
Me.txtPassword.SetFocus
Else
'process the job
End If
£
If Nz(DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword) & "'")) = "" Or
Nz(DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID & "'")) = "" then
MsgBox "LoginID and password incorrect"
intLogonAttempts = intLogonAttempts + 1
£
Else
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmMenu"
End If
If intLogonAttempts >= 3 Then
DoCmd.Quit acQuitPrompt
End If

End Sub

 

[GinaWhipp]

No code tags and what does that *End Sub* belong to? Oh, and do the names in the DLookup() match your table names?

 

[g.mcavoy123]

I am so confused! the end sub belonged to the sub Click_command1 that someone told me to remove. The code tags are pound signs no?

Code:


intLogonAttempts = 0

If IsNull(Me.txtLoginID) Then
MsgBox "Please enter LoginID", vbInformation, "LoginID required"
Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter password", vbInformation, "Password required"
Me.txtPassword.SetFocus
Else
'process the job
End If

THIS DOES NOT WORK BELOW:

If Nz(DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword) & "'")) = "" Or
Nz(DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID & "'")) = "" then
MsgBox "LoginID and password incorrect"
intLogonAttempts = intLogonAttempts + 1

THIS DOES NOT WORK ABOVE^^^^^^


Else
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmMenu"
End If
If intLogonAttempts >= 3 Then
DoCmd.Quit acQuitPrompt
End If

End Sub

 

[gemma-the-husky]

I would possibly simplify the code for this with a goto. It's easy to understand a goto
(although a while loop is fairly similar)
another idea that helps is to just build a framework, and then use functions to implement the framework

so in the example below you need (in bold within the code sample):
a function to get the password
a function to test the password
a function to handle failure

if these are not contained within the process it becomes far more readable.

     attempt = 1
  
 tryagain:
     [B]getpassword[/B]
     if [B]passwordcorrect[/B] then goto continue
     increment attempt
     if attempt > 3 then
         'too many times
          [B]process to deal with failure[/B]
         exit sub
     end if
     goto  tryagain  
  
 continue:
      password succeeded - 
      carry on as normal

 

[g.mcavoy123]

Yes I do understand the logic of what I am doing, I could easily do this in Java (what I know to code in) but every time I seem to do something it doesn't seem to want to work. Would you suggest restarting?

 

[g.mcavoy123]

I have edited the code to try and match the first code as best as I can, however it doesn't seem to work again.

[CODE]

Dim intLogonAttempts As Long
    
    'Check value of password in tblAssociateProfile to see if this matches value chosen in combo box
    If Me.txtPassword = DLookup("Password", "tblUsers", "Password =" & Me.txtPassword) Then
        DoCmd.Close acForm, "frmSplashScreen"
        DoCmd.OpenForm "frmMenu"
    Else
        MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword = Null
        Me.cboAssociateID.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

[/CODE]

 

[Solo712]

I am so confused! the end sub belonged to the sub Click_command1 that someone told me to remove. The code tags are pound signs no?

THIS DOES NOT WORK BELOW:

If Nz(DLookup("Password", "tblUsers", "Password ='" & Me.txtPassword) & "'")) = "" Or
Nz(DLookup("Surname", "tblUsers", "Surname ='" & Me.txtLoginID & "'")) = "" then
MsgBox "LoginID and password incorrect"
intLogonAttempts = intLogonAttempts + 1

THIS DOES NOT WORK ABOVE^^^^^^

The test looks really cockeyed when I look at it now. You want to match the user name to password, right ? So a single test should be sufficient:

If Nz(DLookup("Surname", "tblUsers", "Password ='" & Me.txtPassword) & "'")) <> Me.txtLoginID Then
  MsgBox "LoginID and/or password incorrect"

Hope this works !

Best,
Jiri

 

[g.mcavoy123]

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

Code:


intLogonAttempts = 0

If IsNull(Me.txtLoginID) Then
MsgBox "Please enter LoginID", vbInformation, "LoginID required"
Me.txtLoginID.SetFocus

ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter password", vbInformation, "Password required"
Me.txtPassword.SetFocus

Else
'process the job
End If

If Nz(DLookup("Surname", "tblUsers", "Password ='" & Me.txtPassword) & "'")) <> Me.txtLoginID Then
    MsgBox "LoginID and password incorrect"
    intLogonAttempts = intLogonAttempts + 1

Else
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmMenu"

End If

If intLogonAttempts >= 3 Then
DoCmd.Quit acQuitPrompt
End If

End Sub

 

[g.mcavoy123]

Basically, I want the username to match the surname in a table, the password should match the user name in this table. If the user enter the password wrong 3 times the database will close. That is all I wish to do.