How to create a form to edit a one to many relationship?

Question

Using MS Access 2007, I am creating a student management database. I have tables for:

• Students
• Courses
• CourseSection

What I needed is a way to assign students to a course section. So I created a table that links students and sessions:

• StudentsInSection (contains foreign key to students and foreign key to course section)

I would like to create a form that allows me to edit a section and add students from the list of students. I've done lots of relational database work, just never with access. It seems like such a simple thing. Anybody know a good way to do this?

Answer 1

The easiest thing you can do is create a sub-form off a main form. Base the main form off of Students and base the sub-form off of StudentsSection. On the sub form, add a pull down for selecting the course.

That way the StudentSection will get populated with the StudentID (I assume that's your key) from the parent form, and you can set the CourseID when you set the pull down.

Answer 2

Create a form bound to the StudentsInSection table. If you want to be able to edit the students in the section, the easiest thing to do is to use a combo box with a row source based on the Students table with the bound column of the combo box tied to the student ID in the recordset underlying the StudentsInSection form. This form will be your subform. Set the Default View property of the form to "Datasheet" or "Continuous Forms". Save and close the form.

Create a form bound to the CourseSection table. From the Forms list, click and drag the first form based on the StudentsInSection table onto the design area of the CourseSection form.

In the property sheet of the new subform control, set the Link Master Fields property to the Section ID of your CourseSection table. In the Link Child Fields property, set the value to the Section ID of your StudentsInSection table. Setting these properties will filter the subform data to match the current value in the parent form as records are navigated.