Combo Boxes

[Don S]

I have a form Projects Add/Edit Form]![Lighting] (a tabbed form) with a combo
box cboTypePole1 which uses a query TypePole1qry to select “Desc†(text) and
“Price†(currency) from a table of products. This works fine. The SQL is
SELECT TypePole.Desc, TypePole.Price, TypePole.TypePole FROM TypePole ORDER
BY TypePole.TypePole;

I have another combo box cboPrice which I want to have the value of “Priceâ€
from that corresponds to the product selected in cboTypePole1. My SQL for
this is SELECT TypePole1qry.Price, TypePole1qry.Desc FROM TypePole1qry ORDER
BY TypePole1qry.Desc;

By using an After Update Event macro (RequeryTypePole,Condition
[Forms]![Projects Add/Edit Form]![Lighting].[Form]![TypePole1] Is Not Null,
RunCommand, Refresh) I have been able to get the query for the cboPrice combo
box to display only the “Price†which matches the “Desc†in cboTypePole1.
However I then have to manually select this value to populate cboPrice.

I have tried the “SetValue†action as part of the macro but without success.
Is there are way for me to set the value of cboPrice without having to
manually select it?

 

[Mr. B]

Don S,

From what I can tell by you description, you already have the Price field in
the query that is providing the data for your "cboTypePole1" combo box. In
any case, if the price is in the same query then that value is available from
the same combo box. You do not need another combo box. You can use just a
text box.

First, make sure that you have set the Column Count for your combo box to 2.
Set the Column Widths to a value like: 2";0" (the first value can be what
ever you need it to be, but the second number need to remain a zero if you do
not want to see the Price in the first combo box. These settings will cause
your first combo box to still just display the Desc field but its value will
be avaiable. If you are already showing all of three of the fields in your
combo box then that is Ok also.

Next create a text box and in the Control Source property of the text box use:
=Me.cboTypePole1.column(1)

The columns of a combo box us a zero based numbering system so to get the
value of the second field, we us a 1 for the column.

When you select the Description from the combo box, the Price will be
displayed in the text box. You may want to set the Locked property of the
text box to Yes so that the price value cannot be changed.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm