Totaling Values with Mutliple Subforms within a Form

[Thomas Schoberl]

I am having quite an issue with this, and it is very confusing, so I am going
to provide a screenshot.

I have a form, with three subforms imbedded in it. Each subform holds three
dollar values which are correlated to the size of the unit; basically, a
"price" for each unit size. What I am trying to do is to add the costs of
the same sizes together on the main form.

Each subform is labeled according to "Leg"; aka, Leg1 = Subform1 ... Leg2 =
Subform2 ... Leg3 = Subform3. Now with that said, there are three sizes to
each "Leg", 20' 40' and 45'.

Ultimately, I am adding Leg1.[20'] + Leg2.[20'] + Leg3.[20'] to get a total,
but I either get #Error or #Name when I was looking through the previous
posts about how to add them together. Nothing has seemed to work.

<a href="http://www.angelfire.com/va3/peabody/images/help.JPG"></a>
Any help would be greatly appreciated.

 

[Allen Browne]

Get it working with just one first.
The expression will be something like this:
=[Leg1].[Form]![20']

For an explanation of the ".Form" bit, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Once you have that working, you can add to it, e.g.:
=Nz([Leg1].[Form]![20'], 0) + Nz([Leg1].[Form]![20'],0) + ...

Notes:
====
a) The Nz() is necessary in case one of the subform totals is null.

b) The Name of your subform controls might be different from the name of the
form loaded into it (its source object.)

c) If the subforms are read-only or new records cannot be added, there are
other issues:
http://allenbrowne.com/casu-20.html

d) It's hard to tell, but it might be better to handle this as a related
table instead of multiple fields/subforms.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Thomas Schoberl]

Mr. Browne,

I tried it, I still have a #Name? error.
"Get it working with just one first." Did you mean in the Main form or in
the subform? I was able to create one inside the subform with a =Sum([20'])
{This is known as [Text16]}, but I cannot get this to work for the main form.
Everytime I write a formula into the main form the #Name error occurs.

I also tried the "Allow Additions" set to Yes, and this didnt appear to do
anything.

I didn't understand what you meant in "b)", and "d)" was a little unclear
because I do not know what a related table is. If this means that I would
add Relationships to the tables and be able to link them together. Sadly, I
cannot do this. I have searched for a multitude of ways to be able to
accomplish this, but with the way that the information has to be set up,
there just isn't a way. I can try to explain why.

Each of the "Legs" can apply to either Leg1, Leg2, or Leg3 depending on how
they are linked in a table called "Routings". Basically, I set a master
table holding all of my "Legs", set a primary key to identify a "Leg Number".
In a routing table, I have set up "Route Identifier", "Leg1", "Leg2", and
"Leg3" fields. In three separate queries (which seemed the only logical way
that I could set up the Form I am using), the Legs are separated into 1, 2,
and 3, which has an input criteria the user has to identify on the form by
concantonating the Origin and Destination. The user simply selects Origin
and Destination and then press a button called "Search", to refresh the
queries and populate them on the screen. This is where I wanted to Total the
costs associated with each leg.

If there is a more simple way that I can set up this database, I would be
very appreciative to know. Thanks in advance for all of your help.

Get it working with just one first.
The expression will be something like this:
=[Leg1].[Form]![20']

For an explanation of the ".Form" bit, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Once you have that working, you can add to it, e.g.:
=Nz([Leg1].[Form]![20'], 0) + Nz([Leg1].[Form]![20'],0) + ...

Notes:
====
a) The Nz() is necessary in case one of the subform totals is null.

b) The Name of your subform controls might be different from the name of the
form loaded into it (its source object.)

c) If the subforms are read-only or new records cannot be added, there are
other issues:
http://allenbrowne.com/casu-20.html

d) It's hard to tell, but it might be better to handle this as a related
table instead of multiple fields/subforms.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I am having quite an issue with this, and it is very confusing, so I am
going
to provide a screenshot.

I have a form, with three subforms imbedded in it. Each subform holds
three
dollar values which are correlated to the size of the unit; basically, a
"price" for each unit size. What I am trying to do is to add the costs of
the same sizes together on the main form.

