Query that does not show previously selected items

[poporacer]

I have a form that is used to input staff data. This form is opened from a form that tracks incidents. I have 3 associated tables. The first table (tblStaff)has the information on staff. The second table (tblIncidents) contains the relevant information to each incident. And the third table (tblStaffIncident)contains the information specific to the staff member in the incident. I have a function that pulls a global variable to hold the current incident number (getCurrentLog()) this is what happens, you select an incident from frmIncident and then click on a button to add staff to the incident. A form, frmAddStaff opens up and there is a combo box to select staff that are currently in the database. What I want this combobox to do is populate with all the records from tblStaff but to exclude staff that are already in the selected incident so that there is no way for duplicate staff entries. Each Staff has a unique ID number (CDCNum)
So I need a query that is somewhat like this:

SELECT * from tblStaff
where tblStaff.CDCNum<> (tblStaffIncident.CDCNum and LogNum= getCurrentLog()

So I need all the staff from tblStaff that do not have an entry in tblStaffIncident with a matching log number to getCurrentLog()

I am sure this is probably as clear as mud but hopefully you understand what I am trying to achieve.

 

[PHV]

SELECT * FROM tblStaff WHERE CDCNum Not In (SELECT CDCNum FROM tblStaffIncident WHERE LogNum=getCurrentLog())


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[poporacer]

Thanks....It works GREAT!!!!!