Search code examples
ms-accessmany-to-many

Many to Many Relationship problem with MS Access

I'm building an Access database for one of my University's administrators to track faculty information. There's a section for tracking what faculty is on what committee. This is a many to many relationship, as faculty can be on more than on committee and committees obviously have more than one faculty. Here's a screenshot of my relationship with a Junction Table. I have a demographic table (faculty) with a committees table and a junction table in between them:

Many to many relationship

What I want to have is a Multi Select List Box populated with committees I can select to populate the Junction Table. This works if I have the List Box set to only have one selection. If I have it set to Multiple selections, I get an error that says "You must enter a value in the jctDemographicsCommitteess.committeeID" field.

Does anyone know how to get this to work?

The List box is in a subform with the Record Source being the junction table. Also, when I set the ListBox up, I set "Store that value in this field:" to CommitteeID.

Let me know if you need more information!

Solution

  • I think that using a set of combo boxes on a continuous subform is the way to go here.

    Start by creating a form that is based on the junction table In its properties, change the "Default View" from "Single Form" to "Continuous Forms". Add a combo box to this form, and set the Control Source to be CommitteeID, the RowSource to be based on the Committee table with the CommitteeID being the bound column. Set the Column Count to be 2, and the column widths to be "0;6".

    Go back to your form that is based on the Demographics table. On the toolbox there should be an option to insert a subform/subreport. Add it to the detail section of the Demographics form, and when the wizard starts guiding you through, select "Use an existing form", select the sub form that you have just created, and when asked which fields to use to link, select "DEM_ID" to "DemographicID".

    You should end up with something like this: enter image description here

    I've added a "Remove" button next to the combo box that simply has the following code:

    CurrentDb.Execute "DELETE * FROM Junction WHERE DemographicID=" & Me.DemographicID & " AND CommitteeID=" & Me.CommitteeID
Me.Requery

    Regards,