Login Form (1 Viewer)

[mba_110]

Hi,

I am struct with following code for my login form i have mentioned each code requirement but its not working.

Private Sub BtnLoginOK_Click()
Dim vpwd As Variant


On Error GoTo ErrorHandler:

'Check if username is entered.

If IsNull(txtUsername) Or Me.txtUsername = "" Then
        MsgBox "Username is required", vbOKOnly, "Invalid Entry!"
        txtUsername.SetFocus
  Exit Sub

'Check if Password is entered.
      
 If IsNull(txtPassword) Or Me.txtPassword = "" Then
        MsgBox "Password is required", vbOKOnly, "Invalid Entry!"
        txtPassword.SetFocus
    End If
    Exit Sub



'Check case sensivity of entered username and password is correct 'z123VF&@'.

If DCount("LoginID", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0) > 0 Then
    vpwd = DLookup("strPassword", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0")
    
If StrComp(Me.txtPassword.Value, vpwd & vbNullString, vbBinaryCompare) <> 0 Then
      MsgBox "Invalid Login ID or Password"
   End If


'Check if user's 3  attempts are wrong then close the database with message.

If intLogAttempt = 3 Then
            MsgBox "You do not have access to this database. Please contact Database Administrator." & vbCrLf & vbCrLf & _
            "Application will exit.", vbCritical, "Restricted Access!"
            Application.Quit
  End If


'Check if Login (Userlevel) is "Employee" then go to Data entry menu with User Access Level (by default button is disabled on frmDataEntry_Navigation).

If (Me.txtSecurityLevel) = "Employee" Then
       DoCmd.OpenForm "frmDataEntry_Navigation"
  End If

'Check if Login (Userlevel) is "Admin" then full control all buttons & options ON (on frmDataEntry_Navigation i have enabled button based on "Admin").

If (Me.txtSecurityLevel) = "Admin" Then
       DoCmd.OpenForm "frmDataEntry_Navigation"
  End If


'Check if Login (UserLevel) is "User" then go to Individual record access form.

If (Me.txtSecurityLevel) = "User" Then
        DoCmd.OpenForm "frmEmployeeNavigation"
  End If
            

ErrorHandler:


End Sub

Many thanks for your help.

 

[JHB]

Comment out the error handler, then tell us what is the error number and error description, because "not working" isn't very informative!

 

[Gasman]

Missing End Ifs?

Private Sub BtnLoginOK_Click()
    Dim vpwd As Variant


    On Error GoTo ErrorHandler:

    'Check if username is entered.

    If IsNull(txtUsername) Or Me.txtUsername = "" Then
        MsgBox "Username is required", vbOKOnly, "Invalid Entry!"
        txtUsername.SetFocus
        Exit Sub

        'Check if Password is entered.

        If IsNull(txtPassword) Or Me.txtPassword = "" Then
            MsgBox "Password is required", vbOKOnly, "Invalid Entry!"
            txtPassword.SetFocus
        End If
        Exit Sub



        'Check case sensivity of entered username and password is correct 'z123VF&@'.

        If DCount("LoginID", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0) > 0 Then
            vpwd = DLookup("strPassword", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0")

            If StrComp(Me.txtPassword.Value, vpwd & vbNullString, vbBinaryCompare) <> 0 Then
                MsgBox "Invalid Login ID or Password"
            End If


            'Check if user's 3  attempts are wrong then close the database with message.

            If intLogAttempt = 3 Then
                MsgBox "You do not have access to this database. Please contact Database Administrator." & vbCrLf & vbCrLf & _
                       "Application will exit.", vbCritical, "Restricted Access!"
                Application.Quit
            End If


            'Check if Login (Userlevel) is "Employee" then go to Data entry menu with User Access Level (by default button is disabled on frmDataEntry_Navigation).

            If (Me.txtSecurityLevel) = "Employee" Then
                DoCmd.OpenForm "frmDataEntry_Navigation"
            End If

            'Check if Login (Userlevel) is "Admin" then full control all buttons & options ON (on frmDataEntry_Navigation i have enabled button based on "Admin").

            If (Me.txtSecurityLevel) = "Admin" Then
                DoCmd.OpenForm "frmDataEntry_Navigation"
            End If


            'Check if Login (UserLevel) is "User" then go to Individual record access form.

            If (Me.txtSecurityLevel) = "User" Then
                DoCmd.OpenForm "frmEmployeeNavigation"
            End If


ErrorHandler:


        End Sub

 

[Dreamweaver]

Noticed this

If DCount("LoginID", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0) > 0 Then

 

[mba_110]

I have put all End if on place.

Major issue in 3 portion of case sensitivity code, i dont know what is wrong their.

Errors:
Syntax error
Expected: list separator or)

 

[Dreamweaver]

vpwd = DLookup("strPassword", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0")

strComp Is A call to a function from looks of if I don't think it will work as part of a string but might be wrong

 

[mba_110]

Problem in this line.

If DCount("LoginID", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0) > 0 Then

 

[Dreamweaver]

If you get the compair to work I and a few other would be intereted in seeing how you did it as did have a go about 10 years back and gave up I do remember trying vbBinaryCompare At the time but might revisit it as am upgrading that project at the moment

 

[Gasman]

Problem in this line.

If DCount("LoginID", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0) > 0 Then

What is is meant to be doing?

 

[isladogs]

StrComp has 3 arguments: StrComp(string1, string2, compare) with the last argument being optional

The compare argument settings are:

[B]Constant     Value           Description [/B]
vbUseCompareOption     -1      Performs a comparison using the setting of the Option Compare statement. 
vbBinaryCompare    0    Performs a binary comparison. 
vbTextCompare   1    Performs a textual comparison. 
vbDatabaseCompare    2    Microsoft Access only. Performs a comparison based on information in your database.

If the compare argument is omitted it will use the default for that code module e.g. Option Compare Database

The StrComp function has the following return values:

[B]StrComp return values[/B] 
string1 is less than string2       -1 
string1 is equal to string2          0 
string1 is greater than string2    1 
string1 or string2 is Null           Null

So

StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0)

is doing a binary comparison of the two values and checking if these are the same

I would have expected a textual comparison in this scenario

StrComp(LoginID, '" & Me.txtUsername.Value & "', 1) = 0)

