Match UserNm and Password from table

[GGill]

Hi,
I have one table 'tblUsers' with UserNm and Password fields. In my
'frmLogin' form i have 2 text boxes txtUserNm and txtPassword and button
'Login'. So I am trying too on click 'Login' button write code, If string
UserName and Password match from tblUsers then open some new form for example
'frmNew', If not then msg box.
Here is my unfinished code, please make this code work. I think I have to
use rs.Match and rs.NotMatch or Look, but I don't know how, Please help me
finish this code.
----------------------------------
Private Sub cmdLogin_Click()
On Error GoTo Err_cmdLogin_Click

Dim rs as recordset
Set rs = CurrentDB
Dim strUser as string
Dim strPassword as string

txtUserNm.setfocus
strUser=txtUserNm
txtPassword.setfocus
strPassword=txtPassword

If Not IsNull(me.txtUserNm) and Not IsNull(me.txtPassword) Then
set rs = CurrentDB.OpenRecordset("Select * from tbl Users where UserNm = '"
& strUser & " And Password = '" & strPassword &, dbOpenSnapshot)

If rs.EOF Then
Docmd.OpenForm "frmNew"
End If

If Not rs.EOF then
MsgBox "In Correct"

End If

Exit_cmdLogin_Click:
Exit Sub

Err_cmdLogin_Click:
MsgBox Err.Description
Resume Exit_cmdLogin_Click

End Sub

 

[Douglas J. Steele]

The quotes in your SQL are incorrect, assuming that both UserNm and Password
in your table are text fields.

It needs to be:

set rs = CurrentDB.OpenRecordset("Select * from tbl Users where UserNm = '"
& strUser & "' And Password = '" & strPassword & "'", dbOpenSnapshot)

Exagerated for clarity, that's

set rs = CurrentDB.OpenRecordset("Select * from tbl Users where UserNm = ' "
& strUser & " ' And Password = ' " & strPassword & " ' ", dbOpenSnapshot)

As well, your logic is backwards in terms of checking the recordset: if no
match is found, rs.EOF will be true, not false:

If rs.EOF Then
Docmd.OpenForm "frmNew"
Else
MsgBox "In Correct"
End If

Remove this line of code:

Set rs = CurrentDB

You've defined rs as a recordset: you can't set it equal to the current
database.

Since you're using DAO, you're best off changing your declaration from

Dim rs as recordset

to

Dim rs as DAO.Recordset

Note that you will not be able to make passwords case sensitive with this
approach. As well, I hope you realize that this doesn't really provide any
security: a user doesn't have to be that knowledgable to get around this
security.

 

[GGill]

Hi Douglas,
Thank you so much for the help, now the code works perfect.
Thanks again.