Tip of the Month (September 2002)

Manually Overriding a Lookup Control

You can use a Lookup Control to retrieve a value from some other table and store it in a record. For example, once the user selects a PART_NUMBER for an order, you might lookup the LIST_PRICE in some other table.

In most cases, you would want to use the value retrieved. But, In some cases, you might want to give the user a chance to "override" the lookup value. For example, you may want to enter a price lower than list value.

To do this:

(1) Create another column in your table for the override value (for example, OVERRIDE_PRICE). Create an Edit Control over that column. Set the default value for that column to NULL.

(2) Create another column which will hold the original value if there is no override, or the override value if there is an override. You can use a calculated control to set this. In our example, the new column could be called ACTUAL_PRICE and the expression would be "if OVERRIDE_PRICE = 0 then LIST_PRICE else OVERRIDE_PRICE"

Elsewhere in your form, instead of using the original value, use the value created in step (2). In our example, you would use ACTUAL_PRICE for further calculations, reports, etc. rather than LIST_PRICE.