How to Update Stock Levels when Ordering

[mosh]

Hi All,

I have just created my ordering form but I need help on two functions that needs to be on the form;

1. When I enter the quantity of products in the text box it should update the product stock level

2. If the product is not in stock to provide a notification that the product is not in stock.

Related Tables / Attributes has been attached. Just to explain the relationship - an Order can have many orderlines, an orderline can consist of many products of multiple quantity.

How can I go about updating the product stock_quantity via the order form?

Thanks for your help.
________
AnyWishes

 

[chergh]

You shouldn't have a stock quantity field.

If you need the stock level it should be calculated by subtracting your sales from the amount of stock you have received, this can be done easily with a query. With this query you can then display the stock level on your form with text box.

You can then use a simple if statement to ensure order quantity is < stock level. If order quantity is > stock level you can then have a message box flash up and tell the operator that there is insufficient stock, I would also add additional things at this point but that is dependant upon your company's process.

Anyway get rid of your stock quantity field as a first step and then look at

 

[carboxylic]

Hi this sounds like my problem.

This is my layout:

You suggest I remove the "Units in Stock Field"?

The North wind database seems to have kept it?

 

[mosh]

still need help, can anyone please have a look at my database?

Thanks
________
Iolite Vaporizer Manual

 

[gemma-the-husky]

mosh

take what chergh said as a guide

rather than actually trying to store the current stock quantity most systems actually work it out when its needed. its a bit swings and roundabouts, perhaps, but if you do it this way its bound to be right. if you try and work it out to store a current value, then every time you add an order, delete an order, modify an order etc you have to recalculate the quantities, and its very easy to miss something.

if you recalculate it each time, then when you need to place an order for a product, you need to run a query on that product that evaluates all the movements, which will give you what you the stock balance. What you need is an appropraite totals query to get this data.

Evaluating the stock this way means you dont have to do anything else when you take an order, since just by adding the item to the sales file, it will automatically be taken into account next time you calculate the stock balance.