In Access 2003, I have a form with a combo box that is bound to a Projects table. I'd like the users to choose from a limited list of items to populate the Employee ID column that the combo box is bound to. Employee Name comes from another table and is linked to the Projects table via key fields. So I have set a lookup query as the row source for this combo box. It works well, except that I would like the available list for new record entries to be different than the list for old record entries.
For example, a new record entry has only active employees available in the drop down list. Terminated employees are no longer an option in this list. I filter the lookup query for active employees to accomplish this. However, old records will show a blank in this text box if they have a terminated employee saved in that column. (Because of the filter).
Can't I show the old data properly while still filtering on new records?
Any thoughts / suggestions are most appreciated.
For example, a new record entry has only active employees available in the drop down list. Terminated employees are no longer an option in this list. I filter the lookup query for active employees to accomplish this. However, old records will show a blank in this text box if they have a terminated employee saved in that column. (Because of the filter).
Can't I show the old data properly while still filtering on new records?
Any thoughts / suggestions are most appreciated.