Subform with Comboboxes

[Angate]

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

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??

[RuralGuy]

What displays in a ComboBox is controlled by the Columns property and the ColumnWidth property along with the RowSource of course. A ColumnWidth of 0"; will not display. In your case you would want 0" as the 2nd column.

[Angate]

I have the display set up just fine, the issue is that when I select from the combo box, i get the error:

"Cannot add record(s); join key of table 'ProjectSupervisors' not in recordset"

[TheShabz]

WHERE (((ProjectSupervisors .ProjectID)
You have a space before the .projectID. check to see if that's it.

[Angate]

I get the same issue. I did address the me.filter and changed it to [Forms].[Form].[control].....

What I believe the issue is is that the form's source is the supervisor table, and the sub forms source is the ProjectSupervisor and it is not finding where to put what or something?

[TheShabz]

me.cmbSupervisor (whatever you named the combobox that has it)