Populating Subform Field from Subform Control Source

[dollylectric]

I am working with a form called "frm_schools_copy" with 2 subforms: "frm_contacts_schools" and "frm_school_contact_person".



The form "frm_contacts_schools" has 2fields that I would like populated automatically based on the current records shown on the form: School ID from "frm_schools_copy" and School Contact Person ID from "frm_school_contact_person". School ID works no problem because it is linked to the master form. School Contact Person ID doesn't work because you can't use a control from a subform as the control source.

Came up with a work around that should work, for some reason is not.

I created an invisible text box (Control Name is "Text26" on the main form ("frm_schools_copy") with the control source =[frm_school_contact_person].[Form]![Contact Person ID]

Then I made the control source for School Contact Person ID =[Forms]![frm_schools_copy]![Text26]

This ALMOST works. It gives populates with the number in the invisible text box when you open the form. But when I change records in subform "frm_school_contact_person" the invisible text box changes its value but not the School Contact Person ID control in the "frm_contacts_schools" subform.

Any ideas why and how to fix? Or any ideas on another way to populate this field?

[June7]

What is data relationship? Each school can have more than one contact person? Can a person be a contact for only one school?

[dollylectric]

Schools can have more than 1 contact person; contact person can only have 1 school.

[June7]

Another question. Will every person in contacts table be a contact for some school? If so, just put the school ID in contact info table.

Do you want to provide project for analysis? Make copy, remove confidential data, zip if still large, attach to post.

[dollylectric]

I attached the file here. As you can see, I do have the school ID in the contact persons table (every contact person is associated with a school ID). I made the schools form the main form for usability and ease of navigation for the user, as they will almost always be trying to find the school and not the contact person.

BTW, I am a pretty new user to Access, so if you see anything else in there that's off or could be better, feel free to chime in!

[June7]

Here is what I understand about your db.

Tables:
Schools (school info)
School_Contact_Person (contact info)
Contacts_Schools (contact events)

Your frm_Schools is a form with two subforms.

ContactID control of Contacts_Schools subform should be a multi-column combobox. RowSource of the combobox should be filtered to only the contacts associated with the school. Review tutorials on comboboxes at http://www.datapigtechnologies.com/AccessMain.htm

The Master/Child link properties will force the SchoolID fields of the subforms to populate with the SchoolID of the main form. These fields should not be available to the users because don't want them accidentally editing them.

Project is acting weird. When I try to close a form and save edits, it won't close. Odd.