subform problem

[redblaze]

hi im having problems with a subform... i have the subform in an order form for which u can add many products. each product has a stock number which must be changed when an order is added. for example

order 1

product id= 1234
quantity = 2
stock = 8

because the subform can contain multiple lines in one order, i havent been able to set the stock of each product line in the order. it will only change the stock of the first line...is there any way of getting it to change the stock of each line in the subform?!

thanks for your help in advance!

 

[DBL]

If you are talking about saving the current stock level to a base table every time you take stock out then that's not the way Access is designed. Access isn't designed to hold dynamic information. You have all the information you need within your tables to tell you what the stock level is for each product at that precise moment and there's no need to save it to a table. Use queries to sum the stock that's come in for each product and the stock that's gone out and use another query to subtract the one from the other to show you the current stock level.

Hope that hasn't confused you more! There's mountains on the subject if you search the archives. It's just good database design.

HTH

 

[Pat Hartman]

The code to update stock levels does not belong in an event of the main form. It belongs in the AfterUpdate event of the subform. That way it is always working with the current row of the subform.