Summing all subsubform records related to a form record

[speaton via AccessMonster.com]

Hello, everyone.

I have a Form -> Subform -> Subsubform arrangement in which I want to show
the total of all of the subsubform records somewhere on the form.

I tried putting a text box in the form footer of the subsubform that was set
to =Sum([FieldName]), but when I refered to that text box on the subform
(using =[SubSubFormName]![txtFieldName]), I see that it showed me the sum for
only the subsubform records that were currently displayed, not for all the
subsubform records related to that particular main form record. What I need
to get is the sum for all of the grandchild records.

Can anyone teach me what I need to be doing differently to get the desired
results?

With many thanks in advance for any pointers,

speaton

 

[AccessVandal via AccessMonster.com]

Try something like

=Sum(Forms!MainForm!Control + Forms!SubForm!Control + Forms!SubSubForm!
Control)
or

=Sum(Forms!MainForm!Control) + Sum(Forms!SubForm!Control) + Sum(Forms!
SubSubForm!Control)
or

=Sum(Sum(Forms!MainForm!Control) + Sum(Forms!SubForm!Control) + Sum(Forms!
SubSubForm!Control))

Watch for word wrap in your browser.
If it does not work, try something like

Forms!MainForm!Control + Forms!MainForm!SubForm.Form!Control + .....

Hello, everyone.

I have a Form -> Subform -> Subsubform arrangement in which I want to show
the total of all of the subsubform records somewhere on the form.

I tried putting a text box in the form footer of the subsubform that was set
to =Sum([FieldName]), but when I refered to that text box on the subform
(using =[SubSubFormName]![txtFieldName]), I see that it showed me the sum for
only the subsubform records that were currently displayed, not for all the
subsubform records related to that particular main form record. What I need
to get is the sum for all of the grandchild records.

Can anyone teach me what I need to be doing differently to get the desired
results?

With many thanks in advance for any pointers,

speaton

 

[speaton via AccessMonster.com]

Thank you very much for the response, AccessVandal!

Try something like

=Sum(Forms!MainForm!Control + Forms!SubForm!Control + Forms!SubSubForm!
Control)

Perhaps I am not understanding this correctly, but it looks like this would
add some amount from the MainForm with some amount from the SubForm and then
with some amount from the SubSubForm. Is that correct? If so, what I want to
do is somewhat different.

I think my confusion might stem from the fact that there are no controls on
either for MainForm or the SubForm that I want to have included in the total.


If MainForm record number 1, for example, was related to three records in the
SubForm, and each of those were related to five SubSubForm records that each
had a value of 2, I would want the control on the MainForm to show a value of
30.

If MainForm record number 2, on the other hand, was related to two records in
the SubForm, one of which was related to a single SubSubForm record with a
value of 13 and the other was related to two SubSubForm records, one with a
value of 7 and another with a value of 4, I would want the control on the
MainForm to show a value of 24.

Does that make sense? I want to display the result of summing all of the
grandchild records (on the SubSubForm) for whatever the current parent
record (on the MainForm) happens to be. Will the formula you wrote do that?
If so, that's great, but I'm not sure what controls to put in the MainForm
and SubForm parts.

With many thanks in advance for any further suggestions or advice,

speaton

 

[AccessVandal via AccessMonster.com]

It looks like you’ll need to create a function to add Record 1 SubForm and
SubSubForm. (air code)

Function fAddTotal() as Long
Dim rs as DAO.Recordset
Dim strSQL as String

strSQL = “Select Sum(…..) As fTotal From Table1, Table2, Table3 Where Table1.
SomeID=Table2.SomeID And Table2.SomeID=Table3.SomeID And Table1.SomeID = “ &
Me.SomeID.

CurrentDb.Execute strSQL, dbFailOnError

fAddTotal = rs!fTotal 'return value
Set rs=nothing
End function

Sum(Table3.Col1) assumming “Table3†is the SubSubForm record source and
“col1†is the control field.

And in the unbound control of the SubSubForm input it like “=fAddTotalâ€
without the quotes.

You can create this function in a standard module or you can add it into your
form’s module.

I can't think of other solutions right now.

 

[AccessVandal via AccessMonster.com]

Opps, forgot you can't use select on this.

Function fAddTotal() as Long
Dim rs as DAO.Recordset
Dim db as DAO.Database
Dim strSQL as String

strSQL = “Select Sum(…..) As fTotal From Table1, Table2, Table3 Where Table1.
SomeID=Table2.SomeID And Table2.SomeID=Table3.SomeID And Table1.SomeID = “ &
Me.SomeID.

Set rs = db.openrecordset(strSQL)

fAddTotal = rs!fTotal 'return value
Set rs=nothing
'include your error trapping
End function

Note that you may have to provide some value to "Me.SomeID" as it will not
work in the standard module.

It looks like you’ll need to create a function to add Record 1 SubForm and
SubSubForm. (air code)

Function fAddTotal() as Long
Dim rs as DAO.Recordset
Dim strSQL as String

strSQL = “Select Sum(…..) As fTotal From Table1, Table2, Table3 Where Table1.
SomeID=Table2.SomeID And Table2.SomeID=Table3.SomeID And Table1.SomeID = “ &
Me.SomeID.

CurrentDb.Execute strSQL, dbFailOnError

fAddTotal = rs!fTotal 'return value
Set rs=nothing
End function

Sum(Table3.Col1) assumming “Table3†is the SubSubForm record source and
“col1†is the control field.

And in the unbound control of the SubSubForm input it like “=fAddTotalâ€
without the quotes.

You can create this function in a standard module or you can add it into your
form’s module.

I can't think of other solutions right now.

