Combo box supporting active/inactive choices

[mscertified]

I have a combo box that displays table entres which have an associated
active/inactive switch. The query is currently:
SELECT CodeDesc From tblCodes WHERE ActiveSW = True

However, when accessing an old record that has an inactive value, I still
need to display the value in the combo box. How can this be done?

 

[KARL DEWEY]

I select all records and use two sorts. First sort on Active/Inactive and
then on the other field.
SELECT CodeDesc From tblCodes ORDER BY ActiveSW DESC, CodeDesc

 

[mscertified]

But I don't want to allow an inactive value to be selected; do you validate
that elsewhere?

 

[Klatuu]

Your statements are contradictory. You said
"when accessing an old record that has an inactive value, I still need to
display the value in the combo box."

Then you said:
But I don't want to allow an inactive value to be selected

What is it you really want?

 

[mscertified]

Exactly what I said, I want all values to display in the combo box (NOT in
the drop-down), but only active values to be selectable.

 

[KARL DEWEY]

I do not know of a way for you to have your cake and it also in this case.

My solution allows them to be displayed if they were selected before they
went inactive. The double sort puts all inactive at the bottom of the list
when selecting.

 

[Klatuu]

okay, explain the difference between displaying in the combo box and NOT in
the dropdown, please.
I understand a combo is a combo because it consists of a blend of a list box
and a text box, but I am still not clear on the purpose.

I guess you could use the After Update event of the combo to test to see if
the item is active or inactive and present a message box if it is inactive.

 

[MWS]

What he wants is to display what was selected before it went inactive but not
allow them to select anything else that is currently inactive. This prevents
displaying a empty combobox when the item becomes inactive because the value
in the record has nothing to bind to in the combobox.

HTH
Mitch

 

[KitCaz]

I understand the need and I did as everyone here described--all entries
display in my dropdown list because the rowsource query includes them all,
but it does sort the inactive items to the bottom.

Then I added the field which contains my active/inactive indicator as a
column in the dropdown, with a 0 width. Lastly, in my field's before_update
event, I check that column's value and refuse the update if the user chooses
an inactive row (e.g.,
If field.column(n)='Inactive' then cancel=true, field.undo, that sort of
thing).

So a field which already has an old value will show it, but subsequent
changes to select an old value, whether in the current record or some other,
will prevent selection.

 

[KARL DEWEY]

I just added the Active/Inactive field in the Row Source, displaying it to
the right, and includes sort on the Active/Inactive field and my primary
field.