I am using Access 2007 and am making a form that contains another form. The form contains information about a supervisor, and the sub form is a list of projects that supervisor has or is working on. I have 3 tables here, the supervisor table, the projects table, and an intermediate table relating supervisors to projects by ID. What I want is for the combo boxes to show the Project name rather than it's ID, and I don't want projects the supervisor is already assigned to to show up.
I am having trouble with the combo boxes, If i make the project name visable, when i select one from the list, i get a prompt saying i did not select a valid project ID, and I have had no luck restricting the contents of the selection to those not already selected, I simply do not know where to draw the filter from the selected record of the main form.
Here is the SQL for the Row Source of the combo box
Code:
SELECT Projects.ProjectName, Projects.ProjectID
FROM Projects
INNER JOIN ProjectSupervisors
ON Projects.ProjectID = ProjectSupervisors.ProjectID
WHERE (((ProjectSupervisors .ProjectID) Not In
(SELECT ProjectSupervisors.ProjectID
FROM ProjectSupervisors
WHERE SupervisorID = me.filter)));
I also have the BoundColumn set to 2
Ideas??