Lookup List Management

[emberk]

I have an Access 2013 database for tracking financial information. I am utilizing several lookup lists for ease of data entry and consistency of the entries. Users have the ability to add items to any one of the lookup lists. Some of these lists are getting pretty long and contain items that are no longer needed. I am seeking a way to "hide" a specific list item so that it is not an option when entering new records. I have successfully added a "Hide" column to the lookup list which is a checkbox. Users can check the box if the item should no longer be selected. I then added to my combo box on the form, the condition that the field should only show non-hidden items. This works great, however, if I navigate to a record that used said hidden item, the field is blank.



I am missing the piece that will allow a lookup list item to be hidden, but still display for any record that previously used that item. Hope this makes sense. I have tried searching for an answer, but have been unsuccessful (probably due to my inability to use the proper terms needed for this functionality). Example of layout below.

tblProperty (lists all our properties)

PropID PropName PropHide
1 PropertyA No
2 PropertyB Yes

frmEvent

EventID Property (combo box linked to tblProperty)
1 PropertyA
2 Blank (s/b PropertyB but isn't shown due to combo box query limiting results to show only non-hidden items)

I feel like I need two "property" fields on the form, one so users can select a non-hidden property and one to permanently hold the PropID or PropName so that the information would be visible even if the Property was marked hidden at some point.

Any help or direction would be appreciated.

[ranman256]

yes many of my combos use the HIDE column. (but not that extra property)
I also have a filter combo, that allows users to pick the data in the combo, from a table tQrys2Flt
Short List, qsItemsShort
All items, qsItemsAll

users can use the default Short list, but change it to ALL items, etc

cboFlt_afterupate()
   cboInv.recordsource = cboFlt
end sub

[emberk]

Thank you for your response. Two questions:

1st - So on your form you have two fields, 1 that pulls from either Short List or All Items and 1 that stores the item selected?

2nd - If you don't have an extra "Hide" column in the lookup list, how are items hidden?