Selection in combo box automatically looks up another field?

[Zooper]

This is what I've got:
A Customer Equipment form with a subform.

The main form displays customer name and other info. The subform displays the purchased part, price, and quantity. The subform is in datasheet view.

The purchased part field, [Part Number], is a combo box that gets information from table Equipment List. The value chosen is stored in the table Purchased Equipment. The price and the quantity field are inputted manually.

---------------------------------------------------------
This is what I need it to do:
The user chooses a part from the [Part Number] combo box. The corresponding price for that part number is then automatically looked up and placed in the price field in the subform. (I have already created a table with all the part numbers and their respective prices)

Example:
Part A costs $100
Part B costs $30
Part C costs $150

I select Part A from the combo box in the Part Number field in the subform. The price field is then set to $100.


-------------------------------------------------------

I know this is possible, I've seen an exact example of it in a sample database. I tried to figure out how the sample did it, but when I tried to replicate it, it wouldn't work.

---------------------------------------------------------
This is what I tried:
I tried using a query for the subform and it seemed to look up the values, but I couldnt modify the contents inside the subform. (It would only look up ones that I had previously input manually via the Purchased Equipment table). I got the message "Recordset not updateable".
------------------------------------------------------------

I know what I am trying to do is simple. Please excuse my noobishness.

Thanks a bunch guys.

 

[SyedAqif]

Hi

Try this, On the Control source of the Item Price write

=DLookUp("[ItemPrice]","TblItems","[ItId] = " & [Forms]![MainFormName]![SubFormName]![ItId])

Cheers!
Aqif