cxk cxk - 4 months ago 28
SQL Question

Access SQL update into another table in VBA from a function

I have a function that puts values into a table called InventoryTransaction and that works perfectly fine. However, I need to update another table using the record that was added into InventoryTransaction.
But I'm having difficulty trying to update the values it keeps giving me an error, any guidance will be appreciated

The error message is: "Undefined function 'IT.QuantityWHERE' in expression"

Function EditTransaction(IT As InventoryTransaction, Optional CustomerOrderID, Optional PurchaseOrderID) As Boolean
Dim rsw As New RecordSetWrapper
Dim SQL As String




If rsw.OpenRecordset("InventoryTransaction", "[TransactionID] = " & IT.InventoryID) Then
With rsw.Recordset
If IT.TransactionType <= 0 Then
Exit Function
ElseIf IT.InventoryID = m_cNew_InventoryID Then
rsw.AddNew
ElseIf .EOF Then
Exit Function
Else
rsw.Edit
End If


![ItemID] = IT.ProductID
![TransactionQty] = IT.Quantity
![TransactionType] = IT.TransactionType
![LocationID] = IT.LocationID
![Time] = Now()

EditTransaction = rsw.Update

If IT.InventoryID = m_cNew_InventoryID Then
rsw.Recordset.Bookmark = rsw.Recordset.LastModified
IT.InventoryID = ![TransactionID]
End If


End With

SQL = "UPDATE Inventory " & _
"SET Inventory.Qty = Inventory.Qty + IT.Quantity" & _
"WHERE (Inventory.ItemID = IT.ProductID And Inventory.LocationID = IT.LocationID)"
DoCmd.RunSQL SQL
End If

Answer

Correct your SQL:

SQL = "UPDATE Inventory " & _
      "SET Inventory.Qty = Inventory.Qty + IT.Quantity " & _
      "WHERE (Inventory.ItemID = IT.ProductID And Inventory.LocationID = IT.LocationID)"
DoCmd.RunSQL SQL

Also, you should under no circumstances exit the function while the recordset is open:

With rsw.Recordset
    ' Do stuff.
    ' Don't exit function.
    ' Do more stuff.
    .Close
End With
Set rsw = Nothing    
' Exit function now allowed.