update SQL Query in VBA Using Variables

I am trying to use an update query but cannot get the syntax correct.  Please advise.  This is obviously my hardest thing to grasp (SQL Query syntax with variables).

For Each varItm In Me.lstVendorPartsList.ItemsSelected
        varPartID = Me.lstVendorPartsList.ItemData(varItm)

            DoCmd.SetWarnings False
            Dim sqlStr As String
            sqlStr = "UPDATE " & varTmpTableName1 & " SET " & varTmpTableName1 & ".[qtytopurchase] = " & varUpdateQty _
                        & " WHERE (((" & varTmpTableName1 & ".PartID)= " & varPartID

                       
                 'MsgBox sqlStr
                DoCmd.RunSQL sqlStr
            DoCmd.SetWarnings True
    Next
Lasers07Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try

sqlStr = "UPDATE [" & varTmpTableName1 & "] SET [qtytopurchase] = " & varUpdateQty _
                        & " WHERE PartID= " & varPartID
0
 
jerryb30Commented:
What are data types of varUpdateQTY, carPartID?
You need not be so explicit in naming the table every time you mention a field in the table.
sqlStr = "UPDATE " & varTmpTableName1 & " SET  [qtytopurchase] = " & varUpdateQty _
                        & " WHERE  PartID)= " & varPartID
0
 
Lasers07Author Commented:
Thanks Cap!  I think u are on a roll getting points off my syntax questions!  LOL.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.