OK, let's be precise. Your question is slightly ambiguous to me, and I freely admit it might be my understanding that is the fault so don't take that as a bad comment on you.
On a table, you can put a validation if you wanted, but you should
never be storing calculated fields for inventory things. It is far too easy to desynchronize your on-hand quantity from reality. Search this forum for a huge number of threads on exactly this subject, "Store calculated field"
On a query, there is no place to put a validation, but a query is the right way to handle stock levels for most cases. With a good summation query across all transactions, you can have a highly accurate answer to on-hand levels.
The solution to your validation problem is that you put this test on the FORM, which is capable of looking at something if you place orders via a button-click. Let's say you make a list of things you'd like to place on the order invoice. So you build a sub-form and child table to hold invoice line items. You write some VBA in the OnClick routine of the button that says, "Place an order." Then the VBA does your validation by stepping through the selections in that invoice to compare against on-hand quantities. If you need to pop up the message box, the OnClick code is the prime place to do it.
For the sake of discussion, I'll describe how I would do this. Let's say I have a stock number I'll call SKU. My convention is that if I have stock on hand, my numbers will be positive and if I'm back-ordered, my numbers will be negative. I will have various transaction types. To take a fairly general case, let's say I have
- Stock Add - a shipment came in and has been placed into inventory
- Stock Pull - an order came in and stock was removed to fill an order
- Stock Bad - an attempt to pull stock found a defective item that cannot be retained in the on-hand count
- Stock Return - a pulled order was canceled and stock was returned
- Stock Adjust Up - after an inventory, more of this SKU was found than we thought we had
- Stock Adjust Down - after an inventory, less of this SKU was found than we thought we had
- Stock Archive Point - see discussion later
In one or more transaction tables, you have SKU, quantity, effective quantity, transaction type, transaction date, and perhaps a code number that identifies something about the transaction to which this is related - invoice number, shipment number, inventory special transaction number, etc. The effective quantity is merely the quantity of the transaction times either +1 if it puts things in stock or -1 if it takes things out of stock.
The amount of stock on hand is therefore (loosely stated):
SELECT SUM([EffectiveQty]) AS STOCKLEVEL from TRANSACTIONS GROUP BY [SKU];
So you look up the STOCKLEVEL for a given SKU. And that is the current level of stock for the SKU. Since you have dates in the transactions, you can also do historical studies by performing the same query but with a WHERE clause on the date field for summing transactions before a given date.
The transaction type for archive point is because you have this humongous table of transactions and you sometimes want to archive some of the oldest transactions. So you do the stock-level query for transactions up to the last date you want to archive. Replace ALL of the older transactions for the SKU with a single Archive-point transaction that shows the correct STOCKLEVEL for that SKU on that date. (Might have to do it in multiple steps.)
Anyway, the idea here is if you have something like this, you can simply do a DLookup of the value of the summation query, or you can do a direct DSum of the transaction tables. Your button-click code thus has an easy test to decide that SKU 123456987 can or cannot be pulled from stock.
If you cannot keep all transactions in the same table (and I understand that can happen), look at UNION queries as a way to "synthesize" a transaction table from selected fields (even possibly with different names) in many different contributing tables. Then take your sums from the UNION query. Access Help talks about UNION queries. They can be quite helpful in this case if you don't want to totally rewrite your tables.
