Look up Combo box

[gt11990]

Hello,



Basically i am trying to create a stock control system.

I have two tables one with the products and their stock levels which are updated daily and the order table where orders are made according to the products in the products table with their stock level.

i have created a form for the orders and i have used a combo box to get the product names

SELECT [Products].Part, [Products].PartName FROM Products; - this is what i used for the combo box which is a table/query

my question is how can i have a field to be updated with the selected products stock so when ordering the stock can be seen


Thank you

[ryanmce92]

So you want the stock to appear whenever you select a product from the combo box?.

See the youtube video in the link shows you how to do cascading combo boxes

http://www.youtube.com/watch?v=SpMyGlEInGs

Slightly long video but perfectly shows you how to do this.

[gt11990]

that was great thank you helped me soo much may i ask you another question if you dont mind.

How can i decrease the stock every time a order is made

so the order details with the quantity is recorded into the order table

the current stock is on products table

how can the stocks on products table when a submission is inserted


Thank you

[orange]

If you are planning to do a database to support inventory Control, the you should read this material from Allen Browne.
It will help put concepts into focus.
Good luck.

[gt11990]

Hi thank You for your response however after looking in to the link you gave me i realized that this wouldn't work with my system.

Way mine works is,

order form product combo box gets information from products table. Once product is selected the stock level is displayed on another combo box which also gets the information from products table.

Once a product is selected and a quantity is inputted and submitted the details are recorded on orders table.


The products table is left intact however i want the quantity required box to decrease the stock level in products.

I hope this is clear and hope the images help

Thank you