Calculate a field based on combo box column

[whitjl143]

I have a form that is based on a query. On the form I have a combo box
'Material' that has 3 columns. I have another field on the form, 'OurPrice'
that references the 3rd column of the 'Material' combo box.

My problem is that I need to use the 'OurPrice' field to calcuate other
field on the form. I have the other calculated fields setup in the query but
don't know how to setup the query to see the 3rd column of the 'Material'
field on the form.

Thanks.

 

[Marshall Barton]

I have a form that is based on a query. On the form I have a combo box
'Material' that has 3 columns. I have another field on the form, 'OurPrice'
that references the 3rd column of the 'Material' combo box.

My problem is that I need to use the 'OurPrice' field to calcuate other
field on the form. I have the other calculated fields setup in the query but
don't know how to setup the query to see the 3rd column of the 'Material'
field on the form.

Join the combo box's row source table in the form's record
source query.

If you need help doing that, please post more information
about the two tables and hoe they are linked anlong with the
qurrent query.

 

[Ken Sheridan]

Jenn:

I don't see why you need to reference the combo box's column in the query at
all. Why not just reference it in a computed control on the form? Lets say
for example that you want to compute the price gross of tax from a net price
in the combo box's third column and the value in a field TaxRate, expressed
as a decimal (e.g. 0.175 for 17.5 percent), then a computed control's
ControlSource property would be:

=[Material].[Column](2) * (1+[TaxRate])

If you reference a control on a form as a parameter in a query to compute a
value in a column in the query the value of the parameter would apply
uniformly to all rows in the query's result set, whereas if you do the above
multiple values can be applied differentially to each record in the form,
which sounds like what you want to do here. But if not post back with more
details of what you do want to do.

BTW if you do need to reference an unbound column of a combo box in a query
you should not do so directly, but reference a separate computed control on
the form (your OurPrice control for instance) which in turn references the
combo box's Column property.

Ken Sheridan
Stafford, England