Combo Box based upon records in sub form

[caddcop]

I am working in Access 2003.


I have a sub form that lists employees assigned to a project. It allows the addition of more employees, but I have been asked to provide a list of all employees already assigned to the project to help keep track as employees are added.
I need to populate the compo box from a list of records that are already filtered by the subform. There is an ID field in the two tables that this data comes from.
So far, I have only been able to list all employees, not just those assigned to the current project - that is those on the sub form with the same ID as the main form.

[jzwp11]

Can you provide some details of your table structure relative to your form/combo box question?

[caddcop]

I just realized, my Tread Title is in error. I want a list box, not a combo box. Sorry. That said...
There is an ID column that is the primary link between the tables.
There are multiple tables, all containing Project information.
The main Project table has general information and the ID field is unique. There is only one record for any project in this table.
In the other tables, are staff members, firms, detailed descriptions. Those are just three of the other project tables. These tables can all have multiple entries under each ID as any project can involve multiple staff members, multiple firms and even multiple descriptions, as a description can be for individual tasks or for the overall project.
So on my "project staff" form, the main form lists the project ID, its Name, a few other fields from the main project table and then on its sub form, is information such as staff members full names, their work assignment on the project and a few other fields. The only records that show on this sub-form are those with the same ID as the project on the main form. But while looking at one staff member, you cannot see who else is listed as a staff member. And as soon as you select to add a new member, you cannot see any staff members. To avoid any confusion, I was asked to provide a list, on the form, of any existing staff members.
Hopefully, that is sufficient information.
I did figure out that I could create a second sub-form and display it only in data sheet mode, but a listbox would be more appropriate.

[jzwp11]

Assuming that a staff member can work on multiple projects, I am guessing that your tables look something like this:

tblProjects
-pkProjectID primary key, autonumber
-txtProjectName
other fields


tblEmpoyees
-pkEmpID primary key, autonumber
-txtFName
-txtLName


tblProjectStaffMembers
-pkProjStaffMemberID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkEmpID foreign key to tblEmployees

Then in the list box you want to display all staff member/employees who are not currently assigned to the particular project. Correct?

If so, then you need the list box's row source to filter out those employees already assigned to the project; the query to do that would look like this (you will have to substitute your own table, field, form and textbox control names):

SELECT tblEmployees.pkEmpID, tblEmployees.txtLName, T_Employees.txtFName
FROM tblEmployees
WHERE (((tblEmployees.pkEmpID) Not In (SELECT
tblProjectStaffMembers.fkEmpID from
tblProjectStaffMembers where
tblProjectStaffMembers.fkProjectID=forms!yourmainf orm!NameOfTextBoxThatHoldstheProjectID)))
ORDER BY tblEmployees.txtLName, tblEmployees.txtFName;