ms access - Combobox selection items different from display items -

i have code table used in combobox. has attribute on each code called "isactive" has value of "y" or "n". code table combobox used classify transaction data on orders table. example:

  id  code       isactive   --  ---------- --------    1  repeat        y    2  new           y    3  discount      n 

i want list of items include items no longer active (isactive="n") text can displayed if looks @ old record used code no longer active. example if order last year classified "discount" want show when @ order.

however, want drop-down list new orders not display codes no longer active, since clutter display. if drop down includes codes isactive="y", order last year code of "discount" shows blank.

how can best of both worlds here?

if don't have particular row visible in combo list, combo won't display text corresponding combo's value - no exceptions.

however...

if sort isactive = "n" entries bottom, sort id or code usual, inactive entries won't clutter list (as much), still display correctly. done query bound combo's recordsource.

you can display isactive column users know can , can't select, , prevent selection of isactive = "n" entries in combo's beforeupdate event.