order of macros/vb

[nicwnacw]

i have a form stock which records stocks codes/price/stock in hand and reorder levels. I have a macro (not very good at VB) on the Before Update property of stock in hand that checks if enough stock is available, I have some code on the On Exit property of stock in hand that deducts sales from stock.

both work fine, to test I am entering a quantity that makes stock below reorder level, the macro produces the right message, BUT the stock is still removed giving me a minus balance.

What have I done wrong?

 

[Jack Cowley]

If you delete stock in the On Exit event of the same control in which you check stock then unless you ask if the user really wants to remove the stock the stock will be removed automatically. At least that is what it sounds like you are doing. If, in fact, that is what you are doing then you need to ask the user if they want to delete the stock and if they do then delete it. If they say no then don't delete it. I don't do macros anymore, but you want to do this check in the same macro that deletes the stock.

hth,
Jack

 

[Pat Hartman]

The On Exit event is not the place to do any updating. what if someone just tabbed into the field and then tabbed out without actually changing anything? You'd still update the table. You should check the stock level in the quantity control's BeforeUpdate event. If there is insufficient stock, you need to cancel the update. In the Form's AfterUpdate event, you can update the stock quantity.

HOWEVER, your approach to inventory management is too simplistic. EVERY update has some effect on inventory levels. If the user changes the ordered quantity from 1 to 2, that change needs to be applied. But not as a quantity of 2 but as the difference which is 1. You also need to deal with record deletions. There are several references posted here to good articles on inventory management. Use the search feature to find them.

 

[nicwnacw]

Order of macros

thanks guys. I knew the logic was wrong but couldn't see where. I have circumvented the problem by now showing stock levels on the order form (after discussing with client) so that staff using the system can see what is available.