Textbox Case Sensitive

[cbrsix]

I am trying to create a password that is case sensitive for my login form. I have searched the internet for a solution that I can integrate into my code. I haven't been able to find something that works. I probably am just not inputting things correctly as I am still fairly new to vba, even though I am learning. I know I am able to utilize the ascii character codes somehow but not sure how to put it into my current code. Any help would be appreciated. See below for the code.

Private Sub LogIn_Click()
On Error GoTo errline
If IsNull(Me.txtUserName) Then
MsgBox "Please enter a User Name.", vbInformation, "Missing User Name"
Exit Sub
End If
If IsNull(Me.txtPassword) Then
MsgBox "Please enter a Password for the user name " & Me.txtUserName & ".", vbInformation, "Missing User Name"
Exit Sub
End If
If tblUsers.Password = txtPassword Then
For i = 1 To Len(tbleUsers.Password)
If (Asc(Mid(tblUsers.Password, i)) <> Asc(Mid(txtPassword, i))) Then
counter = counter + 1
End If


Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryRS")
With rs
If .EOF And .BOF Then
MsgBox "Your log on information was incorrect. Try again.", vbCritical, "Failed Log On"
GoTo exitline
Else
If !Administrator_Rights = False Then
DoCmd.OpenForm "frmMenu"
DoCmd.Close acForm, "frmLogOn"
GoTo exitline
End If
End If
End With
If txtUserName = "bschank" Then
DoCmd.ShowAllRecords
DoCmd.Close acForm, "frmLogOn"
Else
DoCmd.OpenForm "frmMenu"
DoCmd.Close acForm, "frmLogOn"
End If
exitline:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub
errline:
Select Case Err.Number
Case Else
MsgBox "There was an error in the program. Please notify database administrator of the following error: Error Number: " & Err.Number & " " & Err.Description, vbCritical, "Please write this error down and note what you were doing at the time."
GoTo exitline
End Select
End Sub

[rivereridanus]

Did you try using InStrB?
"One easy fix is to use the InStrB ("in string byte") function, which considers the strings based on byte value (where "x" is different than "X"). By contrast, the InStr function considers just character value ("x" is the same as "X").
The transformed If statement becomes:
If CBool(InStrB(strOne, strTwo)) Then...
This statement takes the output of InStrB() and converting it to a Boolean (True or False) expression. When InStrB returns 0 (no match found), CBool converts the expression's value to False. If InStrB finds the strings match (by case and by character) the expression evaluates to True."
http://blogs.office.com/b/microsoft-...mparisons.aspx

[John_G]

You can also use the strComp function.

StrComp(String1, String2, 0) returns 0 if the two strings are equal WITH case sensitity.

So, strcomp("ABC", "ABC" , 0 ) = 0 and strcomp("ABC", "abc" , 0 ) = non-zero

John

[cbrsix]

To make sure I am understanding this correctly, I would do the following

If strcomp([tblUsers.Password],txtPassword,1) Then ... whatever else I want to happen.

Is this correct? Would this be added to or replacing a section I have above? Or is it just an additional If/Then?

[John_G]

Hi

Not quite - you have to use '0' in the strComp to ensure case sensitive comparison.

If strcomp([tblUsers.Password],txtPassword, 0 ) <> 0 Then
'
' password was incorrect - Notify user
'
goto exitline
endif
'
' password was correct - continue
'

The above code would replace this block:

If tblUsers.Password = txtPassword Then
For i = 1 To Len(tbleUsers.Password)
If (Asc(Mid(tblUsers.Password, i)) <> Asc(Mid(txtPassword, i))) Then
counter = counter + 1
End If

Whether you could take out this bit or not, I don't know - what is qryRS for? Can a user enter a valid User name - password combination, and still not be allowed in?

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryRS")
With rs
If .EOF And .BOF Then
MsgBox "Your log on information was incorrect. Try again.", vbCritical, "Failed Log On"
GoTo exitline
Else

John

[Rawb]

Even though VBA forces it for error conditions, Goto statements are generally considered "bad practice" when programming. I'd recommend you take John_G's solution and turn it into an If statement instead:

If Not StrComp([tblUsers].[Password], txtPassword, 0) = 0 Then
  ' password was incorrect - Notify user
Else
  '  password was correct - continue
End If

[cbrsix]

I'm not having any luck with this. I am trying these suggestions and it's still not recognizing the different cases. It is still case insensitive..

[John_G]

Try putting a msgbox at the beginning of the function to show the values of and [tblUsers].[Password] and txtPassword

