Limiting table input based on variable values

[MangoFruit]

Hi all,

I'm quite new to Access and am just working on a little practice database where I keep track of sales of a product.
Basically I have a table keeping track of the different types of products, a table keeping track of customers, a table for orders and one for replenishments of stock. I have a query that calculates the stock of each type of product based on replenishments and sales. Now I want to make sure that a customer can't order any more than there is in stock. I know you can restrict what's entered into a table with a validation rule but that's only for static restrictions. After searching the internet for an answer I've pretty much learned that I can't really make this restriction directly in the table, correct? But then how can I achieve this? Do I have to restrict the amount through a form somehow?

 

[Mihail]

I've pretty much learned that I can't really make this restriction directly in the table, correct?

Yes. Correct.

Do I have to restrict the amount through a form somehow?

Yes.

Use the Before Update event of the control for this.

 

[plog]

...a table for orders and one for replenishments of stock.

That's not how you should structure a database that you need to calculate stock in. All of your debits and credits should be in one table, that way, all you need to do is create a query to sum all those debits and credits to determine quantity currently in stock (or in stock no matter the timeframe: yesterday, last month, 7/4/1999).

 

[MangoFruit]

Thank you both for your feedback, I'll get to work on it and see how far I get.