Thank you very much for the response, AccessVandal!

[quoted text clipped - 31 lines]

speaton

 

[AccessVandal via AccessMonster.com]

Just realized that you can use the DSum() function if it work for you.

=DSum("Field","TableName","SomeID = SomeIDControl")

Use the expression builder to look for the control fields or the table fields
but make sure you include the double quotes as expression builder won't add
it for you.

 

[speaton via AccessMonster.com]

Just realized that you can use the DSum() function if it work for you.

=DSum("Field","TableName","SomeID = SomeIDControl")

Use the expression builder to look for the control fields or the table fields
but make sure you include the double quotes as expression builder won't add
it for you.

Thank you, AccessVandal.

I had high hopes for this approach, but I can't seem to get it to work the
way I wanted.

First, I tried to add the ID field for the parent records to all of the
grandchild records (by adding a new column for that table and populating it
with the relevant data), but it seems that I can't get Access to enforce
referential integrity on more than one field per table (is that true?), so
that idea would be of limited usefulness.

The other problem, however, was that the following formula produces only
"#Error" results:

=DSum("GrandchildField","GrandchildTableName","ParentID_inGrandchildTable =
ParentID_inMainForm").

Still, if I can figure out the right way to get the results I want, this
seems like the approach I would prefer to take.

With many thanks for your continuing advice,

speaton

 

[AccessVandal via AccessMonster.com]

Can’t say much about RI, it really depends on your table design and
relationship.

Instead of “ParentID_inGrandchildTable†for the criteria, do have in the
GrandChild table included the ChildTable(the parent of this child) ID? If so,
you might want to use that instead.

=DSum("GrandchildField","GrandchildTableName","GrandchildID =
ChildID_InSubForm").

 

[speaton via AccessMonster.com]

Thank you very much for the continuing suggestions, AccessVandal.

I'm pleased to report that I've figured out a way to get the results I need
after looking at the examples on the Microsoft help pages:

http://office.microsoft.com/en-us/access/HA012288291033.aspx

For whatever reason, Access didn't like it when I provided an explicit name
for the ParentID value in the criteria argument for the DSum function, but it
happily did exactly what I wanted when I rewrote the arguments like this:

DSum("GrandchildField", "GrandchildTableName", "GrandchildTableName.ParentID
= " &[ParentID])

Now if I can just figure out a way to get referential integrity enforced on
the relationship between the Parent and Grandchild tables, my work will be
done!

Definitely getting closer to the goal...

 

[AccessVandal via AccessMonster.com]

That's good to hear.

To enforce RI, a one to one and one to many relationship will work but not
many to many. To enforce RI, a unique index is required in one table, in this
case a one to one and one to many.

You can't enforce RI in many to many as the there's no unique index.

Thank you very much for the continuing suggestions, AccessVandal.

I'm pleased to report that I've figured out a way to get the results I need
after looking at the examples on the Microsoft help pages:

http://office.microsoft.com/en-us/access/HA012288291033.aspx

For whatever reason, Access didn't like it when I provided an explicit name
for the ParentID value in the criteria argument for the DSum function, but it
happily did exactly what I wanted when I rewrote the arguments like this:

DSum("GrandchildField", "GrandchildTableName", "GrandchildTableName.ParentID
= " &[ParentID])

Now if I can just figure out a way to get referential integrity enforced on
the relationship between the Parent and Grandchild tables, my work will be
done!

Definitely getting closer to the goal...

 

[speaton via AccessMonster.com]

Now if I can just figure out a way to get referential integrity enforced on
the relationship between the Parent and Grandchild tables, my work will be
done!

Even without enforcing referential integrity on the relationship between the
Parent and Grandchild tables, I have discovered that I can use the results of
a query to get the effect I need, so this part of the form is now done.
AccessVandal, please accept my deepest thanks for all your help!

speaton

 

[speaton via AccessMonster.com]

Even without enforcing referential integrity on the relationship between the
Parent and Grandchild tables, I have discovered that I can use the results of
a query to get the effect I need, so this part of the form is now done.

Or at least I thought I was done. The one lingering problem is that the
control that shows the total for the grandchild records is not refreshed when
new records are added to the relevant table (or when existing records are
modified).

This seems to be because the query that provides the data is only run the
first time the Main Form is opened, and it is not refreshed thereafter. I
figure adding some code to the AfterUpdate event should do the trick, but I'm
not sure how to do that. For a control named txtUpdateMe on the form
frmMainForm, what code would I need to update the value of txtUpdateMe every
time there was and update to GrandchildFeildName?

 

[AccessVandal via AccessMonster.com]

In all your forms, requery your DSum control in each of the form's current
event.

Private Sub Form_Current()
Me.Textbox.Requery 'if it's on the form or...
'Forms!MainForm!SubForm.Form.Textbox.Requery
' or Forms!SubForm.Form!SubSubForm.Form!Textbox.Requery
'Forms!MainForm!SudForm.Form!SubSubForm.Form!Textbox.Requery
'see Help on these
End Sub

 

[speaton via AccessMonster.com]

In all your forms, requery your DSum control in each of the form's current
event.

Private Sub Form_Current()
Me.Textbox.Requery 'if it's on the form or...
'Forms!MainForm!SubForm.Form.Textbox.Requery
' or Forms!SubForm.Form!SubSubForm.Form!Textbox.Requery
'Forms!MainForm!SudForm.Form!SubSubForm.Form!Textbox.Requery
'see Help on these
End Sub

Thank you, AccessVandal. I found that the following works perfectly.

Forms!MainForm.Textbox.Requery

With many, many thanks for all your help,

speaton