Password login code

[Guest]

I pulled this code from a past thread in 2005 by G Gill and Doug Steele, and
I modified it to match my table fields. I keep getting an error “Block If
without End Ifâ€. I’ve tried putting an “End If†after the “If Not IsNull…â€
statement, but then I get “Invalid syntaxâ€. Where should the “End Ifâ€
statement go?

Private Sub cmdLogin_Click()
On Error GoTo Err_cmdLogin_Click

Dim rs As Recordset
Dim strUser As String
Dim strPassword As String

txtName.SetFocus
strUser = txtUserNm
txtPassword.SetFocus
strPassword = txtPassword

If Not IsNull(Me.txtName) And Not IsNull(Me.txtPassword) Then
Set rs = CurrentDb.OpenRecordset("Select * from tblLogin where Name = '" &
strUser & "' And Password = '" & strPassword & "'", dbOpenSnapshot)


If rs.EOF Then
DoCmd.OpenForm "Main"
Else
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]

You've got two If statements, but only one End If

If Not IsNull(Me.txtName) And Not IsNull(Me.txtPassword) Then
Set rs = CurrentDb.OpenRecordset("Select * from tblLogin where Name = '" &
strUser & "' And Password = '" & strPassword & "'", dbOpenSnapshot)

If rs.EOF Then
DoCmd.OpenForm "Main"
Else
MsgBox "In Correct"
End If

End If ' <== missing

 

[Guest]

Thanks for responding! Stupid question -- where do I put the "End If'"?
I've tried at the end of the first If statement, after the second If
statement, before the "End Sub" -- I either get "Invalid syntax" or "Invalid
argument". And is there actually a single quote after the "End If"?

 

[Douglas J. Steele]

It goes between the existing End If statement and the Exit_cmdLogin_Click:
line.

A single quote in code means everything after the quote is a comment.
However, there's really no need to include it in this case.

 

[Guest]

I tried it there and I'm still getting the "Invalid argument" error. Is
something else awry?:

Private Sub cmdLogin_Click()
On Error GoTo Err_cmdLogin_Click

Dim rs As Recordset
Dim strUser As String
Dim strPassword As String

txtName.SetFocus
strUser = txtUserNm
txtPassword.SetFocus
strPassword = txtPassword

If Not IsNull(Me.txtName) And Not IsNull(Me.txtPassword) Then
Set rs = CurrentDb.OpenRecordset("Select * from tblLogin where Name = '" &
strUser & "' And Password = '" & strPassword & "'", dbOpenSnapshot)

If rs.EOF Then
DoCmd.OpenForm "Main"
Else
MsgBox "In Correct"
End If
End If

Exit_cmdLogin_Click:
Exit Sub

Err_cmdLogin_Click:
MsgBox Err.Description
Resume Exit_cmdLogin_Click

End Sub

 

[Douglas J. Steele]

Dim rs As DAO.Recordset

If that still doesn't fix it, what line of code is causing the error?

(incidentally, there's no need for the two SetFocus statements)

 

[Guest]

Maybe I’m just cursed! That gave me a “User-defined type not definedâ€. I
took out the extra lines, and I’m still getting “Invalid Argument†when I
click on the button. It’s not highlighting any particular line of code – it
doesn’t take me to the VB editor – it just pops up a message box “Invalid
Argumentâ€.

 

[Douglas J. Steele]

If it won't accept Dim rs As DAO.Recordset, that implies you don't have a
reference set to DAO.

While in the VB Editor, select Tools | References from the menu bar. Scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, check it, and click on OK

 

[Guest]

Wee! That worked almost! I really appreciate all the time you’ve taken to
help me with this!

I’m getting “Incorrect†which would be great except that I’m entering the
correct names and passwords, so I must be missing something else. The table
is tblLogin, the fields are Name and Password and the text boxes are txtName
and txtPassword. Do I need to have the strUser and strPassword in the code?
It came from the original code, and I wasn’t sure why it was there so I
didn’t want to mess with it. How about rs.EOF – should that be there?

 

[Ron2006]

Be aware that this code will NOT work if any of you users have a
single quote in their name.

The SQL string gets screwed up.

Ron

 

[Douglas J. Steele]

I didn't notice before that your If statement is backwards.

rs.EOF will be true if no record was retrieved for that name and password,
so you want:

If rs.EOF Then
MsgBox "Incorrect"
Else
DoCmd.OpenForm "Main"
End If

BTW, Name is not a good name for a table field. It's a reserved word, and
using reserved words for your own purposes can lead to problems. Password is
a reserved word, too.

If you cannot (or will not) rename it, at least put square brackets around
it:

Set rs = CurrentDb.OpenRecordset("Select * from tblLogin where " & _
"[Name] = '" & strUser & "' And [Password] = '" & strPassword & "'", _
dbOpenSnapshot)

For more details about reserved words, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Guest]

Thanks Ron!

 

[Guest]

You rule!! That did it! Thanks so much for your help, and I will fix those
naming issues.

I didn't notice before that your If statement is backwards.

rs.EOF will be true if no record was retrieved for that name and password,
so you want:

If rs.EOF Then
MsgBox "Incorrect"
Else
DoCmd.OpenForm "Main"
End If

BTW, Name is not a good name for a table field. It's a reserved word, and
using reserved words for your own purposes can lead to problems. Password is
a reserved word, too.

If you cannot (or will not) rename it, at least put square brackets around
it:

Set rs = CurrentDb.OpenRecordset("Select * from tblLogin where " & _
"[Name] = '" & strUser & "' And [Password] = '" & strPassword & "'", _
dbOpenSnapshot)

For more details about reserved words, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Wee! That worked almost! I really appreciate all the time you've taken
to
help me with this!

I'm getting "Incorrect" which would be great except that I'm entering the
correct names and passwords, so I must be missing something else. The
table
is tblLogin, the fields are Name and Password and the text boxes are
txtName
and txtPassword. Do I need to have the strUser and strPassword in the
code?
It came from the original code, and I wasn't sure why it was there so I
didn't want to mess with it. How about rs.EOF - should that be there?