Update Totals on Related Tables

[fedebrin]

Good day CJ, The Doc Man,

Apologize if I haven't been clear enough, when I wrote "Edit All fields" the Total field shouldn't be one... let me rephrase it "all fields with the exception of the Total which should be calculated from each of the Initiatives"

And I do not need to have the total updated on the ACTIONS table but we do need to have visibility in a form.

See below and example of the ACTIONS DS form, as you see we have multiple Actions and we are missing the total from the initiatives within each action... and also we would like to be able to edit fields shown below

These actions have multiple initiatives each with an amount, see expanded view of the same ACTIONS DS form.

thank you for your patience!

 

[GK in the UK]

I'm not sure if you can get a total to display on your form when it shows many records from Actions.

Consider designing a new main form for Actions which opens only a single record from the table.
On this form you add controls (text fields) for the fields from Actions Table that you want to edit.

Then add a sub form datasheet with the related records in Initiatives.

In the sub form for related records in the InitiativesTable, you add a header and footer. You add a control in the footer with the controlsource like post #14. eg. txtActionTotal = sum([InitiativeTotal])

However the subform header and footer won't be visible, I believe that's how it is in Access when you display a sub form datasheet.
So to show the total on the parent form you need another control (text field) with the controlsource set to the name of the control in the sub form footer.

Since it counts as a different form, you can re-use the name, so you can have a text box on the parent form also called txtActionTotal with the control source set to =Me!Subform1.Form!txtActionTotal (where subform1 is whatever name you give to your subform)

ActionTotal isn't saved anywhere in your tables but you can always see the calculated value when you open the main form.

 

[fedebrin]

Thanks KG

Yes, I believe I will have to settle with 2 different forms:

1) that will be regular with single records and with the Total for its initiatives
2) I will still keep a Datasheet Form as I show above but this one will not display this total

I will try option 1 above and let you know

thanks all