Use Form to Add Bulk Records to Junction Table

[Jenaccess]

Hi,

This question is in regards to Access 2007, but I'm sure advice would be applicable for all versions. I hope so anyway .

I have three main tables I'm working with: Student, Service, and StudentService (which is a junction table for Student and Service). Here is the structure:

Student Table
StudentID (pk)
LastName
FirstName

ServiceTable
ServiceID (pk)
ServiceName
ServiceDate

StudentService Table
StudentServiceID (pk)
StudentID (fk)
ServiceID (fk)


So far, I've managed to use a form to create services in the Service table. Now, I'm trying to figure out how to enroll students into those services. I know that means I have to find a way to get the StudentID, and the ServiceID into the StudentService table. I am able to do this manually, but most likely I'm going to need to be doing this for hundreds of records, so I'm looking for a better way, through a form is possible.

Ideally, my users would be able to choose the Service they want to enroll students in, then choose the students to enroll in that service. Does this make sense? Is it even possible? I've tried so many different approaches (mostly centered around append queries), but am coming up with nothing but frustration. If anyone can help, I'd really appreciate it. Thank you!

 

[pbaldy]

One way:

http://www.baldyweb.com/MultiselectAppend.htm

 

[Jenaccess]

Thank you so much for your reply!

I downloaded the sample database and looked at it, and it seems like a great way to do this. I just have one question though. Would there be a way to filter the records that go in the list boxes. For example, we might have 800 students in the database. Instead of all of them going into the list box, could I choose say, just 6th graders, or just students from a certain school?

 

[pbaldy]

Sure; a common way is what's called cascading combos, which works the same way for a listbox:

http://www.baldyweb.com/CascadingCombo.htm

 

[Jenaccess]

The list box works like a dream. You've saved me weeks worth of banging my head against the wall!

 

[pbaldy]

Excellent! Glad it helped.