Error when I try to change password

[accessnewb]

I have this login form and when I add a new user to the DB, I set the password to temporary. When the password is "temporary" then a new form opens up asking the user if he wants to change password. When user clicks on "yes", then he is taken to another form where he has to enter "new password" and also "confirm new password". So far so good!

I have a button called "Change password" and when user clicks on it, the password is supposed to be updated in the DB. This is where I am facing an error. The code for onClick of the change password button is below:

Private Sub cmdChangePassword_Click()
If Me.txtnewPwd.Value = Me.txtConfirmPwd.Value Then
DoCmd.RunSQL "Update Login set strEmpPassword = Me.txtnewPwd.Value where strEmpPassword = " & strMyPassword & " And lngEmpID = " & lngMyEmpID & ""
MsgBox "Password successfully changed!", vbOKOnly, "Password change"
DoCmd.Close acForm, "F_ChangePasswordYes", acSaveNo
DoCmd.SelectObject acTable, , True
DoCmd.OpenForm "F_Products"
Else


MsgBox "Passwords do not match", vbOKOnly, "Password error"
End If
End Sub

I have declared strMyPassword and lngMyEmpID as global variables.


The problem is when I click on change, I get 2 MsgBoxes asking me to "enter parameter value" (Please check the attached file)


and then I get the confirmation msg saying "you are about to update 0 row(s)". (Please check the attached file - confirm.jpeg)


I know there is some error in my update query cos my password value is not changed.

And even if everything was peachy with my update query, will I still get the confirmation saying ""you are about to update 1 row(s)" ?? Is there anyway to not get that???

[TheShabz]

"Update Login set strEmpPassword = " & Me.txtnewPwd.Value & " where strEmpPassword = """ & strMyPassword & """ And lngEmpID = " & lngMyEmpID & ""

See my changes in red and give that a go. Let me know if that works. The two issues were that Me.txtNewPwd.Value was treated as part of a string and not a reference to your textbox. also strMyPassword was not being treated a string. Do a Google search on how to denote a string within a string in VBA.

On a sidenote, unlike VB, in VBA the .Value is implied, so you don't have to specify it. Me.txtnewPwd.Value is the same as Me.txtnewPwd

[accessnewb]

@ TheShabz: thanks for the solution! It works. But I still get the MsgBox "you are about to update 1 row(s)" . Can I somehow remove that MsgBox?

Also, I still have the "Enter parameter value" issue: once I enter the password and click change I get a MsgBox (shown in attachment).

[TheShabz]

The "you are about to update..." message box can be removed by putting:
DoCmd.SetWarnings False
'your query
DoCmd.SetWarnings True

Be careful with the SetWarnings because if you don't turn it back on (True) or the procedure/sub dies before it turns back on, Access will not show any system warnings or messages until you turn them back on or close out and restart.

For the second dialog box do this:
1. right below Private Sub cmdChangePassword_Click() enter "Dim strSQL As String" (no quotes
2. and under that... strSQL = "Update Login set strEmpPassword = " & Me.txtnewPwd.Value & " where strEmpPassword = """ & strMyPassword & """ And lngEmpID = " & lngMyEmpID
3. under that... debug.print strSQL

When you run it again, paste here what shows up in you immediate window. It's probably some string concatenation issue.

[accessnewb]

Alright! I did what u told and now I don't get the warning "You're about to update 1 row(s)...." . But the "Enter parameter value" MsgBox is still there.


This is the code I used:

Private Sub cmdChangePassword_Click()
Dim strSQL As String
If Me.txtnewPwd.Value = Me.txtConfirmPwd.Value Then
strSQL = "Update Login set strEmpPassword = " & Me.txtnewPwd.Value & " where strEmpPassword = """ & strMyPassword & """ And lngEmpID = " & lngMyEmpID & ""
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Debug.Print strSQL
MsgBox "Password successfully changed!", vbOKOnly, "Password change"
DoCmd.Close acForm, "F_ChangePasswordYes", acSaveNo
DoCmd.SelectObject acTable, , True
DoCmd.OpenForm "F_Products"
Else
MsgBox "Passwords do not match", vbOKOnly, "Password error"
End If
End Sub


This is what I get in my immediate window: Update Login set strEmpPassword = check where strEmpPassword = "temporary" And lngEmpID = 6

[TheShabz]

You took out your RunSQL command. Nothing ran. the "strSQL =" part just sets the string to a variable. You then do
DoCmd.SetWarnings Fase
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

I still need to know what the debug.print shows.

[accessnewb]

Hi,
Sorry! I realized I took out the RunSQL command and so I edited my post. I guess you replied before u had a chance to look at my edited post. Please have a look at my edited reply.

[TheShabz]

First, your syntax should be DoCmd.RunSQL (strSQL) 'you need the parens

I assume 'check' is what the new password should be. In that case:
strSQL = "Update Login set strEmpPassword = """ & Me.txtnewPwd.Value & """ where strEmpPassword = """ & strMyPassword & """ And lngEmpID = " & lngMyEmpID & ""

[accessnewb]

yayy!! It worked!! Thanks

[TheShabz]

Glad you got it to work. If that's all you need, please mark the thread as solved.