I have a situation of Tutors and Courses. To identify which Tutors can teach which Courses is a M:M.
Attached jpg show the Form with combos for Courses, and combo for Tutors.
The junction table is TutorCanTeachJunction. It has Id (autonumber PK) and a Unique index on CourseId and TutorId to prevent duplicates.
I'm sure you can use similar approach.
Good luck with your project.
PS:
Here is the code behind the "Add Tutor Teaches Course" button
Code:
'---------------------------------------------------------------------------------------
' Procedure : btnTeaches_Click
' Author : Jack
' Date : 12-10-2011
' Purpose : To populate TutorCouldTeachJunction Table with
' the id of the tutor and the id of the Course involved.
'Unique index on TutorId & CourseId to prevent duplicates.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Private Sub btnTeaches_Click()
On Error GoTo btnTeaches_Click_Error
On Error GoTo Err_btnTeaches_Click
Dim SQL As String
SQL = "Insert Into TutorCanTeachJunction(TutorId,CourseId) " _
& "VALUES (" & Me.Combo0 & "," & Me.Combo3 & ")"
Debug.Print SQL
CurrentDb.Execute SQL, dbFailOnError
Exit_btnTeaches_Click:
Exit Sub
Err_btnTeaches_Click:
MsgBox Err.Description
Resume Exit_btnTeaches_Click
On Error GoTo 0
Exit Sub
btnTeaches_Click_Error:
MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure btnTeaches_Click of VBA Document Form_frmTutorTeachesCourse"
End Sub