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?

 

[Pat Hartman]

The recordsource of the subform should be the Student_Class table (or preferably a query of that table) with a combo for StudentID so you can choose the student. Make sure the master/child links for the subform control are set correctly.

PS - I would ditch the prefixes that define data type although I don't object to using _fk as a suffix. When looking at a datasheet and when typing you will find the prefixes get in the way. You will have to expand the column width to actually see the meaningful part of the name and when you are typing, it takes more characters than it should before intellisense becomes helpful and you can tab to complete.

 

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

 

[Pat Hartman]

Either the RowSource for the combo is incorrect or one of its properties is set incorrectly. Check the column count and the bound column since those are the two usual culprets.

 

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

 

[Pat Hartman]

Sounds like the combo RowSource is still not correct. It should be the student table but it sounds like you are using the student_class table. The student_class table should be the RecordSource for the subform and the student table should be the RowSource for the combo.

 

[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.

 

[Pat Hartman]

It's easy to miss a setting so that's what I do if I can't see the problem. I just delete the control and add it again. Much less work