Hidden dropdown items

[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,

 

[Danny]

Thank you Pat/DHookom,
Given my OP sample, can you please guide me how to accomplish your suggestions?

TIA
Regards,

 

[Pat Hartman]

Add the ActiveYN flag to your table.
Change the combo's RowSource to a query. and include the ActiveYN flag.
Sort by the ActiveYN flag so the trues will sort first
Then sort by what will be the visible text value
Add a new column and name it Active. It's property should be
IIF(ActiveYN = true, null, "No") This gives the users a visible indication that this item is inactive. You mare the inactive rather than the active because there should be far fewer of them.

 

[MajP]

In single form view this is easy. Takes a little more work in continuous. This works for me in single form view

Private Sub cmboStatus_Enter()
  Dim strSql As String
  strSql = "SELECT tblStatus.StatusID, tblStatus.StadDesc FROM tblStatus where StatusID < 4 ORDER BY tblStatus.StatusID "
  Me.cmboStatus.RowSource = strSql
End Sub

Private Sub cmboStatus_Exit(Cancel As Integer)
 UnfilterList
End Sub

Private Sub Form_Current()
  UnfilterList
End Sub

Public Sub UnfilterList()
   Dim strSql As String
  strSql = "SELECT tblStatus.StatusID, tblStatus.StadDesc FROM tblStatus ORDER BY tblStatus.StatusID"
  Me.cmboStatus.RowSource = strSql
End Sub

See this work

You can see the last value, but it is not one of the choices. You no longer can select it.