Managing contents of a combo box and keeping data

[Guest]

I have a lookup table with a list of employees and their status "active" or
Inactive". I have a combo box on a form that is run by a query that pulls
only "active" employees for the combo box. My problem is that if I change an
employees status to inactive not only do they disappear from the combo box
(which I want), but they also disappear from historical data if they were
ever selected on the form (which I don't want).

What happens now is that I select them from the combo box and leave the
record. Then I change their status in the table. When I go back into the
form the field is blank.

How can I take them out of the list, but still leave them in my data if
they've ever been selected from the combo box?

Thanks in advance for any help I can get!

 

[Guest]

Hi, Lesli.

Assuming your combo box *displays* the employee name, but *stores* the
EmployeeID, do the following:

- Create a query that contains all of the fields of the current recordsource
PLUS the displayed employee name from the Employees table, joined to your
main table by the EmpID.
- Change the form's RecordSource property to the name of the query.
- Add a textbox with no label directly over the existing combo box. Set the
following properties:
- TabStop = No
- ControlSource = The fieldname of the employee name to display
- Save the form

Because it is joined by EmpID, all existing records with Inactive employees
will still display, but because the control cannot be tabbed into, the combo
box will work normally, only allowing entries for currently active employees.

Hope that helps.
Sprinks

 

[Guest]

That doesn't sound too difficult. I'll try that this afternoon. Thanks!