Access 97 code not working in Access 2000 3

[sleonard1432]

I am attempting to use a custom built login and password form that will automatically autopopulate a Current User table for use on forms that will stamp the information automatically. I was able to get the code to work in Access 97, however it is failing miserably in Access 2000. Here is the code:

Private Sub cmdLogin_Click()

Dim UserID, Pwd As String

txtUserID.SetFocus
UserID = txtUserID.Text

txtPassword.SetFocus
Pwd = txtPassword.Text


If UserID = "" Or Pwd = "" Then
MsgBox "Please enter a UserID and password"
If UserID = "" Then
txtUserID.SetFocus
Else
txtPassword.SetFocus
End If
GoTo TheEnd

End If

Dim PwdCheckRS As Recordset
Set PwdCheckRS = CurrentDb.OpenRecordset("select * from LinkedCSRs where userid = " & Chr(34) & UserID & Chr(34))

If PwdCheckRS.EOF = True Then
MsgBox "UserID not known to database"
txtUserID.SetFocus
GoTo TheEnd
End If

PwdCheckRS.MoveFirst

If PwdCheckRS.Fields("Password") = Pwd Then
Dim stDocName As String
stDocName = "frmSplash"
DoCmd.OpenForm "frmsplash"
Else
MsgBox "Invalid Password!"
txtPassword.SetFocus
GoTo TheEnd

End If

Dim MyCSRID As String
Dim MyDefClient As String

Dim MyQT As String
MyCSRID = PwdCheckRS.Fields("csrid")
MyDefClient = PwdCheckRS.Fields("defaultClient")
MyQT = "Select " & Chr(34) & MyCSRID & Chr(34) & " as CurrentCSRID, " & Chr(34) & MyDefClient & Chr(34) & "as CurrentDefClient into LocCurrentUser"

DoCmd.SetWarnings False
DoCmd.RunSQL MyQT
DoCmd.SetWarnings True


TheEnd:
End Sub

 

[ZmrAbdulla]

Try changing

Dim PwdCheckRS As Recordset
Set PwdCheckRS = CurrentDb.OpenRecordset("select * from LinkedCSRs where userid = " & Chr(34) & UserID & Chr(34))

to

 Dim PwdCheckRS As DAO.Recordset    
    Dim db As Database
   Set db = CurrentDb()
   Set PwdCheckRS = CurrentDb.OpenRecordset("select * from LinkedCSRs where userid = " & Chr(34) & UserID & Chr(34))

Zameer Abdulla
Visit Me

 

[PHV]

Simply replace this:
Dim PwdCheckRS As Recordset
By this:
Dim PwdCheckRS As DAO.Recordset

You have to reference the DAO 3.# library:
menu Tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mp9]

You may also need to explicityl set the reference to the DAP 3.6 library (Tools, References from a VBA code editor window) since Access 2000 is expecting you to be using ADO rather than DAO.

http://www22.brinkster.com/accessory/

 

[sleonard1432]

THANK YOU ALL FOR YOUR HELP. It was right in front of my face the whole time. Thanks again. Great help.