Handle NotActive records

[Thinh]

How do you handle Non Active entries from the past past?
Sample exemple.

EmployeeID: AutoNumber
EmployeeNr: Number
EmployeeName: Text
NotActive: Yes/No

I have a combo box in one form that utilize the above table to allow user to select only active employee.
my queries is select EmployeeID, EmployeeName from tblEmployee where NotActive = 0

the problem i face is if a user got terminated today, all entries in the past reference that employeeID will be blank. the value is still in the table but the combo box wont see it since NotActive = -1 for that EmployeeID.

How do i handle this case. Any suggestion is greatly appreciated.

 

[gemma-the-husky]

this is always an issue

in some cases i have used two queries to drive the combo box

1. shows all employees
2. shows active employees

for a new record, i use query 2
for an existing record, i use query 1


this is aircode, but something like

form current event

if me.newrecord then
  employeecombo.recordsource = "query2"
else
 employeecombo.recordsource = "query1"
end if

 

[Thinh]

thank that will solve it but that leave all options open if a user need to edit the drop down.

I guess you cant have it all, this solution is good enought.