Prev: Save Record
Next: Pull Data from Recordset
From: Rachel on 24 Feb 2010 07:30
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

"PieterLinden via AccessMonster.com" wrote:

> Rachel wrote:
> >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)
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201002/1
>
> .
>
 | 
Pages: 1
Prev: Save Record
Next: Pull Data from Recordset