Northwind DB-Delete transactions and return stock when canceling order doesnt work (1 Viewer)

[MarRyb]

Hello experts,
I hope you are all well.

I'm currently working on Northwind sample database in Access 2013. I'm trying to learn as much as I can from this db and
also trying to make it work so my friend can use it for small business.

Everything works fine so far, but I stucked with one problem. Delete transactions and return stock when canceling order does not work and

as a result of this I will get incorrect stock numbers.

Problem description:


When I do new Customer Order, I pick one or more items from the list which are not in stock. This will show prompt telling
me if I want to create Purchase Order. That's fine, so I will create Purchase Order for selected products and will add them to my

inventory to fill items on back order. Up to this point everything works fine. However, when I decide to delete (cancel) this order,

database won't delete all data from Inventory Transaction table, all those back ordered items (which suppose to be deleted) are still

there. As a result of this I get incorrect numbers in my inventory which is populating into the rest of database, because these items

won't delete.

Note: Delete Order button will delete only items which have OrderID in the record in Inventory Transactions table.

Unfortunatelly
filled items form back order does not have OrderID in there. I hope there is some slution for this.

Can I ask you for some help or recommendation to overcome this issue please.

I will try to attach my version of Northwind db here fro you to make better picture of this issue.

Thank you very much fro any help.

Martin

 

[MarRyb]

Hi is here anyone who can solve this kind of issue. Will appreciate any help. Thank you

 

[Block]

Actually it is very easy to be done with one delete query and simple VBA code. If you still need answer I will be more than happy to provide you with one.

 

[jdraw]

MarRyb,

I have never used the 2013 Northwind database. Is your attachment the original or have you modified it.
Could you clarify?

Thanks.

 

[Block]

Ok so this the solution:
Table Inventory Transaction is responsible for calculating amounts in your stock.
That table is connected with table Orders through field Customer Order ID.
So create delete query with following SQL code and name it something like qrdelete:

DELETE [Inventory Transactions].*, [Inventory Transactions].[Customer Order ID]
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].[Customer Order ID])=[Forms]![Order List]![Order ID]));

Then change VBA code for OnClick action for Delete Order button so it looks like this:

Private Sub cmdDeleteOrder_Click()
    DoCmd.OpenQuery "qrdelete", acViewNormal, acEdit
    If IsNull(Me![Order ID]) Then
        Beep
    ElseIf Me![Status ID] = Shipped_CustomerOrder Or Me![Status ID] = Closed_CustomerOrder Then
        MsgBoxOKOnly CannotCancelShippedOrder
    ElseIf MsgBoxYesNo(CancelOrderConfirmPrompt) Then
        If CustomerOrders.Delete(Me![Order ID]) Then
            MsgBoxOKOnly CancelOrderSuccess
            eh.TryToCloseObject
        Else
            MsgBoxOKOnly CancelOrderFailure
        End If
    End If
End Sub

That is it, on click all transaction connected to specific order ID will be deleted and stock will be updated to before state, as well as delete specific order and order details.

 

[Dreamweaver]

I am currently working on my own version on Northwind which includes stock control I only post used stock when creating the invoice so before that point the order could be deleted without effecting the stock levels.