Use StrComp Function to match case

[pdeman]

Hello,

Can anyone help me by modifying the following code to include StrComp Function to make the ValidPassword sql variable case sensitive?

‘***************************Code Start**************************************************
Private Sub cmdOK_Click()
On Error GoTo Err_Handler
Dim ValidPassword As DAO.Recordset

'Check for valid password.
Set ValidPassword = CurrentDb.OpenRecordset("SELECT * FROM tblLogonPassword WHERE " & _
"UserName='" & Forms![frmLogonPassword]!txtUserName.Value & "' AND " & _
"Password='" & Forms![frmLogonPassword]!txtPassword.Value & "';")

If (ValidPassword.RecordCount = 1) Then
If (ValidPassword!DisableLogon = True) Then
MsgBox "Logon Disabled", vbOKOnly, MsgTitle()
Set ValidPassword = Nothing
Exit Sub
Else
Set ValidPassword = Nothing
DoCmd.Close acForm, "frmLogonPassword"
Exit Sub
End If
End If

Set ValidPassword = Nothing

MsgBox "Invalid Entry." & Chr(13) & Chr(13) & "1) Ensure your User Name is correct." & _
Chr(13) & "2) Ensure your Password is correct.", vbOKOnly, MsgTitle()
DoCmd.GoToControl "txtPassword"

Exit_Handler:
Exit Sub

Err_Handler:
Call LogError(Err.Number, Err.Description, "cmdOK_Click()", , True)
Resume Exit_Handler

End Sub
‘***************************Code End**************************************************

This is the portion of code to modify:
Set ValidPassword = CurrentDb.OpenRecordset("SELECT * FROM tblLogonPassword WHERE " & _
"UserName='" & Forms![frmLogonPassword]!txtUserName.Value & "' AND " & _
"Password='" & Forms![frmLogonPassword]!txtPassword.Value & "';")

I have tried:
Set ValidPassword = CurrentDb.OpenRecordset("SELECT * FROM tblLogonPassword WHERE " & _
"UserName='" & Forms![frmLogonPassword]!txtUserName.Value & "' AND " & _
"([Password], Forms![frmLogonPassword]!txtPassword.Value,0)=0;")

But nothing I do works? Please help.

 

[pdeman]

sorry there is a typo:

I have tried:
Set ValidPassword = CurrentDb.OpenRecordset("SELECT * FROM tblLogonPassword WHERE " & _
"UserName='" & Forms![frmLogonPassword]!txtUserName.Value & "' AND " & _
"StrComp([Password], Forms![frmLogonPassword]!txtPassword.Value,0)=0;")

 

[TheAceMan1]

How are ya pdeman . . .

Try this:

[blue]   Dim db As DAO.Database, ValidPassword As DAO.Recordset, SQL As String
   Dim usrName As String, PWD As String

   Set db = CurrentDb
   usrName = Me!txtUserName
   PWD = Me!txtPassword
   
   SQL = "SELECT * " & _
         "FROM tblLogonPassword " & _
         "WHERE [UserName]='" & usrName & "' AND " & _
                StrComp([Password], PWD, vbBinaryCompare) = 0 & ";"
   Set ValidPassword = db.OpenRecordset(SQL, dbOpenDynaset)[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[pdeman]

Hello TheAceMan1,

Thanks for your response.

I tried the code but it generated the same error I was getting with my code.

This is the error:

Error 2465: Microsoft Access can't find the field '|' referred to in your expression.

 

[PHV]

Why not simply use the DLookUp function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

pdeman . . .

The [blue]StrComp[/blue] function was not concatenated properly. This is corrected. Also the [blue]Dlookup[/blue] suggestion by [blue]PHV[/blue] was an excellent one. It'll save quite a few lines of code and eliminates the recordset. So ... using [blue]Dlookup[/blue], try the following:

[blue]   Dim Cri As String, NL As String, Rtn
   
   NL = vbNewLine
   
   Cri = "[UserName]='" & Me!txtUserName & "' AND " & _
         "StrComp([Password], '" & Me!txtPassword & "', 0) = 0;"
   Rtn = DLookup("[DisableLogon]", "tblLogonPassword", Cri)
      
   If Not IsNull(Rtn) Then
      If Rtn = True Then
         MsgBox "Logon Disabled", vbOKOnly, MsgTitle()
      Else
         DoCmd.Close acForm, "frmLogonPassword"
      End If
   Else
      MsgBox "Invalid Entry." & NL & _
             "1) Ensure your User Name is correct." & NL & _
             "2) Ensure your Password is correct.", _
             vbOKOnly, _
             MsgTitle()
      Me!txtPassword.SetFocus
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[pdeman]

Hello all,

Excellent.
Both work fine.
Will stick with the StrComp in this instance.
Thanks to everyone for contributing to this thread.