Best form event to update stock

[kroeger]

Hi, I would appreciate some advice on the following:

I have an order form with an order details sub form. Both are bound to their respective tables and function correctly, with the OrderID being inserted automatically into the order details table.

However I am using VBA to update and check stock and have it working when attached to a separate form called process orders. Which event would be the best place to place this code. I do not really want to have the process orders form. Would it be on the master form or the sub form. I thought of placing it in the after insert event on the subform, but there can be many order items per order and it will run before I have finished adding order items. I tried it in the after insert event on the master form, but I think that this will run the code too soon as at this stage the order details will not have been written to that table.

 

[Christos99]

Hi, I would update the stock in the after update event of the order line form as you don't want someone else grabbing the stock (if it is multi user).
Just bear in mind you may have to reverse the stock allocation if the order is not completed or cancelled.

 

[kroeger]

thanks I will give it a go

 

[gemma-the-husky]

generally with inventory, you would not update stock or maintain an "as of now" balance.

merely keep a table of transactions, and calculate the balance whenever required. Seriously.

so stock balance is simply "sum of all transactions since the reference date". From time to time (eg monthly, or after a physical stock check) you would create a new "reference date balance"

 

[kroeger]

Thanks I can see the logic in doing it that way. So long as I know the starting balance I can then write a query to sum all of the order item quantities to get qty sold. The reason I need to reference a stock qty though is so that the order can be fulfilled or whether additional qty needs to be bought in if ordered qty is greater than on hand qty (though just thought I can get the on hand figure from the query). thanks again.