Runtime Error on Password code. HELP!!!

[roaml]

Hi,

I have used the following code (in earlier Access versions) to record a user’s name and log-in date when accessing a particular form. I am trying to use this code for a form in Access 2003 but I am running into a run-time error message “Type Mismatch”. I have had no success in locating the problem. I wonder if someone can see what I am missing.

<BEGIN CODE>

Private Sub WO_Login_Dialog_Click()

Dim MyTable As Recordset
Dim strMessage As String

Set MyTable = CurrentDb.OpenRecordset("UserPass")

MyTable.Index = "UserName"
MyTable.Seek "=", Form_Password.UserName

If MyTable.Fields("UserPW") = Form_Password.UserPW Then
Form_Password.UserID.Value = MyTable.Fields("UserID")
Call stamp(Form_Password.UserName, "WOLog", Now())
DoCmd.Close acForm, "Password", acSaveYes
DoCmd.OpenForm "issue_log_new_form"

Else
If MyTable.Fields("UserPW") <> Form_Password.UserPW Then
MsgBox ("Incorrect Password!! Please try again.")
UserPW = ""
UserPW.SetFocus

End If

If UserPW = "" Then
UserPW = ""

Else
MsgBox ("Please Enter Your Password!")
UserPW.SetFocus
End If
End If
End Sub

<END CODE>

<BEGIN MODULE>

Sub stamp(formname As String, UserName As String, D As Date)

Dim db As Database

Set db = CurrentDb

db.Execute ("INSERT INTO USERS (UserName,formName,accessDate) VALUES ('" + formname + "','" +
UserName + "','" + Format(D, "mm/dd/yyyy hh:mm:ss") + "')")

End Sub

<END MODULE>


Thank you.

 

[lupins46]

A clue would be nice....
Which line is the code failing on?

 

[roaml]

I'm sorry, it was a long day.

Here you go.

Set MyTable = CurrentDb.OpenRecordset("UserPass")

Thank you.

 

[lupins46]

Try adding a dao prefix:

Dim MyTable As DAO.Recordset

 

[roaml]

lupins46,

I tried your suggestion, and now I am receiving this message "User-defined type not defined." on Dim MyTable as DAO.Recordset.

Thank you.

 

[JerryKlmns]

I modified it a lot to use ADO instead of DAO. If you need DAO on Tools-->References tick on DAO 3.6 library!

Private Sub WO_Login_Dialog_Click()
Dim rst As ADODB.Recordset

If Forms!Form_Password.UserName.Value & "" = "" Then
   MsgBox ("Please Enter Your UserName !")
   Forms!Form_Password.UserName.SetFocus
   Exit Sub
End IF
If Forms!Form_Password.UserPW.Value & "" = "" Then
   MsgBox ("Please Enter Your Password!")
   Forms!Form_Password.UserPW.SetFocus
   Exit Sub
End IF                            

Set rst = CurrentProject.Connection.Execute ("Select UserPass.* From UserPass Where [UserName]='" & Forms!Form_Password.UserName.Value & "' And UserPW = " & Forms!Form_Password.UserPW.Value & ";")
If rst.eof and rst.bof then
   MsgBox ("Incorrect UserName/Password!!  Please try again.")
   Forms!Form_Password.UserPW.SetFocus
   Forms!Form_Password.UserPW.Value = ""
   rst.Close
   Set rst= Nothing
Else
   rst.Close
   Set rst= Nothing
   Call stamp(Form_Password.UserName, "WOLog", Now())
   DoCmd.Close acForm, "Password", acSaveYes
   DoCmd.OpenForm "issue_log_new_form"
End IF
End Sub