Updating junction table in form

[Palladian]

Hi,

I have an access database that contains three tables: Person, Scholarship, and PersonToScholarship (a junction tables that handles the many-to-many relationship between people and scholarships. It includes three fields: autoID, PersonID, and ScholarshipID).

I have a form that displays each person's information, including a subform that displays their scholarships (using a query that joins Person, PersonToScholarship, and Scholarship on Person.ID to PersonToScholarship.PersonID and Scholarship.ID to PersonToScholarship.ScholarshipID). The problem is that you can't use this subform to add or delete scholarships from a person's form page. The subform currently contains just one field, ScholarshipName, and if you change that name, you aren't changing the scholarship that the person is affiliated with, you are changing the scholarship's name.

What I need is a way to use this form to add and delete scholarships relationships without having to manually insert them into the PersonToScholarship table. Maybe with combo boxes...?



Any help is greatly appreciated. Thanks!

[ssanfu]

What I need is a way to use this form to add and delete scholarships relationships without having to manually insert them into the PersonToScholarship table. Maybe with combo boxes...?

You are correct.

In my subforms, the subform is in continuous forms, with a combo box to change the (in your case) scholarship - which actually should change the ScholarshipID (in the junction table). The ScholarshipID field should not be visible (field width = 0), the scholarship name visible (using the combo box).

[Palladian]

So how do I go about using combo boxes? Because a person can have multiple scholarships, do I write some sort of macro (I have no experience with those) to generate the number of combo boxes that I need?

[ssanfu]

When you add a new record in the subform, you set it up to automatically enter the personID and the ScholarshipID is selected by the combo box.
The subform allows you to have as many records in the subform as you want/need. The records source for the subform would be a query on the junction table.

[Palladian]

Sounds good--I'm new to Access, though, and I'm still confused as to how to go about doing this. I've attached my database to my original post--would you mind showing me how to do this?

[ssanfu]

Won't be able to look at it until tonight....

[ssanfu]

Something like this??

[Palladian]

This is great! Thank you so much!