CODEWITHSUNDEEP
sqlvbams-access
cxk
cxk

Reputation: 27

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

Upvotes: 0

Views: 857

Answers (2)

Rene
Rene

Reputation: 1093

It's updating all records because your UPDATE statement says so. I don't know why you switched to your latest UPDATE statement. You should stick with the UPDATE statement as provided by Gustav.

Upvotes: 1

Gustav
Gustav

Reputation: 55841

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.

Upvotes: 0

Related Questions