Each subform is labeled according to "Leg"; aka, Leg1 = Subform1 ... Leg2
=
Subform2 ... Leg3 = Subform3. Now with that said, there are three sizes
to
each "Leg", 20' 40' and 45'.

Ultimately, I am adding Leg1.[20'] + Leg2.[20'] + Leg3.[20'] to get a
total,
but I either get #Error or #Name when I was looking through the previous
posts about how to add them together. Nothing has seemed to work.

<a href="http://www.angelfire.com/va3/peabody/images/help.JPG"></a>
Any help would be greatly appreciated.

 

[Allen Browne]

Responses in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Mr. Browne,

I tried it, I still have a #Name? error.
"Get it working with just one first." Did you mean in the Main form or in
the subform? I was able to create one inside the subform with a
=Sum([20'])
{This is known as [Text16]}, but I cannot get this to work for the main
form.
Everytime I write a formula into the main form the #Name error occurs.

If you have the Text16 working, try:
=[Sub1].[Form]![Text16]
substituting your subform name for Sub1.
Note that this is the name of the subform *control*, which may not be the
same as the name of the form.

With the main form open in design view, right-click the edge of the subform
control, and choose Properties. On the Other tab of the Properties box, what
is the Name property? That's the name to use instead of Sub1.

I also tried the "Allow Additions" set to Yes, and this didnt appear to do
anything.

I didn't understand what you meant in "b)", and "d)" was a little unclear
because I do not know what a related table is. If this means that I would
add Relationships to the tables and be able to link them together. Sadly,
I
cannot do this. I have searched for a multitude of ways to be able to
accomplish this, but with the way that the information has to be set up,
there just isn't a way. I can try to explain why.

Each of the "Legs" can apply to either Leg1, Leg2, or Leg3 depending on
how
they are linked in a table called "Routings". Basically, I set a master
table holding all of my "Legs", set a primary key to identify a "Leg
Number".
In a routing table, I have set up "Route Identifier", "Leg1", "Leg2", and
"Leg3" fields. In three separate queries (which seemed the only logical
way
that I could set up the Form I am using), the Legs are separated into 1,
2,
and 3, which has an input criteria the user has to identify on the form by
concantonating the Origin and Destination. The user simply selects Origin
and Destination and then press a button called "Search", to refresh the
queries and populate them on the screen. This is where I wanted to Total
the
costs associated with each leg.

If there is a more simple way that I can set up this database, I would be
very appreciative to know. Thanks in advance for all of your help.

This is a separate issue to your main question of getting the value from the
subform back onto your main form.

Ultimately, it is more crucial though. You will need to read up on
normalization to understand if it may be better to have a related table with
a *record* for each leg, instead of having the 3 legs as fields in the one
table.

Get it working with just one first.
The expression will be something like this:
=[Leg1].[Form]![20']

For an explanation of the ".Form" bit, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Once you have that working, you can add to it, e.g.:
=Nz([Leg1].[Form]![20'], 0) + Nz([Leg1].[Form]![20'],0) + ...

Notes:
====
a) The Nz() is necessary in case one of the subform totals is null.

b) The Name of your subform controls might be different from the name of
the
form loaded into it (its source object.)

c) If the subforms are read-only or new records cannot be added, there
are
other issues:
http://allenbrowne.com/casu-20.html

d) It's hard to tell, but it might be better to handle this as a related
table instead of multiple fields/subforms.

message

I am having quite an issue with this, and it is very confusing, so I am
going
to provide a screenshot.

I have a form, with three subforms imbedded in it. Each subform holds
three
dollar values which are correlated to the size of the unit; basically,
a
"price" for each unit size. What I am trying to do is to add the costs
of
the same sizes together on the main form.

Each subform is labeled according to "Leg"; aka, Leg1 = Subform1 ...
Leg2
=
Subform2 ... Leg3 = Subform3. Now with that said, there are three
sizes
to
each "Leg", 20' 40' and 45'.

