displaying totals from nested subforms on main form

[bkberg05]

Hi - i have:

main_form
sub_form_a which is linked to main_form (many to one)
sub_form_b which is linked to sub_form_b (many to one)

sub_form_b has a field called 'amount'



I put an unbound field called 'sum_Amount_b' in the footer of sub_form_b to capture the sum of 'amount'. Works fine.

I put an unbound field on sub_form_a call 'sum_Amount_from_B' which is equal to 'sum_Amount_b'. Works fine.

I tried putting an unbound field called 'sum_Amount_A' in the footer of sub_form_a which summed up 'sum_Amount_from_B'. That didn't work and I understand now that you can't sum up an unbound field.

The last part of my plan had been to put an unbound field on main_form called 'sum_amount_total' which was supposed to equal to 'sum_Amount_A', but of course that didn't work cause the step above didn't work.

My next try was to add a bound field to sub_form_a's data source. Let's call that 'bound_sum_a'. I thought that once I had a bound field reflecting the correct value, then updating the main form would be easy. I tried lots of different ways to do this, but with no luck as yet.

So the broad question is how do I get my unbound field on the main_form (sub_amount_total) to reflect a grand total of all the amounts from both the sub forms.

The more detailed question (if I'm on the right track) is how to get the bound field (bound_sum_a) to equal the total of the amounts from sub_form_b. And to have this happend as people are making changes to the data (changing amounts, adding or deleting records). I've tried a variety of after_updates, after_inserts, etc. using refresh, requery on various objects. Not only have I not hit on the right combination, but each of the refresh, requeries cause me to lose focus which is an undesirable outcome.

Sorry for the long windedness, but I wanted to be thorough. Have been at this for several hours now and need some expert advise desparately. I really appreciate you reviewing this.

[jzwp11]

My next try was to add a bound field to sub_form_a's data source. Let's call that 'bound_sum_a'. I thought that once I had a bound field reflecting the correct value, then updating the main form would be easy. I tried lots of different ways to do this, but with no luck as yet.

In general, calculated values should not be stored in tables, so I would abandon this approach right away.

Now, you should be able to pull the sum values from the controls you set up and are working, but you will not be able to use the sum() function, but you should be able to just add them together (control1+control2). The key is making sure you reference the controls properly in the expression.

This site has the details on how to reference controls when subforms and subsubforms are involved.

[bkberg05]

I'd certainly rather not try to store the value in the table. I went down that path when I saw that you couldn't sum an unbound object.

I don't understand how to do this though. It's just a single field. Each record on the main form may have many records on the first subform, each of which can have many records on the second subform. The amounts I'm adding up are on the second subform. As I said, I can get the totals from sub_form_b to display on sub_form_a. I just can't get them all added up and onto the main_form.

I've referred to the site you referenced many times and understand how to do that, but am not grasping the concept I'd use to add these up.

Thanks again.

[jzwp11]

So you want to get the total of all items of the subsubform across all items in the subform. The best way to do this would be to create a query that pulls the all of the records from the 3 tables. I assume that your table structure looks something like this:


table1 (main form is based on this table)
-pkMainID


table2 (subform is based on this table)
-pksecondaryID
-fkMainID foreign key to table1

table3 (subsubform is based on this table)
-pktertiaryID
-fksecondaryID foreign key to table2
-fieldwithvalue

Once you have the query set up, add a control to the main form that uses the DSUM() domain aggregate function. The syntax would look something like this

=DSUM("fieldwithvalue", "queryname", "pkMainID=" & me.ControlOnMainFormThatHoldsValueOfpkMainID)

The expression above assumes that pkMainID is a numeric datatype.

[bkberg05]

Thanks. Liking this concept. Set up the query. All it has is two fields. pkMainID and fieldwithvalue. All my records are in there when i run the query. I added the control to the main form as follows.

=DSum("ExpenseAmount","qryExpenseReportAmountDetai l","ExpenseReportID=" & me.ExpenseReportID_formcontrol)

ExpenseAmount is fieldwithvalue (from the query)
qryExpenseReportAmountDetail is the query name
ExpenseReportID is pkMainID
ExpenseReportID_formcontrol is ControlOnMainFormThatHoldsValueOfpkMainID (it was originally named ExpenseReportID, but I changed it when it didn't work.

Feels like I've got this right, but am getting a #Name? on the form.

Any ideas? I've triple checked my names...

[jzwp11]

My error, you need to reference the field name not the control name

=DSum("expenseamount","qryExpenseReportAmountDetai l","expensereportID=" & [ExpenseReportID])

[bkberg05]

yipee!!!!!!!!

So next question is how do I get the field to update when I add, delete or change a detail record?

I've done each action and the value doesn't change...

Thanks

[jzwp11]

I believe that you will need a little bit of code in the after insert and after update events of the subform(s). That code will need to requery the control in the main form

I believe the code as follows should work (not tested) if executed from the subsubform

Me.Parent.Parent!ControlName.requery

[bkberg05]

Hi and thanks again - yes, that causes the control on the main form to update, but it has an undesired effect on the detail records. It seems like there is a temporary loss of focus and when focus returns, the first record in the list gains the focus. That's ok to me on an update/changed record which doesn't happen very often. But on inserts, which happen frequently, seeing the cursor pop to the top of the list and then having to grab the mouse to enter a new record is probably going to frustrate the user. Is there any way I can keep the focus where it is?

[jzwp11]

Is there any way I can keep the focus where it is?

I don't know about that. I tried a few things, but they did not work.

Another but probably less desirable option is to have a button on the main form that does the requery and leave it to the user to click the button if they want to see the updated value after their data entry is complete.

[bkberg05]

ok. thanks.

well, if anyone else has an idea, please let me know...

[jzwp11]

You might want to start a new thread with that specific question and then reference this thread in case the responders want some background.