Hi Guys,

ive got 2 tables one product with stock, and one with transactionline

Product has product id, productname, stock

transactionline has

transactionlineid, productid(fk),transactiontype, quantity

if the form has transactiontype 1 then will deduct stock from quantity

here is the vba code that i created(im a newbie) but gives me error at docmd.runsql mysql

Does this works? (vba with access 2007)
------------------------------------------------
dim yoursql,mysql,success as string

yoursql= "SELECT product.productid,Sum([TransactionLine].Quantity) as "&_
"SumofQuantity" & _
" FROM [producut] INNER JOIN TransactionLine ON " & _
"[product].ProductId = TransactionLine.ProductId " & _
" GROUP BY [product].ProductId"

DoCmd.RunSQL yoursql

If TransactionTypeId = 1 Then
mysql = "UPDATE Product SET Product.stock =" &_
"product.stock - [Sumofquantity] "

DoCmd.RunSQL mysql
MsgBox success


End If