Comination of 3 Combo boxes fills Text box

[Rachel]

Hi,
I have a subform 'OrderDetailsSubform' (datasheet view) on which I have 3
combo boxes - cboCategories, cboSizes, cboProducts. They are all synchronised
to filter down by selecting Product Category, Product Size, then the Product
itself. The subform's controlsource is a query OrderDetailsExtended.
The next field is an text box txtUnitPrice. I want this to populate with the
price for the product selected in the combo boxes.
I am stumped however, as the ProductID (which is what would distinctly
identify the product and the price) isn't actually selected in any of these
combo boxes.

I tried making cboProducts display the ProductID and ProductName but when
moving to the next row/record the previous row blanks out.

I hope this makes sense?!

How do I get this to work? I have tried placing a Dlookup in the
txtUnitPrice such as:
=DLookup("UnitPrice", "Products", "ProductName = " & [cboproducts]) but as
the are products of the same name but different sizes (and therefore
different prices) this wouldn't work.

I thought to of putting 3 criteria into the Dlookup (if thats possible) so
that Category, Size and ProductName must be found but have know idea how to
write it.

Then I guess there is a way to do this with a query but I can't get my head
around that!

Any help would be great!!!!

Rachel

 

[Rachel]

Thanks for your reply Pieter,
However this is similar to what I had tried already and I am getting the
same type of problem:
I changed the rowsource query to include the UnitPrice -
SELECT Products.ProductID, Products.ProductName, Products.UnitPrice
FROM Sizes INNER JOIN Products ON Sizes.SizeID=Products.ProductSize
WHERE (((Sizes.Size)=[cboSizes]));
and changed the column widths.
This works fairly well in that txtUnitPrice updates based on the third
column, however 2 problems:
1. The figure referenced from the third column fills into txtUnitPrice as
only a number eg 14 instead of $14.00 which is how txtUnitPrice is
formatted...
2. When I move to the next record cboProducts changes as I select a
different category and size and the previous record changes as well and I
lose the data - I know this is due to the rowsource but I don't know how to
get around it.
Is there a completely different way I should be doing this? Surely it is
possible I just don't know where to go from here.
Thanks again for your time and help.
Rachel