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

 

[PieterLinden via AccessMonster.com]

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?!

Sort of...
The usual way to do it is to create a combobox with more than one column. In
your case, you would include the Price in the combobox's rowsource query.
Something like...
SELECT ProductID, ProductName, ProductPrice
FROM Product
WHERE...

and then in your combobox, set the column count to 3 and the column widths to
0;1;0
Then you can *reference* the third column of the Product combobox ...
cboProduct.columns(2) even though it's hidden. (The numbering of columns
starts with zero, so just subtract one from the column number...)

then you can make the price textbox be bound to the combobox... set the
rowsource to =cboProduct.columns(2)

 

[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