Lookup question

[Stacey]

I desperately need some help please. I have two tables:
rftbl_TestCodePrices
tbl_TestsOrdered

I have a subform where there is a combo box (cbo_TestName) that looks up the
test name from the rftbl_TestCodePrices table. I need the price of that to
update a text box (txt_Price) everytime the test name changes. I have tried
the DLookup and it seems to put the price on the next line. People will be
able to order multiple tests so this doesn't work correctly. Help please.

 

[John Vinson]

I desperately need some help please. I have two tables:
rftbl_TestCodePrices
tbl_TestsOrdered

I have a subform where there is a combo box (cbo_TestName) that looks up the
test name from the rftbl_TestCodePrices table. I need the price of that to
update a text box (txt_Price) everytime the test name changes. I have tried
the DLookup and it seems to put the price on the next line. People will be
able to order multiple tests so this doesn't work correctly. Help please.

Does TestsOrdered have a Price field? If txt_Price is unbound (i.e.
has no Control Source, or a DLookUp expression for a control source)
you'll get this effect, since every record *appears* to have its own
textbox, but there is really just one.

If you just want to *display* whatever the current price is, set the
txtPrice's Control Source to

=dboTestName.Column(n)

where (n) is the *zero based* subscript of the price field in the
combo box's RowSource query. Include the price along with the test
name in the query upon which the combo is based.

If, however, you want to *STORE* the price permanently - which might
be a good idea, as the price of the test might change and you'll want
to record the price actually charged at the time of the test - put
some VBA code in the combo box's AfterUpdate event:

Private Sub cbo_TestName_AfterUpdate()
Me!txtPrice = Me!cbo_TestName.Column(n)
End Sub

Again, (n) would be 2 if the price is in the third column of the
query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Stacey]

Thank you John. Storing the info is exactly what I wanted. You are a life
saver!