Many to Many Relationship problem with MS Access

user_13283482 0
Viewed 257

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!

ms-access many-to-many
2 Answers

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,

user_79440270

I went and grabbed some code I wrote a long time ago to help. I cleaned it up a little bit but I left things for you to figrue out on your own. Im sorry Im lazy, but you got free code out of it. Its thoroughly documented with inline notes/comments.

Happy coding!

Private Sub SampleListBoxLoop()
    On Error GoTo SampleListBoxLoopErr
    Dim arr(0 To 4) As String ' you might need to vchange this too
    Dim lCol As Long, lRow As Long
    Dim vbsql As String

    With Me.ListBox
        'I wrote this in Access97. Not sure if listbox looping is still backwards or not - find out!
        'Looping through multi column list boxes is transposed.
        'Which is why we loop through columns first
        For lCol = 0 To .ColumnCount + 1 'needs to be plus due to column header
            If .Selected(lCol) Then
                'You'll need to change the next line based on your scenario. 
                'Since I gave you free code, you'lll have to figure this one out for yourself :P
                For lRow = 0 To 4 'this is to 4 due to Ubound of array and not row count since row count changes with each run
                    'Assign to the array transposed so that it is filled in normally.
                    arr(lRow) = Nz(.Column(lRow, lCol))
                Next lRow
            End If
        Next lCol

        'needed for deselecting
        For lRow = 0 To .ListCount - 1
            .Selected(lRow) = False
        Next lRow
    End With

    If arr(0) <> "" Then 'Cannot run this if empty array
        'Build your SQL statements herre
        CurrentDb.Execute vbsql, dbFailOnError
    End If  

    Me.Requery
    Me.ListBox.Requery
    Me.Subform.Requery

Exit_Sub:
    Exit Sub

SampleListBoxLoopErr:
    If DBEngine.Errors.Count > 1 Then
        'ODBC Error
        For Each errany In DBEngine.Errors
            msgbox "ODBCExecute: Err# " & errany.Number & " raised by " _
             & errany.Source & ": " & errany.Description, _
             vbCritical, "cmdExecuteAttached()"
        Next errany
    Else  'Access Error
        msgbox "ODBCExecute: Err# " & ERR.Number & " raised by " _
         & ERR.Source & ": " & ERR.Description, _
         vbCritical, "cmdExecuteAttached()"
    End If
    GoTo Exit_Sub
    Resume
End Sub
user_50092930
Related