If they are different, post the bit of code that does the check. We have to be missing something.

John

[Rawb]

Try the functions below (you might need to change the SQL Query to match your Table structure)

Private Function CheckLoginInfo(UserName as String, Password as String) as Boolean
  On Error GoTo Error_CheckLoginInfo ' Error checking FTW!

  ' Declare and initialise our Recordset with a Query that looks up the user's UserName ONLY (password checked in code)
  Dim rstInfo as DAO.Recordset

  Set rstInfo = CurrentDb().OpenRecordset("SELECT * FROM tblUsers WHERE [UserName]='" & UserName & "'", dbOpenForwardOnly)

  ' If the RecordCount Property of the Recordset is 0, then no user with that UserName was found
  If rstInfo.RecordCount = 0 Then
    CheckLoginInfo = False ' Failed Login!
  Else
    ' If a comparison of the password in the database and the user entered password DOES NOT equal 0, then they DO NOT match
    If Not StrComp(rstInfo("Password"), Password, 0) = 0 Then
      CheckLoginInfo = False ' Therefore failed Login!
    Else ' If they DO match
      CheckLoginInfo = True ' Successful Login! (YAY!)
    End If
  End If

' Clean up our Objects and exit the Function
FunctionClosing:
  rstInfo.Close

  Set rstInfo = Nothing

  Exit Function

' If we ran into an error somewhere
Error_CheckLoginInfo:
  CheckLoginInfo = False ' Assume Login failed!

  Resume FunctionClosing ' Exit the function
End Function


Private Sub LogIn_Click()
  On Error GoTo FunctionClosing ' Error checking FTW!

  Dim boolGoodLogin As Boolean

  ' If either the UserName or Password are blank, force the user to enter them before continuing
  If IsNull(Me!txtUserName) Then
    MsgBox "Please enter a User Name", vbInformation, "Missing User Name"

    Me!txtuserName.SetFocus()
  Else If IsNull(Me!txtPassword) Then
    MsgBox "Please enter a Password for the user name " & Me.txtUserName & ".", vbInformation, "Missing User Name"

    Me!txtPassword.SetFocus()
  Else ' If both a UserName and Paswrod were entered
    boolGoodLogin = CheckLoginInfo(Me!txtuserName, Me!txtPassword) ' Check them against the database

    ' If the informatched and the login was successful
    If boolGoodLogin = True Then
      DoCmd.OpenForm "frmMenu" ' Open our new Form

      ' If our user was bschank and had the correct password
      If txtUserName = "bschank" Then
        DoCmd.ShowAllRecords ' Show everything!
      End If

      DoCmd.Close acForm, "frmLogOn" ' Close the Login Form
    Else ' If the login failed, alert the user and clear their last attempted password
      MsgBox "Your log on information was incorrect. Try again.", vbCritical, "Failed Log On"

      Me!txtPassword = Null
      Me!txtPassword.SetFocus()
    End If
  End If
End Sub

[cbrsix]

So I've tried these and decided to simplify it to the following.

Private Sub LogIn_Click()
If IsNull(Me.txtUserName) Then
MsgBox "Please enter a User Name.", 16, "Missing User Name"
Exit Sub
End If
If IsNull(Me.txtPassword) Then
MsgBox "ddf", 16, "adf"
Exit Sub
End If
If Not StrComp(Password, txtPassword, 0) = 0 Then
MsgBox "wrong", 16
Else
If txtUserName = "admin" Then
DoCmd.ShowAllRecords
DoCmd.Close acForm, "frmLogOn"
Else
DoCmd.OpenForm "frmMenu"
DoCmd.Close acForm, "frmLogOn"
End If
End If
End Sub

The problem now is that in my sample db this works exactly how I want it to. But when I enter this code into my main db it doesn't work. It says that the password is always wrong.

[Rawb]

Is this the full Sub? Because I don't see where "Password" is being declared.

[John_G]

In this line: If Not StrComp(Password, txtPassword, 0) = 0 Then

it's easy enough to see what txtPassword is - but where is the value in Password coming from?

It looks like you left out the bit where it checks the database table tblUsers to to see if the user name / password combination is correct, so of course it is always "wrong"

John

[cbrsix]

I just tried that. I'm getting the same results. I type in a correct user name and corresponding password and it comes back with the message box that it's wrong.

[John_G]

Rawb: the 0 as the third argument in the StrComp call indicates binary comparison is to be used.

John

[John_G]

Did you add code to check the database for the password corresponding to the user name?
It's not txtPassword that is the problem - it's Password. Where does it get a value to be used in the comparison?

John