need help with formatting SQL for dropdown text box

Greetings And thank you for any help/advice you can offer.

I have a simple form where I need to track what jobs which volunteers are assigned to. The form is an “editable grid view” that contains three fields. Date, Job and employee. Both the Job and Employee are drop-down lists.

when creating a new entry, I want only the ‘active’ volunteers to be listed in the drop-down list.
In the form-field {empID} the select statement is => select * FROM employees WHERE active is null ORDER BY lName
However, because of the select is qualified by ‘WHERE active is null’, employee data from earlier jobs does not appear after the volunteer is de-active.

How do I SHOW the volunteer information (name and number -that are no longer active) in the “editable grid view” and NOT have de-active volunteers show on the drop-down list when I assign volunteers to job?

(tracking form is for a charitable organization that gets a lot of volunteers {empID} to perform various jobs. after X-number of weeks, they want to de-activate the volunteer so the list drop-down list does not get gigantic BUT still show which volunteers performs what jobs via historical lookup)

Thanks again for any help.

p.s. the ability to have code associated with event(s) associated with the specific field would be very simple… i.e. (sudo code) if {field} = newrecord then select only active employees else select all
This would allow terminated employee data to be visible in historical records and not be displayed in new record drop-down select lists.

See this link - it covers a similar requirement as I recall that should give you a steer in the right direction.

Thank you adz1111

I think your solution will work (have not tried it yet).

Stu Buck
Phoenix AZ