Deleting ComboBox lookup options but keep old records

[FlyingMonkey]

Hi

I have a ComboBox which looks up the records in a query. The query contains the names of current employees. The table upon which the query is based lists Employees and their start date and finish date. The Query is based on finish date being Null (ie they're still working here).

In the main form which details tasks, I would like the ComboBox to just list current employees (which it does as it's based on the query) but when an employee leaves, their name is deleted from all previous records.

Is there a way to keep the ComboBox options limited to current staff, while also keeping the names of previous employees in the form?

I checked the table which the form is based on and it still has the values, so I'd like to show in the form too as that's what people will be working on.

 

[FlyingMonkey]

I feel like I nearly have this working how I want it to.

I changed some options on the Property Sheet.

I changed 'Limit to List' from 'Yes' to 'No'. To do this I had to have both columns to the same width.

I also changed 'Show Only Row Source Values' from 'No' to 'Yes'.

This now has the affect of showing the EmployeeID and Name (from my query) in the drop down in my ComboBox. It also keeps historical info even though people may have left.

But as the EmployeeID is the bound column, it is this which displays in my Form. This is fine for running reports etc. But I would like the form to display the name instead.

Is this possible?