Delete transactions and return stock when canceling order - Northwind Database

[saudkazia]

I have built my Database from the Northwind database. One gaping problem is that cancelled order doesn't return stock. I tried to fix this my modifying the delete vba entry but for some reason it doesn't want to delete the order details or transactions.

Here is what I tried to use -



Dim rsw As New RecordSetWrapper
If rsw.OpenRecordset("inventory transactions", "[Customer Order ID] = " & OrderID) Then
Delete = rsw.Delete
ElseIf rsw.OpenRecordset("order details", "[Order ID] = " & OrderID) Then
Delete = rsw.Delete
ElseIf rsw.OpenRecordset("Orders", "[Order ID] = " & OrderID) Then
Delete = rsw.Delete
End If

What am I doing wong.

[JamesDeckert]

https://msdn.microsoft.com/en-us/lib...or=-2147217396
I haven't done this and the microsoft website doesn't show it, but you do have to do an update after you've changed data using dao.rs.
The MS site does a movenext which automatically does an update.
Try an rsw.update after the delete and if that doesn't fix the problem, see if the MS site can help.

[orange]

Please tell us more about RecordSetWrapper.

As for the Cancelled Order, I would have expected you would be required to Cancel the order, then return all items and quantities to the Items/Products table. It could be automated, but you would do it in some code attached to an event.

Just as you add items to an Order, or update stock when you receive Product from Supplier, I would think returning stock to Items/Products would be code you write --- for Cancelled Orders or Item Returns.

[saudkazia]

RecordSetWrapper is a SQL Recordset automation function in Northwind Database. I understood the issue after .movenext from JamesDeckert.

This worked perfectly.

Dim rsw As New RecordSetWrapper

 If rsw.OpenRecordset("order details", "[Order ID] = " & OrderID) Then
        With rsw.Recordset
            If Not .EOF Then
                Do While Not .EOF
                   Delete = rsw.Delete
                    
                    .MoveNext
                Loop
            End If
        End With
    End If
    If rsw.OpenRecordset("Inventory Transactions", "[Customer Order ID] = " & OrderID) Then
        With rsw.Recordset
            If Not .EOF Then
                Do While Not .EOF
                 Delete = rsw.Delete
                    
                    .MoveNext
                Loop
            End If
        End With
    End If
    Set rsw = Nothing
    


      If rsw.OpenRecordset("Orders", "[Order ID] = " & OrderID) Then
        Delete = rsw.Delete
    End If

Basically the problem was that the current rsw would delete only one record that matched. We need to to do a loop of all the matching records and delete them.

This site helped in the syntax for recordsetwrapper - https://github.com/jamiewest/VBA-Lib...cordsetWrapper

[orange]

Thanks. I don't do much with ADO and haven't seen this.