Include a fillable option in a calculated field?

[Metric]

Hello,

I am creating a table which, in part, associates costs with certain items. So I built an expression to generate costs:

IIf([Unit]="Unit A", 15,0) & IIf([Unit]="Unit B", 17,0)

To make Unit A come up as $15.00 and Unit B come up as $17.00 and so on. It works well for all the items I have solid costs for, but the problem is that sometimes things are not so conveniently categorized. I'd like to include an option to select 'Unit' as "Other" and then allow the user to type in the value themselves. Is there a way to do this within a calculated field?

Thanks!

 

[theDBguy]

Hi. A "calculated" field, by design, is not updateable (read only). You know how on some website forms you get to choose certain things and then there's an "Other" option, and when you choose it, there's an empty textbox where you'll enter what you want? Maybe you can do something like that. Just a thought...

 

[Metric]

You mean like adding an 'additional comments' section or something like that? I thought about that but I'd like to use the cost to move forward with other queries, etc. It may well be the only solution though.

 

[MajP]

You can set a default value to a field, but then the field is still editable.

 

[Pat Hartman]

IIf([Unit]="Unit A", 15,0) & IIf([Unit]="Unit B", 17,0)

This expression doesn't make sense. The & is a concatenation operator so the results of both IIf()'s end up in a single field.

So you have either 150 when unit is A
or
017 when unit is B

The second will look correct if the field is numeric which will suppress the leading 0 but the first should always look incorrect.

 

[June7]

Code behind form can fill in cost based on value in Unit field. This will allow user to edit cost. Can even set control locked based on unit value - use Conditional Formatting to enable/disable.

"Other" is not very informative - shouldn't this be a specific unit identifier? Select unit from combobox using a table as source. If not there then use NotInList event if you want to allow user to create new unit record.