Archive value for combo box.

[whitbacon]

I would like to have the ability to archive values in a lookup table. Take the following example

lookup table

luid, label, archive
1, green, No
2, red, No
3, blue, No

select * from lookup table where archive = false
I insert the above rowsource into a combo box. It works perfectly.

The users decide blue is no longer a valid color. You change the table as follows:

luid, label, archive
1, green, No
2, red, No
3, blue, yes

Now the old records that had blue selected show blank combo boxes!

in other words the value for a lookup table was once valid. It becomes invalid. I would like the user to see the value of what once was but no be able to select if for current records. I don't want to remove it from older records, but do not want it to be a valid response for future current records. I would like to make it user friendly. (fewer key strokes, not yelling at you for selecting an archived values)

Everything I can think of seems so contrived lots of buttons a combobox just for updating.

Thanks in advance

[FishVal]

Hi, whitbacon.

A general approach is dynamically change combobox RowSource in Current form event to fit combobox content to a current record context (certainly other situations, e.g. record editing, dealing with record context changing should be treated in the same way).

Combobox RowSource may look like

select * from [lookup table] where archive = false OR luid=  ... [luid] value for the current record either as constant or form control reference ....

BTW:

The users decide blue is no longer a valid color.

Would it make more sense to make [archive] a Date type field - a date when a colour was moved to archive? Also you may add additional date field - for colour introduction date. Thus you have a date frame when particular colour is valid. Combobox RowSource is filtered by a date in FK side to fit the frame. This way you get more flexible database design.

Does this make a sense?

Regards,
Fish