error 3464 - Password change form

[Sheba]

hi, in addition to having a combo box and txtPassword, I have modified my login form to include two more text boxes (txtnewPW and txtConfPW). These are initially invisible but when the user's PWreset field (in the user table) is true, they become visible. When I click on the OK button, I want the new password to update user's password in the table tblUser.

on the highlighted line, I'm getting run-time error 3464: "data type mismatch in criteria expression". what is wrong with this code?

Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb

strSQL = "UPDATE tblUser SET tblUser.strPassword = '" & Me.txtConfPW.Value & "' " & "WHERE (((tblUser.UserID)='" & Forms!frmLogin!cmbUser.Column(0) & "'));"

If Not IsNull(Me.txtPassword) And Me.txtConfPW <> Me.txtPassword Then

db.Execute strSQL, dbFailOnError

DLookup("strPassword", "tblUser", "[UserID]=" & Forms!frmLogin!cmbUser.Column(0)) = Me.txtConfPW.value
MsgBox "Password updated!", vbOKOnly, "Success!"
'MsgBox "You must log in again with new password"

Else

MsgBox "Password confirmation does not match New Password.", vbOKOnly, "Mismatched Password Confirmation"
Me.txtNewPW.Visible = True


Me.txtConfPW.Visible = True
Me.txtConfPW.SetFocus

End If

[ssanfu]

In the table, is tblUser.strPassword a Text type field?
In the table, is tblUser.UserID a Text type field?

For debugging, add the blue lines to your code.:

msgbox Me.txtConfPW
msgbox Forms!frmLogin!cmbUser.Column(0)

strSql = "UPDATE.........."
debug.print strSql

Is the displayed data what you expect given the fields data types?

[ranman256]

dont run that...just use
docmd.runsql strSQL

[Sheba]

many thanks steve.

huh? don't run what

[ssanfu]

Using
db.Execute strSQL, dbFailOnError
is handled by Jet (faster)

Using
docmd.runsql strSQL (slower)
has to be interperated first by Access, then executed by Jet. DoCmd also gives the warning messages; dB.Execute (or Currentdb.Execute) doesn't.

I always use "db.Execute strSQL, dbFailOnError", never "docmd.runsql".

[Sheba]

Okay ranman, I assumed that I understood your post but commenting' out the currentdb.execute line still causes the error.

Steve - on tblUser, strPassword is Text data type and userID is autonumber. I added the lines. It gave me back my new password and userID and at then end, the error 3464 on the same line

[ssanfu]

and userID is autonumber.

Then the WHERE clause should not have the single quotes:

"WHERE (((tblUser.UserID)='" & Forms!frmLogin!cmbUser.Column(0) & "'));"

Should be:

"WHERE (((tblUser.UserID)=" & Forms!frmLogin!cmbUser.Column(0) & " ));"

It is better to use "db.Execute " (IMHO)

[Sheba]

thanks Steve. Sorry, it's been a long day and I'm kinda in a daze right now, what does IMHO means

[ssanfu]

http://www.urbandictionary.com/define.php?term=IMHO

[Sheba]

thanks for that Steve. Nothing like humour to lighten the day. I'll study that dictionary closely.

My code now works by the way. Thank you and thanks to you as well ranman. Have an excellent weekend!!

[Sheba]

um... question though. I want the PWreset field to be set to False after the password update.
I used the code in blue but it is not working... it is giving run time error 451 on the same line

sigh! I should have just left for the day. I had to go and add something to the program

MsgBox "Password updated!", vbOKOnly, "Success!"
MsgBox "You must log in again with new password"
Forms!frmLogin!cmbUser.Column(4) = False

[ssanfu]

You can't change the value of a column in the row source for the combo box. You have to update the underlying table for whatever the field is in Column(4), then requery the combo box (or maybe the form - I've never tried this). Does that make sense?

Something like"

strSQL = "UPDATE tblUser SET tblUser.PWreset = FALSE WHERE tblUser.UserID =" & Forms!frmLogin!cmbUser.Column(0) & ";"
db.Execute strSQL, dbFailOnError

Not sure what the table name is.....

[Sheba]

hey Steve, yes it makes sense to me. I'll try that and repost an update
thanks again

[Sheba]

yes, yes yes yes, it worked Steve to the rescue again.
thanks

[ssanfu]

Aww, shucks.....