Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    13

    Unanswered: Update query to change user password

    I am trying to use a form to update a user password in my user table. My form requires entry of username, old password, new password, and verification of new password. The code I'm using is below, and everything works except the update query part. I'm know very minimal VBA, and I'm not sure what is wrong. Any help? Thanks!


    Private Sub Command55_Click()
    'Check to see if data is entered into the UserName combo box

    If IsNull(Me.UName) Or Me.UName = "" Then
    MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
    Me.UName.SetFocus
    Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.oldPass) Or Me.oldPass = "" Then
    MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    Me.oldPass.SetFocus
    Exit Sub
    End If

    'Check value of password in tblUsers to see if this
    'matches value chosen in combo box

    If Me.oldPass.Value = DLookup("strEmpPassword", "tblUsers", _
    "[lngEmpID]=" & Me.UName.Value) And Me.newPass1.Value = Me.newPass2.Value Then

    CurrentDb.Execute ("UPDATE tblUsers SET tblUsers.strEmpPassword = " & Me.newPass1.Value & " WHERE tblUsers.lngEmpID = " & Me.UName.Value & "")
    MsgBox ("Your password has now been updated")
    DoCmd.Close

    Else
    MsgBox "Invalid Entry. Please Try Again", vbOKOnly, _
    "Invalid Entry!"
    Me.oldPass.SetFocus
    End If



    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    i would expect the problem is going to be because you don;t have the new values of user and password quoted


    CurrentDb.Execute ("UPDATE tblUsers SET tblUsers.strEmpPassword = '" Me.newPass1.Value & "' WHERE tblUsers.lngEmpID = '" & Me.UName.Value & "'")

    ut in the absence of any error message its hard to say
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    13

    still not working...

    Thank you for your reply. I tried your code, but is still not working. When I click the command button, it just gives me the error: Run time error 3464, Data type mismatch in criteria expression.

    Any other ideas on how to get this working?

    Thanks for your help, i really appreciate it.

  4. #4
    Join Date
    Jul 2009
    Posts
    13

    update on the update query code

    Hi, I realized an error in the code (my WHERE statment was incorrect), so I changed it to:

    CurrentDb.Execute ("UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.newPass1.Value & "' WHERE tblUsers.strEmpName = '" & Me.UName.Value & "'")

    Now everything is working smoothly except that the field strEmpPassword is not actually being updated in the tblUsers. ?? Why??

    Any help would be greatly appreciated...thanks!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is there a value in Me.newPass1.Value
    have you set a breakpoint on code and examined the SQL you are actually sending to the SQL engine?
    ms access breakpoint - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jul 2009
    Posts
    13

    is there another way

    Here's where my VBA ignorance is going to become really apparent...I'm not so familiar with the breakpoint thing. I did toggle it on to stop the code, but I don't really know what to do from there...

    I tried a test by removing the update query, and instead just inserting a message box that would display me.newpass1.value, and that worked fine.

    Any other ideas on how to update the data in that field? thanks again.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are developing in Access then you need to develop good debugging skills. the Access VBA editor is a very good and the debuggin capabilities are excellent

    read the supplied references, theres plenty there

    when you have problems with SQL interactiuons its important to make certain that the SQL you are sending is valid AND does what you think it does

    if you cannot understand the debugging techniques, such as setting a breakpoint then consider building the SQL and assigning it to a variable
    then execute the sql

    eg
    strSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" Me.newPass1.Value & "' WHERE tblUsers.lngEmpID = '" & Me.UName.Value & "'")

    CurrentDb.Execute (strSQL)

    you can then display the value of the sql by inserting a msgbox call

    strSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" Me.newPass1.Value & "' WHERE tblUsers.lngEmpID = '" & Me.UName.Value & "'")
    msgbox (strSQL)
    CurrentDb.Execute (strSQL)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jul 2009
    Posts
    13

    thanks

    Thanks for your help with this...appreciate the advice. I know you're right, I should probably take a class or something. At this point I know more about Access than I ever cared to...but not enough to be good at it... I would be ok with that for today if I could just get this dumb password to update. I'll keep working on it.

    thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •