Select unused records in a listbox

[CoreyReynolds]

Hello all!

Thanks everyone for the great help you've given me so far on this forum - I have another intermediate question.

I currently have a form where a user chooses a piece of equipment, followed by a subform where they can add records to it which are different parts that piece of equipment can have, which is working good! The user just clicks a drop down, selects the compartment they want to add and they

My tables go tblEquipment (equipmentID and equipmentName) - tblCompartment (equipmentID and compartmentCode) -tblCompartmentList (compartmentCode and compartmentDescription)

My current rowsource for the dropdown box in my sub form is "SELECT tblCompartmentList.compartmentCode, tblCompartmentList.compartmentName
FROM tblCompartmentList;" and its Control Source is set to dump their selection into the compartment table.



My Question:
I only want compartments that have NOT yet been added to that specific equipment to show up in that drop down box, so that if the user say adds a transmission, then adds another record, transmission will not be an available selection.


Thanks in advance. Hopefully I have provided enough information but please let me know if you need anything else!