CODEWITHSUNDEEP
sqlsetwhere-clauseexecute
Helspont666
Helspont666

Reputation: 1

CurrentDb.Execute strSQL - Can't get table values to set to new user input

Access BD, VBA / SQL Newby -- Please forgive my ignorance

I have a form that should update a users password entered in to PWord1, and re-entered into PWOrd2. If they did everything correctly then the new password in PWord1 should be placed into table (users), and the check box pupreq should be set to false (True means they need a password reset), Where the user name in Username in the table is the same as in TempVars("UserNameTemp")

Private Sub UpDate_Click()

    Dim strRST
    strRST = 0
    
    Dim strSQL As String
    
    strSQL = "UPDATE users " & _
        "SET password = '""" & PWord1 & """, " & _
        "SET pupreq = """ & strRST & """, " & _
        "WHERE Username = """ & TempVars("UserNameTemp") & """"
    
    'set focus
    PWord2.SetFocus
    
    'What to do if PWord1 is missing
    If IsNull(PWord1) Then MsgBox ("PassWord 1 Missing"): Exit Sub
    
    'What to do if PWord2 is missing
    If IsNull(PWord2) Then MsgBox ("PassWord 2 Missing"): Exit Sub
    
    'what to do if PWord1 and PWord2 do not match
    If PWord1 <> PWord2 Then MsgBox ("PassWords do not Match"): Exit Sub
    
    'what to do if passwords do match-----------
    'Set the password in our user table to password 1 from the from, then set pupreq in the table to false, where tempvars is the same as the user name
    CurrentDb.Execute strSQL

    ' continue on to the logon screen
    DoCmd.OpenForm "LogOnScreen"
            
    'close current window - Update Password
    Me.Visible = False
    
    'MsgBox "hello world"

End Sub

I've tried to research how to use Execute and SET properly but I haven't been able to find anything that explains it in a manner I understand...

Upvotes: 0

Views: 29

Answers (1)

ValNik
ValNik

Reputation: 5916

Second SET and last comma unnecessary.

Try

    strSQL = "UPDATE users " & _
        "SET password = '""" & PWord1 & """, " & _
        " pupreq = """ & strRST & """  " & _
        "WHERE Username = """ & TempVars("UserNameTemp") & """"

Common query format

Update <table_name>
  set <field_name1>=<new_value1> ,
      <field_name2>=<new_value2> ,
      <field_name3>=<new_value3>
where <condition>

Upvotes: 0

Related Questions