Login problem

[wdu94]

Hello,

I have login form for filling "UserID(primary key) and password" and also I have table "tblPASSWORD". I don't know why this doesn't work. When I filling the UserID and password then click the button, I got error "Item can not be found in the collection correspond to the requested name or ordinal". But I can find this UserID and password in the table that means I enter the correct UserID and password.

May be the UserID duplicate when I enter it? if so, how to solve it.

Many Thanks.
Jeannia

The following are my codes:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim conn As New ADODB.Connection
Dim com As New ADODB.Command
Set conn = CurrentProject.Connection
Set com.ActiveConnection = conn
Dim rst As ADODB.Recordset


Dim strSQL As String
Dim sPasswd$
strSQL = "select UserID, Password from tblPASSWORD " & _
"where UserID like '" & txtUserID & "'"
Set rst = New ADODB.Recordset
rst.Open strSQL, conn
If rst.EOF And rst.BOF Then
MsgBox "No such userID (" & txtUserID & &quot. Please try again.", vbInformation, "Unkown user"
GoTo Exit_Command6_Click
Else
sPasswd = rst.Fields(2) & ""
If sPasswd <> Me.txtPassWord Then
MsgBox &quot;Incorrect userid and/or password.&quot;, vbInformation, &quot;Authentication failed&quot;
GoTo Exit_Command6_Click
End If
End If

DoCmd.OpenForm &quot;frmInspectors&quot;

Exit_Command6_Click:
' clean up and then exit
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

 

[Maquis]

Well, I didn't examine your code too closely so I'm not sure why it failed, but here is a less complicated way of doing it.

Dim sPassword as string
Dim Criteria as string

Criteria = 'UserID = &quot;' & txtUserID & '&quot;'
sPassword = Dlookup(&quot;Password&quot;,&quot;tblPassword&quot;,Criteria)
if isnull(sPassword) then
msgbox &quot;no such user&quot;
GoTo Exit_Command6_Click
else
if sPassword <> me!txtPassword then
msgbox &quot;wrong password entered&quot;
GoTo Exit_Command6_Click
end if
end if

<do stuff> Maq B-)
<insert witty signature here>

 

[jebry]

Hi!

If I'm not mistaken, the Fields collection is a zero based collection so your line rst.Fields(2) would be out of range and should be rst.Fields(1) to get the password.

hth
Jeff Bridgham