combobox selection list not updating after each selection

[Twinnie]

Hello All,
I am designing a nomination form (web database so no vba macros can be used).
The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.
The form is bound to the tblSubmit table where the submissions are populated.
I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.
This is the select statement to populate the job level combobox: SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));
The select statement to populate the combobox for the nominee combobox: SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));
The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self.
There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"
Kindly help with a hint of how to get the checkbox selected for each corresponding staff.
Sample of the database has been attached.
Thank you

 

[JHB]

It seems like your zip file isn't valid, (I'm not able to open it), maybe therefore you haven't got any answers.

 

[Twinnie]

Hello JHB,

My apologies.

I have uploaded the sample db again.

Thanks

 

[JHB]

Replace what you have with the below, (database attached).

SELECT Staff_List.Staff_Name, Staff_List.Level FROM Staff_List WHERE Staff_List.Level=[forms]![frmtest]![cboLevel] AND Staff_List.Staff_Name<>[forms]![frmtest]![cboStaffName];

 

[Twinnie]

Hello JHB,

Thanks a lot.

It is working fine now.

I have also replicated same in my original database.

Thank you.