Now the next bit gets perhaps further confused

If DCount("LoginID", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0) > 0 Then

This checks if the count of Login values where the stored login matches the entered login value is greater than 0 then .... do the next thing!

But why make it so complicated? Why not just check the UserName where the loginID matches the entered value. Something like this:

strUserName=Nz(DLookup("UserName","tblUserSecurity","LoginID= '" & Me.txtUsername & "'"),"")
If strUserName<>"" Then

OR perhaps you could make use of this example database https://www.access-programmers.co.uk/forums/showpost.php?p=1598987&postcount=4

 

[mba_110]

Thanks for everybody for your help.


now the error is shifted to error handling label, i have following full code after you guys inputs, i did put semicolon on label place but not showing the desired result return with compile error "Label is not defined" even i put label its showing the same thing, is it causing from any part of below code?

Private Sub BtnLoginOK_Click()
Dim vpwd As Variant


On Error GoTo ErrorHandler:

'Check if username is entered.

If IsNull(txtUsername) Or Me.txtUsername = "" Then
        MsgBox "Username is required", vbOKOnly, "Invalid Entry!"
        txtUsername.SetFocus
 End If

'Check if Password is entered.
      
 If IsNull(txtPassword) Or Me.txtPassword = "" Then
        MsgBox "Password is required", vbOKOnly, "Invalid Entry!"
        txtPassword.SetFocus
    End If
  



'Check case sensivity of entered username and password is correct 'z123VF&@'.

