Programming many-to-many relationship

[pademo57]

Hi There,

I have three tables:
Students
STUDID (pk)
txtFname
txtLname
etc.

Classes
CLASSID (pk)
txtClassName
txtClassRoom
etc.

Student_Class (join table)
STUD_CLASSID (pk)
fk_StudID
fk_ClassID

I am trying to set up a form which shows a "Class" and a subform which I can add a student to it. However, I keep getting errors and Access won't let me add a student. It will show me already added students.
What is the best way to set this up?

 

[pademo57]

Thanks Pat,
I did try, as you suggested, making a query of the Student_Class table and then a combo for StudentID but when I click the combo it doesn't show a list, instead it shows a one line blank?
What am I doing wrong?

 

[pademo57]

Hi Pat, again you were right, I had the wrong RecordSource. However, now it only shows people who have been assigned a course. It doesn't show people who haven't been assigned a course.

E.g.
Student 1- Class 101
Student 2 - Class 101
Student 3 - Class 202
Student 4 - no class assigned

Student 4 will not show up in the combo box. Now what have I done wrong?

 

[pademo57]

Hi Pat,
Not sure what I did with the first version but no matter what I tried it wouldn't work, so I scrapped the whole thing and started again and NOW it works. Thanks for all your help.