I have a scenario where I’m assigning users and machines to a project.
This is done through a form and subform. The user selects the project and a week commencing date on the main form and can then assign a machine and a user on the subform.
User and Machine are separate comboboxes.

I’d like the list of machines available in the combobox to only include machines which have not already been selected that week.

So creating first record would show the full list available. Second record created would show all except the previously selected value. etc

I have already created records in the subform by just linking the user and machine comboboxes in the normal manner to their underlying source tables. i.e

cboMachines Row Source SELECT tblMachines.MachineID, tblMachines.MachineName FROM tblMachines;
cboUsers Row Source SELECT tblUsers.UserID, tblUsers.UserName FROM tblUsers;

These are bound to the underlying table tblWeeklyScheduleDetail

To change this and have cboMachines only include unassigned machines I have tried the following query qrycboMachines set as the Row Source on cboMachines.

SELECT tblMachines.MachineID, tblMachines.MachineName FROM tblMachines WHERE NOT EXISTS (SELECT tblWeeklyScheduleDetail.MachineID FROM tblWeeklyScheduleDetail WHERE tblWeeklyScheduleDetail.MachineID = tblMachines.MachineID AND tblWeeklyScheduleDetail.WeeklyScheduleID = [Forms]![frmWeeklySchedule]![WeeklyScheduleID]);

Result in Form view is that existing selected values are not displayed but cboMachines does correctly show remaining machines available when clicking on the drop down.

I reverted to SELECT tblMachines.MachineID, tblMachines.MachineName FROM tblMachines; to get the values back and applied qrycboMachines to the On Click event of cmbMachines

Private Sub cboMachines_Click() cboMachines.RowSource = qrycboMachines End Sub
Result is that existing values are displayed but nothing appears in cboMachines After a value is selected.

Is there a way to achieve the desired result?