Invoice query (quantities and sub totals)

[cnlifeasitis]

Hi

I am creating an invoice which has a subform of the products which the customer may purchase.

So within the invoice itself there is:

ProductID | Product_Name | Quantity | Unit_Cost | Line_Total


I am hoping to have the Product_Name as a drop down of all the products which are available and would like the Unit_Cost to automatically lookup the unit cost of the respective Product.
Could someone please help me as i dont know how to do it, at the moment its a case of the individual having to look at the unit cost within the Product_Name drop down, remember it and then enter it lol

Also, how do I have the Line_Total as a calculated field of the Quantity*Unit_Cost? Do I put it within the Order Details table (which handles the many-to-many) or within a query?


Any help or links directing me to answer would be great
Many thanks in advance

:: CNLIFEASITIS

 

[andrew93]

What have you done so far? What are the fields in your table of products? And what are the fields in your invoice items table? What you want to do can definitely be done - let us know what you have done so far so that we dont go over things you have covered already.
Andrew

 

[ScottGem]

Have you taken a look at the Northwinds sample database? The order form uses the same techniques that should work for you. In your case I would use the Column property. This means adding the Unit cost to the query behind the Product info. Your ProductName dropdown, should NOT be storing the product name, but the ProductID. The relevant properties of the combobox should look like this:

RowSource: SELECT ProductID, ProductName, UnitCost FROM tblProducts ORDER BY ProductName;
Bound column: 1
ControlSource: ProductID
Column Count: 3
Column widths: 0";2";0"

The result of this will store the ProductID for your joins, display the ProductName and include the UnitCost so you can use it. The final step is top use the After Update event of the combo you use the Code Builder to add this line of code:

Me!txtUnitcost = Me!cboProduct.Column(2)

This the set the UnitCost control to the the unit cost of the selected record in the Product combo.

Note: you need to use your own names for the tables, fields and controls. But you might want to do some research on naming conventions.

As for the Line Total, this should NOT be stored. As a general rule we do not store calculated fields since they can be calculated for display at any time. Where you do the claculation depends on your work flow. I would set the controlsource of the control on the subform to:

=[Quantity]*[UnitCost]

For reports I would do it in a query:
LineTotal: [Quantity]*[UnitCost]