In a form, how to set conditions on drop down menu options?

[hungpuiki]

1. I am developing an inventory management app.

2. I created a form for recording inventory in/out activities.

3. After the user has encoded a specific product and has selected “Out”, the users should be allowed to select only locations which have available stocks only, but not all the locations.

4. Can we set conditions on the drop down options based on the stock quantity? and if possible, can the drop down menu show not only the locations but also the available quantity?

Many thxxx for your help!!

Tables (attributes):

1. Product (ProductID, Product_description)
2. Location (Location)
3. Transaction (in_out, Qty_change, ProductID, location, User, DateTime)