override default looked-up values

[Daniel]

Hi,

I've been trying to figure out a properly normalized and efficient way
to implement a system wherein:

-default values are looked up based on one or more fields. These
should be queried dynamically as they will change.
-it is possible to override these default values

An example:
Voltage is based on motor rating and drive type. However, the user
must be able to specify a different voltage under special
circumstances. The voltage might change if the categories associated
with motor ratings change.

Currently, I include a "voltage_override" field, and use an IIF
statement which determines whether voltage_override is null, but this
is quite slow and the majority of records leave this field empty.

This applies to (currently) about 7 fields, but it will probably apply
to more later.

Thank you,
Daniel

 

[Pat Hartman \(MVP\)]

If the value of these fields does NOT change if the lookup value changes,
then it is appropriate to store the value rather than look it up each time.
to do that, add the voltage column (for example) to the RowSource query for
the MotorRating. Then in the AfterUpdate event of the combo, store the
associated voltage value.

Me.txtVoltage = Me.cboMotorRating.Column(?)

Replace the ? with the column number of the column that contains the value
you want to store. Remember, the columns of a combo are a zero-based array.
That means that the first column is referenced as .Column(0), the second as
..Column(1), the third as .Column(2), etc.

It is not a violation of second normal form to store information you look up
when that information needs to stay static rather than change over time or
when you need to be able to override a value. By populating the bound field
in the AfterUpdate event of the combo, you store the value and allow it to
be overridden.

If for some reason, you want your overridden value to remain even if the
value selected from the combo changes, just check for null:

If IsNull(Me.txtVoltage) then
Me.txtVoltage = Me.cboMotorRating.Column(?)
End If

 

[Daniel]

Thank you for your comments. I will re-evaluate my needs to determine
whether I can mimic static lookup values.

I was hesitant to store those values, but it now seems like the most
logical approach.

Daniel