strloginID = Nz(DLookup("LoginID", "tblUserSecurity", "LoginID= '" & Me.txtUsername & "'"), "")
If strUserName <> "" Then
    vpwd = DLookup("strPassword", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0")
    End If
    
    
If StrComp(Me.txtPassword.Value, vpwd & vbNullString, vbBinaryCompare) <> 0 Then
      MsgBox "Invalid Login ID or Password"
   End If

'Check if user's 3  attempts are wrong then close the database with message.

If intLogAttempt = 3 Then
            MsgBox "You do not have access to this database. Please contact Database Administrator." & vbCrLf & vbCrLf & _
            "Application will exit.", vbCritical, "Restricted Access!"
            Application.Quit
  End If


'Check if Login (Userlevel) is "Employee" then go to Data entry menu with User Access Level (by default button is disabled on frmDataEntry_Navigation).

If (Me.txtSecurityLevel) = "Employee" Then
       DoCmd.OpenForm "frmDataEntry_Navigation"
  End If

'Check if Login (Userlevel) is "Admin" then full control all buttons & options ON (on frmDataEntry_Navigation i have enabled button based on "Admin").

If (Me.txtSecurityLevel) = "Admin" Then
       DoCmd.OpenForm "frmDataEntry_Navigation"
  End If


'Check if Login (UserLevel) is "User" then go to Individual record access form.

If (Me.txtSecurityLevel) = "User" Then
        DoCmd.OpenForm "frmEmployeeNavigation"
  End If
            
exit_errhandlers:
Exit Sub
errhandlers:
    MsgBox Err.Description, vbCritical
    Err.Clear
    Exit Sub
End Sub

 

[isladogs]

You've scrapped the first strComp as I suggested but not the second

Try replacing

vpwd = DLookup("strPassword", "tblUserSecurity", "StrComp(LoginID, '" & Me.txtUsername.Value & "', 0) = 0")

with

vpwd = Nz(DLookup("strPassword", "tblUserSecurity", "LoginID='" & strLoginID & "'"),"")

The next part

If StrComp(Me.txtPassword.Value, vpwd & vbNullString, vbBinaryCompare) <> 0 Then

should I believe be vbTextCompare

Then you have

If intLogAttempt = 3 Then

Where is the code to loop through counting the login attempts?
As it appears to be missing, that will never be triggered

I would also suggest you simplify the 3 user level If...End If statements by using Select Case instead

Sorry - no idea what you're referring to regarding the

semicolon on label place

 

[mba_110]

Thank for everyone.

Some what i have reached the half way, the problem is in execution.

I have following issue in code.

1. When i login with correct username and password it's still not allowing me to enter in database and still showing "Invalid Login ID and Password"

2. Case sensitivity is working only for password and not for username.

3. if username is blank it is asking "Username is required", "Password is required" and "Username and password is incorrect" all messages one by one at same time, i want this event wise if username is blank than it should ask for username to enter and not password or any other thing and same for password it should see first username is mentioned than only ask for password to enter if its left blank and click on OK button.


Presently this is issues i have.

 

[GinaWhipp]

Only gave a quick read but noticed you are missing a critical line. Right before the End If's checking for User Name and Password you omitted the the...

Exit Sub

You must put that so it stops checking until that condition is met. So that section should read...

If IsNull(txtUsername) Or Me.txtUsername = "" Then
        MsgBox "Username is required", vbOKOnly, "Invalid Entry!"
        Me.txtUsername.SetFocus
Exit Sub
End If

The other thing I noticed is this code does not go on the On_Click of the button. It belongs in the After_Update event of the txtPassword control. However, the lines to open the Form can go in the On_Click event of the button. That way it will check for the correct entries before attempting to open the Form(s).

Can't help with the case sensitive part... too tired. Someone should pass by with why that's not working.

 

[isladogs]

There have been so many suggested changes that it would be helpful for you to post your latest code

 

[mba_110]

Here is my latest code.

the area of case sensitivity is still a issue and 3 attempts are also not working now before it was working i dont know what went wrong now.

Private Sub BtnLoginOK_Click()
Dim vpwd As Variant
Static Attempts As Integer
Dim SecurityLevel As String
Dim strLoginID As String



'Check if username is entered.

If IsNull(txtUsername) Or Me.txtUsername = "" Then
        MsgBox "Username is required", vbOKOnly, "Invalid Entry!"
        txtUsername.SetFocus
        Exit Sub
 End If

'Check if Password is entered.
      
 If IsNull(txtPassword) Or Me.txtPassword = "" Then
        MsgBox "Password is required", vbOKOnly, "Invalid Entry!"
        txtPassword.SetFocus
        Exit Sub
    End If
  


'Check case sensivity of entered username and password is correct 'z123VF&@'.

strLoginID = Nz(DLookup("LoginID", "tblUserSecurity", "LoginID= '" & Me.txtUsername & "'"), "")

    If LoginID <> "" Then
vpwd = Nz(DLookup("strPassword", "tblUserSecurity", "LoginID='" & LoginID & "'"), "")
    Exit Sub
    End If
    
    If StrComp(Me.txtPassword.Value, vpwd & vbNullString, vbBinaryCompare) <> 0 Then
    MsgBox "Login ID & Password are case sensative please make sure you enter correct information.", vbOKOnly, "Invalid Entry"
    
    Exit Sub
    End If
     
'Check if user's 3  attempts are wrong then close the database with message.


If Me.txtPassword.Value = DLookup("[strPassword]", "tblUserSecurity", "[LoginID]='" & Me.txtUsername.Value & "'") Then
   MsgBox "Welcome to Employee Management System"
   
 Else
  MsgBox "Username or password are incorrect!", vbCritical, "Warning"
  Attempts = Attempts + 1
   Me.txtUsername.SetFocus
    If Attempts = 3 Then
    MsgBox "You do not have access to EMS Database, Please contact system Administrator", vbOKOnly, "Invalid Entry!"
     DoCmd.Quit
     Exit Sub
     End If
 End If

'Check if Login (Userlevel) is "Admin" then full control all buttons & options ON (on frmDataEntry_Navigation i have enabled button based on "Admin").

If SecurityLevel = "Admin" Then
       DoCmd.OpenForm "frmDataEntry_Navigation"
       Exit Sub
  End If


'Check if Login (Userlevel) is "Employee" then go to Data entry menu with User Access Level (by default button is disabled on frmDataEntry_Navigation).

If SecurityLevel = "Employee" Then
       DoCmd.OpenForm "frmDataEntry_Navigation"
       Exit Sub
  End If


'Check if Login (UserLevel) is "User" then go to Individual record access form.

If SecurityLevel = "User" Then
        DoCmd.OpenForm "frmEmployeeNavigation"
        Exit Sub
  End If
            

End Sub

 

[isladogs]

I've made several changes and added error handling.
Changes in RED with some comments in GREEN

Its air code so will need checking. It it does fail, step through the code to identify where it fails

Private Sub BtnLoginOK_Click()

[COLOR="red"][B]On Error GoTo Err_Handler[/B][/COLOR]

Dim vpwd As [B][COLOR="Red"]String[/COLOR][/B] 
Static Attempts As Integer  [COLOR="Green"][B]'for info Static / Dim do the same thing[/B][/COLOR]
Dim SecurityLevel As String
Dim strLoginID As String

'Check if username is entered.
If [B][COLOR="red"]Nz(Me.txtUsername,"") = ""[/COLOR][/B] Then
        MsgBox "Username is required", vbOKOnly, "Invalid Entry!"
        Me.txtUsername.SetFocus
        Exit Sub
 End If

'Check if Password is entered.
 If [B][COLOR="red"]Nz(Me.txtPassword,"") = ""[/COLOR][/B] Then
        MsgBox "Password is required", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
  
'Check case sensitivity of entered username and password is correct 'z123VF&@'.

   strLoginID = Nz(DLookup("LoginID", "tblUserSecurity", "LoginID= '" & Me.txtUsername & "'"), "")

    If strLoginID <> "" Then
            vpwd = Nz(DLookup("strPassword", "tblUserSecurity", "LoginID='" & LoginID & "'"), "")
      [COLOR="Green"][B] '    Exit Sub 'This shouldn't be here[/B][/COLOR]
    End If
    
    If StrComp(Me.txtPassword.Value, vpwd & vbNullString, vbTextCompare) <> 0 Then
           [COLOR="green"][B]  'Suggest you just state 'Incorrect password ' here - don't guide the user[/B][/COLOR]
              MsgBox "Login ID & Password are case sensitive please make sure you enter correct information.", vbOKOnly, "Invalid Entry"
              Exit Sub
    End If
     
[B][COLOR="Red"]Attempts=0[/COLOR][/B] 'reset the count

'Check if user's 3  attempts are wrong then close the database with message.
If Me.txtPassword.Value = DLookup("[strPassword]", "tblUserSecurity", "[LoginID]='" & Me.txtUsername.Value & "'") Then
     MsgBox "Welcome to Employee Management System"
 Else
    [B][COLOR="Red"] Attempts = Attempts + 1
     Select Case Attempts
     
     Case 1
         MsgBox "Username or password is incorrect!" & vbCrLf & _
           "Please try again", vbCritical, "Warning"
         Me.txtUsername.SetFocus
     
     Case 2
         MsgBox "You have entered an incorrect username or password twice" & 
      vbCrLf & _
          "You have one more chance to do this correctly", vbCritical, "Warning"
         Me.txtUsername.SetFocus

     Case 3 
           MsgBox "You do not have access to EMS Database, Please contact 
              system Administrator", vbOKOnly, "Invalid Entry!"
           Application.Quit
         [COLOR="Green"][B] ' Exit Sub 'not needed as you have quit[/B][/COLOR]

     End Select[/COLOR][/B]
 End If

[B][COLOR="Red"]Select Case "SecurityLevel"

Case "Admin" [/COLOR][/B]
    'full control all buttons & options ON (on frmDataEntry_Navigation i have enabled button based on "Admin").
       DoCmd.OpenForm "frmDataEntry_Navigation"
       Exit Sub
 
[B][COLOR="red"]Case "Employee" [/COLOR][/B]
      'go to Data entry menu with User Access Level (by default button is 
         disabled on frmDataEntry_Navigation).
       DoCmd.OpenForm "frmDataEntry_Navigation"
       Exit Sub