Ultimately, I am adding Leg1.[20'] + Leg2.[20'] + Leg3.[20'] to get a
total,
but I either get #Error or #Name when I was looking through the
previous
posts about how to add them together. Nothing has seemed to work.

<a href="http://www.angelfire.com/va3/peabody/images/help.JPG"></a>
Any help would be greatly appreciated.

 

[Thomas Schoberl]

Allen,

Thanks! The problem was that I kept putting in Leg1.Form and not [Leg
1].Form. A simple space can ruin my life, but thank you again for all of
your help.

Responses in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Mr. Browne,

I tried it, I still have a #Name? error.
"Get it working with just one first." Did you mean in the Main form or in
the subform? I was able to create one inside the subform with a
=Sum([20'])
{This is known as [Text16]}, but I cannot get this to work for the main
form.
Everytime I write a formula into the main form the #Name error occurs.

If you have the Text16 working, try:
=[Sub1].[Form]![Text16]
substituting your subform name for Sub1.
Note that this is the name of the subform *control*, which may not be the
same as the name of the form.

With the main form open in design view, right-click the edge of the subform
control, and choose Properties. On the Other tab of the Properties box, what
is the Name property? That's the name to use instead of Sub1.

I also tried the "Allow Additions" set to Yes, and this didnt appear to do
anything.

I didn't understand what you meant in "b)", and "d)" was a little unclear
because I do not know what a related table is. If this means that I would
add Relationships to the tables and be able to link them together. Sadly,
I
cannot do this. I have searched for a multitude of ways to be able to
accomplish this, but with the way that the information has to be set up,
there just isn't a way. I can try to explain why.

Each of the "Legs" can apply to either Leg1, Leg2, or Leg3 depending on
how
they are linked in a table called "Routings". Basically, I set a master
table holding all of my "Legs", set a primary key to identify a "Leg
Number".
In a routing table, I have set up "Route Identifier", "Leg1", "Leg2", and
"Leg3" fields. In three separate queries (which seemed the only logical
way
that I could set up the Form I am using), the Legs are separated into 1,
2,
and 3, which has an input criteria the user has to identify on the form by
concantonating the Origin and Destination. The user simply selects Origin
and Destination and then press a button called "Search", to refresh the
queries and populate them on the screen. This is where I wanted to Total
the
costs associated with each leg.

If there is a more simple way that I can set up this database, I would be
very appreciative to know. Thanks in advance for all of your help.

This is a separate issue to your main question of getting the value from the
subform back onto your main form.

Ultimately, it is more crucial though. You will need to read up on
normalization to understand if it may be better to have a related table with
a *record* for each leg, instead of having the 3 legs as fields in the one
table.

Get it working with just one first.
The expression will be something like this:
=[Leg1].[Form]![20']

For an explanation of the ".Form" bit, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Once you have that working, you can add to it, e.g.:
=Nz([Leg1].[Form]![20'], 0) + Nz([Leg1].[Form]![20'],0) + ...

Notes:
====
a) The Nz() is necessary in case one of the subform totals is null.

b) The Name of your subform controls might be different from the name of
the
form loaded into it (its source object.)

c) If the subforms are read-only or new records cannot be added, there
are
other issues:
http://allenbrowne.com/casu-20.html

d) It's hard to tell, but it might be better to handle this as a related
table instead of multiple fields/subforms.

message
I am having quite an issue with this, and it is very confusing, so I am
going
to provide a screenshot.

I have a form, with three subforms imbedded in it. Each subform holds
three
dollar values which are correlated to the size of the unit; basically,
a
"price" for each unit size. What I am trying to do is to add the costs
of
the same sizes together on the main form.

Each subform is labeled according to "Leg"; aka, Leg1 = Subform1 ...
Leg2
=
Subform2 ... Leg3 = Subform3. Now with that said, there are three
sizes
to
each "Leg", 20' 40' and 45'.

Ultimately, I am adding Leg1.[20'] + Leg2.[20'] + Leg3.[20'] to get a
total,
but I either get #Error or #Name when I was looking through the
previous
posts about how to add them together. Nothing has seemed to work.

<a href="http://www.angelfire.com/va3/peabody/images/help.JPG"></a>
Any help would be greatly appreciated.