Obsolete Items in Combo Boxes

[Guest]

The main table in my Access 2002 application is called tblContracts. One of
the fields in this table is called ContractTypes, which is displayed as a
combo box in my main form, and is linked through a foreign key to the lookup
table tblContractTypes. Some contract types have become obsolete and users no
longer want to see them in the Contracts form combo box list.

I added an Active checkbox to tblContractTypes table, and added the criteria
"Active = True" to the SQL statement of the row source for the combo box in
the Contracts form.

This works, except that when we review older contracts with obsolete types,
the combo box displays a blank. If I change Limit to List to no, the obsolete
items are displayed. Even though I have referential integrity enforced
through a relationship, I would still rather have Limit to List turned on. Is
this the best I can do, or is there a better way to do this?

 

[Joan Wild]

Instead of adding a criteria of Active = True, sort the rowsource so that
true sort to the bottom of the list.

 

[Guest]

This is so obvious a solution, I don't know why I didn't think of it. Thanks,
Joan!

 

[Ron2006]

And maybe add column headings and sow the second column so they know
that they are Active or not.

Also in the before update event for the field add a test and if the
selection is NOT active then cancel the event and do a msgbox to the
effect.

Ron