cxk cxk - 1 year ago 120
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
ElseIf .EOF Then
Exit Function
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)"
End If

Answer Source

Correct your SQL:

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

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.
End With
Set rsw = Nothing    
' Exit function now allowed.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download