Many-to-Many Form Input

[Shifty10]

Good Evening,

For some reason I am missing the concept of how to create a form for a Many-to-Many Relationship. I can see the idea, but just cant quite put it into practice. Here is what I have:

Student Table:
StudentID (primary key), Name, etc.

Course Table:
CourseID (primary key), CourseName, etc.

Link table:
LinkID (primary key), StudentID (primary Key), CourseID (primary key)

One student can be enrolled in many courses, while each course can have multiple students enrolled.

I am looking for how to input, via a form, which students are enrolled in which courses. I know it has something to do with a form and subform, but not quite clear on how to do it.

any help would be greatly appreciated!

 

[Alansidman]

Look here:
http://www.techrepublic.com/article/accommodating-a-many-to-many-relationship-in-access/5285168

 

[Shifty10]

The link actually helped a lot, thank you. Unfortunately, it also made me realize that I posed the problem incorrectly. To better explain what I am trying to do, I have a group of students, we'll say 100 for example. There is a certain list of classes they are required to take/attend and they must take all of them. Additionally, I would like to track when each class was completed (date). Here are the three tables currently:

Student Table:
StudentID (PK), LastName, FirstName

Course Table:
CourseID (PK), CourseName, CourseCode,

Link Table:
LinkID (PK), StudentID (FK), CourseID (FK), Complete (yes/no), CompletionDate

Following the guidance from the link you provided, I created the query below:

qryCourseCompletion:
CourseName, CourseCode, StudentID, Complete, CompleteDate, LastName, FirstName

I then created a form based on the query where the main form is linked to the Student (displaying last name and first name) and the subform is linked to the Course. What I would like to do is, upon selecting a student record in the main form, display all courses simultaneously in the subform while having the ability to edit the completion and completion date fields. I hope this makes sense, I am currently unable to upload an image of my query or form.

Is this possible/does my issue make sense?

 

[jdraw]

You should be able to upload an image if you make it a ZIP file.


Also there is a free youtube video on many to many at

https://www.youtube.com/watch?v=7XstSSyG8fw

 

[Mr. B]

Yes, it is possible to do exactly what you want to do but a sub-form is not required here. You first need a way to assign or link students to the courses they are signed up for, so you do need the table to hold a record for the Student record ID and the Course record ID for each course. This table could also hold other data directly related to this linked record.

You must have a way to manage the creation or editing of each of the many-to-many records that will exist in this table. To do this follow the general steps below:
​

Create a form and use your many-to-many table as the record source for this form. (More on this later.)

Create a listbox and have it display the list of students. The StudentID should be the bound value for this listbox.

Then create another listbox showing all available courses that have not already been linked to the student selected in the Students listbox. The CourseID would be the bound value for this listbox. This listbox should have the "Multi-select" property set to "Simple". This will allow the user to select multiple courses and have the selected courses linked to the selected student (VBA code will be required to actually create each record in the many-to-many table).

Create another listbox that will display only the courses that the student selected from the list of Students is signed up for. This listbox will use the record ID from the many-to-many table as the bound value.

You would then be able to have a button on the form that is available (enabled) only when a Student has been selected and at least one course has been selected from the Courses list box. When this button is clicked, the VBA code mentioned above would create a record in the Many-to-Many table containing the Student record ID, the Course record ID and any other related information for each of the Courses selected from the Courses listbox.

The listbox that displays the list of Courses already linked to the Student would then be requeried and would immediately show all of the selected Students courses.

You would also need a button that would only be available (enabled) when the user has selected a Course that is linked to the selected Student that, when necessary, will allow the record that links the selected student with the selected course to be removed from the many-to-many table.

Then when you need to update anything about the record for the student and one of that student's specific course, users can simply select a student and a course that is already linked to the selected student and you can then display controls on this same form (not a subform) that will allow the user to update the information as required. This form would use the many-to-many linking table as its record source.

This is the way I would approach the management of a many-to-many relationship.

As you work through the process described above, post back with your specific questions and someone will be glad to try to help.

 

[Shifty10]

Thanks for the advice. Ive been busy at work so i am only now getting a chance to get back to it, but I did encounter an issue that i am experimenting with right now.

When I create the listboxes that you reccommended, I use the wizard. In there, it has one section where you select, store values in a field or remember for later use. which is the option i would need to use to accomplish this? As i said, im playing around with both right now trying to figure it out but a

ny further guidance would be greatly appreciated.

 

[Mr. B]

For the purpose you are going to be using the list boxes for, you can just select the "remember for later use" option. You will be reading the information about the selected values later when you are ready to create the record in the linking table.