password help

[junmart]

i am learning how to verify userid and password entered by the users. it seems to be working except the part when the wrong userid or password is entered. here is my code:

Dim db As Database
Dim rst1 As Recordset
Set db = CurrentDb
Set rst1 = db.OpenRecordset("Select * From passwordtest Where [UserId]= '" & txtuserid & "'And [password] = '" & txtpswd & "'")

If IsNull(Me![txtpswd]) Then
MsgBox "Missing Password information." & vbCrLf & "Please enter information Now or Cancel!", vbOKOnly + vbExclamation, "Password Info"
Me![txtpswd].SetFocus
Exit Sub
End If

If rst1("password") = Me![txtpswd] And rst1("userid") = Me![txtuserid] Then
MsgBox "successful!", vbExclamation
Else
MsgBox "invalid password", vbExclamation

End If
Exit Sub
End Sub

i cant make it display the message "invalid password", instead it gives me an error message "no current record", run time error! whats wrong with my code? please help!

 

[Alexandre]

Dim db As Database
Dim rst1 As Recordset

If Me![txtpswd]<>"" AND Me![txtuserid]<>"" Then
'Before anything else test whether
'all required data were entered
Set db = CurrentDb
Set rst1 = db.OpenRecordset("Select * From passwordtest Where [UserId]= '" & Me![txtuserid] & "'And [password] = '" & Me![txtpswd] & "'")
'But if there is no record in your table matching the criterias
'(ie wrong password and/or login)
'then the returned recordset will be void. So you must make a test
if rst1.RecordCount = 0 then
MsgBox "Invalid password or login", vbExclamation
Me![txtuserid].SetFocus
Else:
MsgBox "Access granted", vbExclamation
'On principle you would want to undertake some specific action
'here (open the switchboard and close the current form?)
end if

else:
MsgBox "Missing information." & vbCrLf & "Please enter Login AND Password Now or Cancel!", vbOKOnly + vbExclamation, "Password Info"
Me![txtuserid].SetFocus
end if ' Before leaving the sub!

End Sub

[This message has been edited by Alexandre (edited 10-03-2001).]