[B][COLOR="red"]
Case "User" [/COLOR][/B]
       'go to Individual record access form.
        DoCmd.OpenForm "frmEmployeeNavigation"
        Exit Sub
[COLOR="red"][B] 
End Select[/B][/COLOR]

[COLOR="red"][B]Exit_Handler:
      Exit Sub

Err_Handler:
       MsgBox "Error " & Err.Number  & " " & Err.Description & " iun cmdLogin_Click procedure"
       Resume Exit_Handler[/B][/COLOR]
   
End Sub

HTH

 

[mba_110]

The problem in this area of code, the code is not even giving response to correct entry i checked with wrong one and write one both the result is same.

'Check case sensitivity of entered username and password is correct 'z123VF&@'.

   strLoginID = Nz(DLookup("LoginID", "tblUserSecurity", "LoginID= '" & Me.txtUsername & "'"), "")

    If strLoginID <> "" Then
            vpwd = Nz(DLookup("strPassword", "tblUserSecurity", "LoginID='" & LoginID & "'"), "")
        
        End If
    
    If StrComp(Me.txtPassword.Value, vpwd & vbNullString, vbTextCompare) <> 0 Then
              MsgBox "Invalid Login ID and Password.", vbOKOnly, "Invalid Entry"
              Exit Sub
    End If

