Error in Change Password Form

[Manan Hotak]

Hi Guys!



I am having trouble with the following code. Every time I click on the command button I get the error message below. I think there is something with the sql statement. Please help.:

Error Message:

Runtime error 3075

Syntax error (missing operator) in query expression 'Last Name=Manan Hotak'.

The following is the code:

Private Sub ChangePassword_Click()

Dim strsql As String

'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboCurrentEmployee) Or Me.cboCurrentEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Program"
Me.cboCurrentEmployee.SetFocus
Exit Sub
End If

'Check to see if data is entered into the old password box
If IsNull(Me.oldpassword) Or Me.oldpassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Program"
Me.oldpassword.SetFocus
Exit Sub
End If

'Check value of password in Users to see if this
'matches value chosen in combo box
If Me.oldpassword.Value = DLookup("Password", "Users", "[Last Name]=""" & Me.cboCurrentEmployee.Value & """") Then

strsql = "UPDATE Users SET password=" & "'" & Me.NewPassword.Value & "' WHERE Last Name=" & Me.cboCurrentEmployee.Value
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True
MsgBox "Password has been changed", vbInformation, "Password Changed"
Else
MsgBox "The Old Password does not match", vbInformation, "Type Correct Old Password"

Exit_Command6_Click:
Exit Sub

Err_ChangePassword_Click:
MsgBox Err.Description
Resume Exit_ChangePassword_Click

End If

End Sub

[help_me_with_access]

we you concat strings, this syntax will not work:

"[Last Name]=""" & Me.cboCurrentEmployee.Value & """"

you need this:

"[Last Name]='" & Me.cboCurrentEmployee.Value & "'"

spelled out, it is:

*single quote
*ampersand
*double quote
*variable
*ampersand
*double quote
*single quote
*(the rest of the string sql that's left, if any)
*double quote to close string statement

[Manan Hotak]

Thanks for the solution above.

I have replaced the following code:

strsql = "UPDATE Users SET password=" & "'" & Me.NewPassword.Value & "' WHERE [Last Name]='" & Me.cboCurrentEmployee.Value & "'"


Now when I click on run command I get a Pop Up menu in which I have to put the user name for which I want to change the password. I don't understand how that comes. When I type the username in the menu it then changes the password.

[help_me_with_access]

I don't have time to look at your coding.

but I can tell you from experience that prompt dialogs of this sort that pop up like so is a result of an error usually representing a variable that is referenced improperly in code. the popup is basically telling you that "Last Name" exists somewhere in the code that doesn't compile into standard execution at runtime, but it can still be interpreted by the environment which is why the code doesn't break. I think that's right...

if you want to upload your file, I might be able to tell you what's up.

[Manan Hotak]

Hi

The problem is solved. I had mistakenly changed in the Users table the name of the field from Last Name into LastName. That was the reason with the Pop Up Message.

Final Working Code is as following for all other readers:

Private Sub ChangePassword_Click()

Dim strsql As String

'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboCurrentEmployee) Or Me.cboCurrentEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Program"
Me.cboCurrentEmployee.SetFocus
Exit Sub
End If

'Check to see if data is entered into the old password box
If IsNull(Me.oldpassword) Or Me.oldpassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Program"
Me.oldpassword.SetFocus
Exit Sub
End If

'Check value of password in table Users to see if this matches value chosen in combo box
If Me.oldpassword.Value = DLookup("Password", "Users", "[Last Name]=""" & Me.cboCurrentEmployee.Value & """") Then

'Change the Old Password in to New password
strsql = "UPDATE Users SET password=" & "'" & Me.NewPassword.Value & "' WHERE [Last Name]='" & Me.cboCurrentEmployee.Value & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL (strsql)
DoCmd.SetWarnings True
Debug.Print (strsql)
MsgBox "Password has been changed", vbInformation, "Password Changed"
Else
MsgBox "The Old Password doesnot match", vbInformation, "Type Correct Old Password"

Exit_Command6_Click:
Exit Sub

Err_ChangePassword_Click:
MsgBox Err.Description
'Resume Exit_ChangePassword_Click

End If

End Sub

[Manan Hotak]

Hi,

The code above is working perfectly but there is one small/big issue:

When I select a username from the combo box and set a new password for the same user then it changes the password for all users in the table Users, which means it is not changing one user password but it changes all users passwords.

Can anyone look at it.

[Manan Hotak]

Hi guys!

I just exported the Table and Form to a blank database and then imported the Form and Table to the original database. It is solved now. I don't know why it was not working previously. Welcome to Microsoft!

Anyway! You can now use the code as mentioned above.

I am marking the thread as "Solved".

Thanks for your hint help_me_with_access.