Make data editable in a query (L of Learner)

[Heribert]

I am working on a database for a charity. The have sponsors and they may "adopt" one or more children. In a specific situation there is child with two sponsors.
So I have a Sponsor table linked to an adoption table linked to child table.

Donor Table (1)--[Donor ID]--<(n) Adoption Table (n)>--[Child ID]--(1) Child Table

I am trying (or struggling) to get one screen - editable(!) - where I can go through each donor and it shows the children linked to that donor, where I can update both the Donor details and the Child Details (I am not talking about creating or deleting an entity...)

I tried by creating one query and build a form on the basis of that query. The fields become read only.
I tried with creating a form based on Donor and a subform based on a query on the tables Child -< Adoption - So this way the query has only two tables and the subform is linked on the basis of Donor ID. Work as a train, but fields are again read only...



I am running out of creative ideas... Any suggestion would be helpful..

Thanks

[June7]

Cannot do that - at least not easily.

For your case, simplify - don't use query, use tables as recordsources for forms.

Main form bound to Donors and subform bound to Adoptions. Adoptions would have a combobox for selection of child.

Then, if you need to modify a child details, open another form filtered to that child. If you want to add a new child "on the fly" during data entry, use combobox NotInList event to manage that (use same form designed for editing child details). Common topic.

Now, if you REALLY want child details form to always be available on the same main form, review https://www.fmsinc.com/MicrosoftAcce...edSubforms.asp

[Heribert]

Thank you. Afterposting the question i started messing about with nested sub forms. Child form is a sub of Adoption form and Adoption form a child of Donor form. I think if i add your info in the mix it will work.

Again thank you
😊

[June7]

If I understand your schema, Adoptions is a junction table to associate donors with children. AdoptionID is not saved into Children table - ChildID is saved into Adoptions. This means Children is not a table dependent on Adoptions because child records can exist that are not associated with an Adoption. Therefore, Child form cannot be a subform of Adoptions. Children table would be a lookup source for Adoptions.

And now that I think about, the Synchronize Related Subforms technique is not applicable.