UPdate Query not working

[chrisjames25]

Hi.

WIthin a form i have a command button. As part of the code I want to update a table that is not bound to the form.

THe update qry i have tried to code states the following:

CurrentDb.Execute "UPDATE Tbl_SalesOrderDetails SET Delivered_Qty = delivered_qty +" & Me.Txt_QtySupplied & "WHERE SalesDetails_ID=" & Me.Txt_SalesOrderDetails_ID, dbFailOnError

Basically when the company order say 2000 cups from a supplier, the supplier may send 500 one week and then 1500 the next. When the first 500 arrive I want to update the table to show 500 have arrived. So it would go to table, look at current amount deliverered and then add the new arrival to it. So in the example above I would initially have 0 delivered qty. THen when enter first input it would go tup to 500. THen when enter second input it would go to 2000.

THere is a reason I cant just bind this table to the form as this form is used another purpose.

I also run the code below to update when the deliveredq qty matches ordered:

CurrentDb.Execute "UPDATE Tbl_SalesOrderDetails SET Delivery_Complete = True WHERE SalesDetails_ID=" & Me.Txt_SalesOrderDetails_ID, dbFailOnError

And this one works fine so im just not sure why the top one doesmt.

ANy help massively appreciated.

 

[Gasman]

Put it all into a string variable.
Debug.Print that string variable.

Problem should then be obvious?

101 debugging.

Then when correct use that string variable in the Execute.

 

[MajP]

I see at least one. Lets assume txt_QtySupplied = 100

SET Delivered_Qty = delivered_qty +" & Me.Txt_QtySupplied & "WHERE SalesDetails_ID=" & Me.Txt_SalesOrderD
SET Delivered_Qty = delivered_qty +100WHERE SalesDetails_ID=123
space after an = is not required, but before words it is.

 

[Gasman]

Plus I doubt just

, dbFailOnError

is going to work?

 

[chrisjames25]

I see at least one. Lets assume txt_QtySupplied = 100

SET Delivered_Qty = delivered_qty +" & Me.Txt_QtySupplied & "WHERE SalesDetails_ID=" & Me.Txt_SalesOrderD
SET Delivered_Qty = delivered_qty +100WHERE SalesDetails_ID=123
space after an = is not required, but before words it is.

CHeers for this. I noticed this using gasmans approach and then corrected and now it all looks ok but still notworking. Saying syntax error now.

 

[Gasman]

Show us your debug.print

 

[chrisjames25]

Apologies I didnt know how to do a debug.print so I created a string and turned it into a message box so i could see what string looked like.

Cant beleive built whole database and never known how to use it

 

[Gasman]

In the code use
Debug.Print strSql
Where strSql is your string variable.
In the immediate window ctrl+g, copy and paste back here.
I suspect it will be the latter part that I posted.

 

[chrisjames25]

Brilliant, THis is what is appearring in immediate window:

UPDATE Tbl_SalesOrderDetails SET Delivered_Qty = 2,000 WHERE SalesDetails_ID=179

oh i should have said i streamlined the code a bit to the following:

Dim codeissue As String

codeissue = "UPDATE Tbl_SalesOrderDetails SET Delivered_Qty = " & Me.Txt_QtySupplied & " WHERE SalesDetails_ID=" & Me.Txt_SalesOrderDetails_ID
Debug.Print codeissue
MsgBox (codeissue)

 

[Gasman]

You should not have a comma in the amount.

 

[chrisjames25]

You should not have a comma in the amount.

Yep that was the trick. Cannot beleive a little separator caused my headache!!!!!!! Thank you so much.

 

[Pat Hartman]

Your problem is bigger than that. Updating an accumulator is a poor solution because it leaves no audit trail so if it is incorrect, there is no way to figure out the error. The better solution is to log "transactions so if an order is filled in three parts, you end up with three records which you can sum to find the quantity received.