Remove Terminated Employee from Combo box selections

[Kiley]

I have an employee table, then I have a table that records new business into the company. I have a combo box in that new business form where the user can enter their name. The combo box is referencing the employee table. Is there a way, once an employee is terminated, to visibly remove their name from my combo box in the new business form without it removing their name from all associated records previously entered? I thought about an active/inactive checkbox in the employee table, but I still don't know that that would solve the name disappearing on the new business form once marked inactive... Has anybody else put together a scenario similar to this? How did you accomplish?

Thanks in advance!

[June7]

Certainly. The combobox RowSource should be independent of the report RecordSource. They should not both reference a query that has a filter to exclude the 'inactive' records.

[Micron]

Suggest a TerminateDate (date) field. If Null (blank) the employee is active (presuming you are only concerned with terminated and not sick leave). If a date is there, it not only tells you they are terminated, it provides the date of termination. Then the combo row source should include WHERE tblEmpl.TerminateDate Is Not Null.

[Kiley]

Thank you both. I am able to successfully remove inactive employees from the login form (which works as the filter on the new business form. The new business form's record source is the table for new business and is not the same record source as the combo box for the employee name (that record source is the employee table). So all that said - it is removing the names from the combo box, but in the new business form in all previously entered records, that employees name is now also removed if I've marked them inactive, and i do not want those records to change.

[June7]

Not understanding. Records would only be 'removed' if RecordSource or RowSource query has filter criteria to exclude inactive. Just applying a filter will not change record. If you want to provide db for analysis, follow instructions at bottom of my post.

[Kiley]

Sorry, maybe I wasn't wording my question properly. I found somebody else in another forum with this same issue and they worded it much better than I did:

"I have a combo box with a list of user names. The data source of the list is a table, tblUsers, and the property "Limit to list" is set to Yes, in order to avoid anything other than valid users to be entered. Over time, some users will become "inactive", e.g. leave the company. Now I would like to have a way to remove/hide these users from the combo box, without causing conflicts in the existing table. In other words, it should not be possible anymore to select an inactive user name from the list, but the existing records should remain unchanged."

Unfortunately that person did not find a solution either. But that is what I'm trying to accomplish.

[aytee111]

This is bringing to mind a solution I saw years ago. I will try and remember the steps.

There are two fields for the employee name, one on top of the other. The combobox has a transparent background and is the front/top object. Change the record source to include the employee name, saying IIf(employeeactive=True,"",employeename). This is a textbox field under the combobox and will display when the combobox is blank.

[June7]

Okay, a combobox with conditional RowSource and lookup alias does not work nice with Continuous or Datasheet view forms. Are you saying even if employee is inactive you still want to see the record on the form?

Aytee's approach of a textbox under a transparent combobox is one option and probably simplest. Set the textbox as Locked Yes and TabStop No.

Another possibility involves a UNION query as the combobox RowSource.

SELECT [tbxEmpID] AS EmpID, [tbxEmpName] AS EmpName FROM Table1
UNION SELECT EmpID, EmpName FROM Table1 WHERE IsActive = True;

In this approach, need code that requeries the combobox when navigating records. Use form OnCurrent event.

The form RecordSource would still need to include employee name and name really should be in multiple fields (FirstName, LastName). Adjust as appropriate for either approach.

[June7]

An alternate UNION which does not require tblEmpl in the form RecordSource:

SELECT EmpID, EmpName FROM tblEmpl WHERE EmpID = [EmpID]
UNION SELECT EmpID, EmpName FROM tblEmpl WHERE IsActive = True;

[orange]

You might consider adding a field to your employee table or where ever you show ActiveEmployees.
A flag or yesNo field say IsTerminatedYN. Then when you terminate an employee you set this flag to T/True
In you combo box, only show employees where IsTerminatedYN is not True.

In many systems records are never physically deleted. Instead, they use some sort of "logically deleted indicator".

This way you can keep all employee records (current, terminated, retired, deceased.....)