Calculating cost from 2 tables

[stevemccauley]

I have 2 tables (ProductList and OrderList). The ProductList tbl has the unit price of the product and the OrderList has the quantity ordered. On my OrderList form I want to select the product name from a cbo box and enter the quantity, then have access take the unit price from ProductList tbl and perform a calc. How do I get the correct unit price on my form?

 

[charityg]

In the control source property of the textbox that will display the total price

=DLookup("[UnitPrice]", "ProductList", "[ProductID] = " & me!cboProduct) * me!txtQuantity

BTW, direct from Access help on DLookup Function:

You can also use the DLookup function in an expression in a calculated control on a form or report if the field that you need to display isn't in the record source on which your form or report is based. For example, suppose you have an Order Details form based on an Order Details table with a text box called ProductID that displays the ProductID field. To look up ProductName from a Products table based on the value in the text box, you could create another text box and set its ControlSource property to the following expression:

=DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID)