Update Query in VBA

[coyote]

Guys am in a jam.
I am trying to update qty in stock in table products.
ProductID and stock take qty are numeric.
I just want to update the quantites where stock take qty is greater than 0.

Am using the following code but am getting an error.

Dim strSql As String
Dim s As Long
Dim d As Long
s = Me.StockTakeDetails!StockTakeQty
d = Me.StockTakeDetails!ProductID
    
    strSql = "UPDATE Products " & _
             "SET Products.qty_in_hand=" & s & _
             " WHERE Products.product_id=" & d & " And s <> 0 & """
   With DoCmd
   DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
                .SetWarnings False
                .RunSQL strSql '
                .SetWarnings True
                
     End With

All help appreciated

 

[BamaColtsFan]

What error are you getting?

 

[MSAccessRookie]

Guys am in a jam.
I am trying to update qty in stock in table products.
ProductID and stock take qty are numeric.
I just want to update the quantites where stock take qty is greater than 0.

Am using the following code but am getting an error.

Dim strSql As String
Dim s As Long
Dim d As Long
s = Me.StockTakeDetails!StockTakeQty
d = Me.StockTakeDetails!ProductID
 
[B][COLOR=green]   strSql = "UPDATE Products " & _[/COLOR][/B]
[B][COLOR=green]            "SET Products.qty_in_hand=" & s & _[/COLOR][/B]
[B][COLOR=green]            " WHERE Products.product_id=" & d & " And[/COLOR] [COLOR=blue]" & [/COLOR][COLOR=blue]s &[/COLOR] [COLOR=green]"[/COLOR][COLOR=green]<>[/COLOR][COLOR=green] 0 & "[/COLOR][COLOR=red]""[/COLOR][/B]
   With DoCmd
   DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
                .SetWarnings False
                .RunSQL strSql '
                .SetWarnings True
 
     End With

All help appreciated

I have marked up your code above. The Code that is marked in Green seems Structurally OK. The Code that is marked in Blue needs to be added to perform what I believe is the intent of the Query. The Code that is marked in Red should no longer be required.

Note this is "Air Code and as yet untested, but I think it should work OK.

 

[Rich]

DoMenuItem has been obsolete for years, replace it with the RunCommand equivalents

 

[coyote]

MSAccessRookie I tried your solution but am getting a syntax error.
Any help gurus
Cheers

 

[DCrake]

strSql = "UPDATE Products " & _
"SET qty_in_hand=" & s & _
" WHERE product_id=" & d & " And StockTakeQty <> 0"

David