Stock Level Warning

[ChrisTheAncient]

I'm plodding away making a database (based on NWinds) to make a control system for a few products I want to sell.

I use an Order Form, with a subForm of ordered items (just like NWinds) and have coded the subForm so that it will decrement the stock of an item each time one is ordered.

So far so good.

Using a query as a test, I can soon work out whether stock in hand of an item is at, or below, reorder level.

Now the crunch...

When the database is closed at the end of work session (or started at the beginning of a work session - but I would prefer the former), I would like a warning to pop up to inform the user (usually me) that an item has fallen to, or gone below, re-order level. It would be nice if that warning could indicate the item(s) to be restocked.

My vb and coding is basically 'noddy' level and I can understand very simple, monosyllabic instructions if they are spelt out s-l-o-w-l-y.

Any ideas or help at hand, please folks?

TIA

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[Golom]

Don't know your table structures ... or much else about your application ... but, assuming

[tt]tblStock
StockCode InStock MinQty MaxQty
123 2 5 10
234 4 2 5
456 0 5 8
[/tt]
The following query will display the items to be ordered and the number to order to bring levels to the MaxQty
[blue][tt]
Select StockCode, InStock,
(MaxQty - InStock) As [QuantityToOrder]

From tblStock

Where InStock <= MinQty
[/tt][/blue]
You could run some variant of this when you are exiting the application to display the items to be restocked.

 

[ChrisTheAncient]

Hi Golom

Thanks for that! I think I can see the way forward now, with your help!

I suspect that I will run an 'on quit' style macro to run a form that is based on that query and immediately close the form it there are no levels to top up.

Many thanks

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************