Vba Access 2007, update stock from quantity

[thanosgr]

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

[Bill Crawley]

unless it's a type this will not help

FROM [producut]

should it not be

FROM [product]

[thanosgr]

its a mistake...
here is the code again

Private Sub button20_Click()
Dim mysql, yoursql, recsource, success As String
 
 DoCmd.OpenForm "sumqtr", acViewDesign
 recsource = Forms!sumqtr.RecordSource
 DoCmd.Close acForm, "sumqtr", acSaveNo 
 
 Dim cnn1 As ADODB.Connection
 Dim myrecordset As New ADODB.Recordset
 Set cnn1 = CurrentProject.Connection
 myrecordset.ActiveConnection = cnn1
 myrecordset.CursorType = adOpenDynamic
 myrecordset.LockType = adLockOptimistic
 
 
 yoursql = "SELECT Sum([TransactionLine].Quantity) as SumofQuantity" & _
            " FROM [" & recsource & "] INNER JOIN [TransactionLine] ON [product].ProductId = [TransactionLine].ProductId " & _
            " GROUP BY [product].ProductId"
         
myrecordset.Open yoursql
DoCmd.RunSQL yoursql
 
If TransactionTypeId = 1 Then
  
 
            mysql = "UPDATE product SET product.stock = product.stock" & _
            " - [yoursql]"
            DoCmd.RunSQL mysql
            success = MsgBox("records updated", vbOKOnly, "Success")
            MsgBox success
            
   End If

[Bill Crawley]

What is the error message? and if you place a breakpoint on the line what is the value of myrecordset after you have opened it?

If Myrecordset is populated, then I dont see the point of the DoCmd

also have a look at yoursql and check that it is well formed.