Just i want you to know the tblUserSecurity fields name, may be it can help.

LoginID = Text (PK)
StrPassword = Text
SecurityLevel = Text



First we need to fix the above part of code and then need to see cases for wrong attempts and SecurityLevels, may be above is causing the problem once its clear i can move forward for remaining code portions.

Many thanks for your help buddy.

 

[isladogs]

Unfortunately you didn't say what that result was.
Nor exactly where its failing.

You need to step through the code line by line and check wha actually ha'pens

StrPassword is an odd choice of field name but I knew that from the DLookup. It sounds like a variable however.

Add debug.print lines or message boxes for strLoginID and vpwd.
Are those two If ... End If clauses being entered or bypassed?

Also did you look at my fully working example app? See link in post 10.
Using that might save you a lot of time

 

[mba_110]

Thank you for your guidance.


i have fixed one step in case sensitivity password is working as per the case sensitivity, but unfortunately username is not what changes i have to do to make username also case sensitive?

'Check case sensitivity of entered username and password is correct 'z123VF&@'.

   Debug.Print strLoginID = Nz(DLookup("LoginID", "tblUserSecurity", "LoginID= '" & Me.txtUsername & "'"), "")

    If strLoginID <> "" Then
      Debug.Print vpwd = Nz(DLookup("strPassword", "tblUserSecurity", "LoginID='" & LoginID & "'"), "")
        
    
    If StrComp(Me.txtPassword.Value, vpwd & vbNullString, vbTextCompare) <> 0 Then
              MsgBox "Invalid Login ID and Password.", vbOKOnly, "Invalid Entry"
              Exit Sub
              End If
    End If

Also after 1st wrong attempt 1 case is working only, if i enter 2nd time wrong password or username its not giving message for 2 case in attempts scenario can also suggest what is wrong in this part.

Attempts = 0 'reset the count

'Check if user's 3  attempts are wrong then close the database with message.
If Me.txtPassword.Value = DLookup("[strPassword]", "tblUserSecurity", "[LoginID]='" & Me.txtUsername.Value & "'") Then
     MsgBox "Welcome to Employee Management System"
 Else
     Attempts = Attempts + 1
     Select Case Attempts
     
     Case 1
         MsgBox "Username or password is incorrect!" & vbCrLf & _
           "Please try again", vbCritical, "Warning"
         Me.txtUsername.SetFocus
     
     Case 2
         MsgBox "You have entered an incorrect username or password twice" & vbCrLf & _
          "You have one more chance to do this correctly", vbCritical, "Warning"
         Me.txtUsername.SetFocus

     Case 3
           MsgBox "You do not have access to EMS Database, Please contact system Administrator", vbOKOnly, "Invalid Entry!"
           Application.Quit

     End Select
 End If

i went step by step and found the above, but unable to understand what is exactly missing in those areas.