row source of combo box, obsolete options

[hmlhml]

I am really embarrassed to have to ask this question, but it's puzzling me.

I have a number of combo boxes offering options, some of which have become obsolete. The row-sources for the boxes therefore have a WHERE clause along the lines: "WHERE mytable.obsolete=false". This is fine for making new entries, but it prevents the box from displaying old records correctly, with their obsolete selection. Is there any way I can modify this to allow the row-source to contain all entries that are not obsolete and also the existing entry even if it is obsolete?

I'd be very grateful for any help.

 

[dhookom]

I typically include them all but sort them so the obsolete are at the bottom and display "(obs)". You can add code to the after update of the combo box to check for the (obs) and warn the user.

Duane
Hook'D on Access
MS Access MVP

 

[hmlhml]

ooh, yes, thanks, that might well solve the issue. For many of the combo boxes I can sort on my obsolete field, and combine the existing description with "(obs)" using an expression as the combo box's control source. I have one or two which have NotInList events to add new items. I presume these won't work with an expression as control source, so I'll look at whether I can indicate "obsolete" by conditional formatting (greyed out, or something).
Thanks for that!

 

[hmlhml]

someone else has suggested I stick a text-box on top of the combo-box to display the value, the combo providing the drop-down. Seems a bit complex to me. I personally don't like controls on top of one another as I find it confusing when viewing the form in design view, but if it functions as expected, who am I to argue?
I prefer your version, dhookom!

 

[Domino2]

Sometimes I have set the position of a control programatically, so it is in it's correct position on form opening. Bit of a pain working out final position of the control to hard code in but clearer in design view. Only an idea.

 

[hmlhml]

Thanks Domino2, I never thought of moving controls programatically. New vistas open!
I still wish their were a more elegant way to deal with values that are no longer valid. Such is life.