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
If IT.TransactionType <= 0 Then
ElseIf IT.InventoryID = m_cNew_InventoryID Then
ElseIf .EOF Then
![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]
SQL = "UPDATE Inventory " & _
"SET Inventory.Qty = Inventory.Qty + IT.Quantity" & _
"WHERE (Inventory.ItemID = IT.ProductID And Inventory.LocationID = IT.LocationID)"
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.