By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,989 Members | 1,275 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,989 IT Pros & Developers. It's quick & easy.

Archive value for combo box.

P: 8
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
Apr 13 '08 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. select * from [lookup table] where archive = false OR luid=  ... [luid] value for the current record either as constant or form control reference ....

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?

Apr 13 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.