The problem you are describing wants a 3'rd option where the IsActive only offers 2 options.

To me it sounds like your solutions is to change the IsActive(Yes/No) into a Combo Box with additional options.  You'd have to modify any other query's, forms and reports... possibly VBA code.

Another option might be to add another Yes/No field to the Table and modify your queries to match.

-Raymond

Another option might be to change the IsActive field from a yes/no field to a numeric field (pretty much any numeric datatype other than "Byte") and then set the Triple State property in your form to yes.

then, in your combo box, where you check for IsActive, set it to <>0. This way, you'd have 3 options with one field.

Thanks for the replies. I need you to bring it down a notch, that developer badge is misleading, I'm almost done with Expert level. The TechHelp videos are just so much more FUN than class lol.

It isn't a normal checkbox. I used the code from this comment to make a large check box (https://599cd.com/blog/display-comment.asp?CommentID=52157). I treat it like a regular checkbox but if I'm understanding right, it might make triple state easier?

I would check out this video by Rick.  I think this will help you.

Using Triple State Check Boxes in Microsoft Access

-Raymond

Hi Monica, sorry if I jumped too far ahead.

Before we get too far, I guess, maybe what we proposed might be more than what you stated you need. The triple state video we shared will help accomplish the task in the way I mentioned, but it might be overkill now that I've re-read your post.

If all you want is for their name to show up in the combo boxes until they've been replaced, you don't need to do so much work. A simpler solution may be the one Richard offered in the Extended Cut of his Hide Inactive video.

With this method, you only need to slightly modify the Query or SQL behind the combo box row source you still want them to show up in until they are replaced.

@Donald thanks for your reply. The way mine is set up is as a combobox on a continuous subform, so there isn't a way to do the ID=ID to include the recently retired person. What it has gotten me thinking is to change the source of the combo to an unfiltered employee list and then use Me.NewRecord to somehow filter the dropdown. I haven't used Me.NewRecord before, is this possible?

If you filter the dropdown in any record, even me.newrecord, it might affect the appearance of all the other records. I don't understand how your combo box is set up I guess as it should be as simple as adding an "OR" criteria to the query (since it's already checking IsActive)

The problem is that even if I switch to my unfiltered query, I can't add the current contents of the combobox to it as an OR filter because the ID will always match the ID. I don't have a secondary location to reference, and if there was more than one manager, I don't think that would work either. It hasn't been an issue before, this was just something that would have been nice.

Can you do something in the filter or where part like:

WHERE IsActive = True OR ID = Forms!MYFORMNAME!ID

This would show it if it's Active OR if it equals the ID on the current record/row of you continous form