Hidden dropdown items (2 Viewers)

[Danny]

Greetings,
I've a field called Status with dropdown items (below). I was tasked to hide dropdown items that are no longer in use.

Row source:

SELECT [tblSTATUS].[STATUSID], [tblSTATUS].[STATDESC] FROM tblSTATUS ORDER BY [STATDESC];

In order to hide the unused dropdown items, I used STATUSID <> 4 AND <>5.

The dropdown now shows open, pending, and closed as planned. However, for historical cases where the status dropdown was Referred or Triage, the dropdown shows blank.

How can I maintain the historical dropdown items when viewing the old cases?

TIA,

Regards

 

[MajP]

Change the rowsource in the on enter and on exit. In the on enter if new record then filter the choices. On the exit unfilter to show all.

 

[Pat Hartman]

I sort all the no longer used items to the bottom of the list to keep them out of the way but leave them there so the old records can show them.

However, I do have code in the BeforeUpdate event of the combo that prevents users from selecting inactive items. You need that also.

 

[DHookom]

I would add a column/field that stores whether or not the status is still in use. You should not be hard-coding <> 4 AND <>5.

 

[June7]

You said this is a field, are you viewing a table or a form? Need to use a form so you can control this.

Is form in Single or Continuous or Datasheet view?

Pat's solution may be simplest but if you really don't want to always show the archived values in list, here is another approach https://stackoverflow.com/questions...ous-form-the-second-combo-doesnt-show-its-val

 

[Danny]

Thanks everyone for your feedback.
I'll try your suggestions. Can you please share the syntax for both on ‘enter’ & on ‘exit’ and/or before update event?

June7

...are you viewing a table or a form? form
Is form in Single or Continuous or Datasheet view? Continuous

TIA

Regards,