subform for order details

[John thomas]

I have created an orders form with sub form for details .I have to put a combo box on subform to obtain products but this will only alow me to put the product name in the products field i also need to get the cost in the form field .How do i do this
Thank you

 

[pbaldy]

The second method:

http://www.baldyweb.com/Autofill.htm

 

[John Big Booty]

So longs as the price forms part of the Row Source of your Combo you can use the following to populate an unbound text box;

=ComboName.Column([B][COLOR="Red"]X[/COLOR][/B])

Where X represents the column in your Combo (not necessarily visible) bearing in mind that the columns in a combo are numbered from zero on up.

Now I'm going to assume that your Sub Form is in continuous view so if this is correct this will not work properly. What I have done in the past to get around this, is to populate my Sub Form using a query that brings all your information together and does any calculations. I then use a pop up form in which the user can select the product and quantities and append them to the order, as products are added the subform is requeried to keep it up to date.

 

[pbaldy]

This is one of the times when I think storing the cost in the details table is appropriate. Otherwise, when the price changes and you change the price in the products table, you lose the actual price earlier sales were made at. There are alternatives, but I'd simply store the cost with the sale.

 

[John Big Booty]

You could always use a price history table, but I certainly agree storing the unit price is a whole lot easier

 

[pbaldy]

Yeah, the price history table is probably the pure normalized approach, but at some point depending on the number of products available and the volatility of price changes, it would become unmanageable.