Subform Totals

[Axis]

I have a form with a subform embedded in it. The sub form is continuous
forms and uses a view as the data source. In the footer of the form I have
several fields that are total fields for the sum of fields in the main body
of the report. ie in the detail I have a field which has x as its source,
and in the footer the field is set to "=Sum([x])". When I open the sub form
on its own the totals display correctly, but when I open the main form the
totals all come up as $Error. I notice that if I do something which causes
the sub form to requery that the correct totals briefly flash into the fields
before being replaced with $Error again.

Can anyone tell me what can cause this.

 

[Sylvain Lafontaine]

Maybe the text field is not wide enough to hold the result? Instead of
using a View, you could also use a SP who would compute all the required
values. Finally, it's not clear from your description what is what.

 

[Axis]

To Clarify the questions you raised
1. Yes the field is wide enough to hold the displayed value;
2. I did think of using an SP with a separate subform to calculate the
values, but I haven't done that yet as it will be a lot more complicated, and
it seems from the fact that the values are being initially calculated that
the data source is not the problem. I just wish to know if anyone has come
accross this before and can tell me whether there is a simple solution or not;
3. I am not sure what is not clear to you but I noticed that I inadvertantly
used the word report instead of form. I hope this explanation will clarify
things

The sub form is a bound to a view.
Some of the fields in the detail section of the sub form are bound to fields
with numeric values.
For each of these fields their is a total field in the form footer.
So where the field in the detail section of the form is bound to the field x
from the view, the respective total field in the form footer is bound to the
formula Sum([x])

The questions is why do the total fields in the form footer of the sub form
show "$Error" when the main form is opened, but display the correct totals
when the sub form is opened by itself.

Maybe the text field is not wide enough to hold the result? Instead of
using a View, you could also use a SP who would compute all the required
values. Finally, it's not clear from your description what is what.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I have a form with a subform embedded in it. The sub form is continuous
forms and uses a view as the data source. In the footer of the form I
have
several fields that are total fields for the sum of fields in the main
body
of the report. ie in the detail I have a field which has x as its source,
and in the footer the field is set to "=Sum([x])". When I open the sub
form
on its own the totals display correctly, but when I open the main form the
totals all come up as $Error. I notice that if I do something which
causes
the sub form to requery that the correct totals briefly flash into the
fields
before being replaced with $Error again.

Can anyone tell me what can cause this.

 

[Sylvain Lafontaine]

Sorry for the delay but I'm still unable to understand your post. You said
that:

Some of the fields in the detail section of the sub form are bound to
fields
with numeric values.
For each of these fields their is a total field in the form footer.
So where the field in the detail section of the form is bound to the field
x

The problem is that I absolutely don't know if you are talking about the
form footer of the subform or about the form footer of the main form.

I have created a subform. For this subform, there is a field [IdRepas],
this field is associated with a textbox whose name [TextIdRepas]. On the
footer section of this subform, I have added a control with the following
source:

=Sum([IdRepas])

This summation display correctly. Now, if I replace it with the following
source:

=Sum([TextIdRepas])

it doesn't display correctly anymore and display the #Error message. So I
changed back the source to =Sum([IdRepas]). The name for the textbox
control holding this summation is [TextSumIdRepas]. I remember that this
textbox is located in the footer section of the subform.

After that, I've inserted this subform onto a main form. The name of the
control holding this subform is SubformChoixEleves. On the footer section
of this main form, I've added the following textbox:

=[SubformChoixEleves].[Form].[TextSumIdRepas]

Notice that I'm now refering from the main form the control located in the
footer section of the subform that is containing the expression
"=Sum([IdRepas])" as its data source. This work properly. If I replace
with a mathematical expression such as:

=[SubformChoixEleves].[Form].[TextSumIdRepas]+[SubformChoixEleves].[Form].[TextSumIdRepas]

it still works properly. However, if instead of trying to access the
control locate in the footer section of the subform and containing the
summation expression, I try to directly access the individual controls of
the detail section of the subform or the recordset of the subform, this
doesn't work anymore; for example with:

=Sum([SubformChoixEleves].[Form].[IdRepas])

=Sum([SubformChoixEleves].[Form].[Recordset].[IdRepas])

These two last expressions don't seem to work. Maybe it's possible to make
them work, I don't know how at this moment.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

To Clarify the questions you raised
1. Yes the field is wide enough to hold the displayed value;
2. I did think of using an SP with a separate subform to calculate the
values, but I haven't done that yet as it will be a lot more complicated,
and
it seems from the fact that the values are being initially calculated that
the data source is not the problem. I just wish to know if anyone has
come
accross this before and can tell me whether there is a simple solution or
not;
3. I am not sure what is not clear to you but I noticed that I
inadvertantly
used the word report instead of form. I hope this explanation will
clarify
things

The sub form is a bound to a view.
Some of the fields in the detail section of the sub form are bound to
fields
with numeric values.
For each of these fields their is a total field in the form footer.
So where the field in the detail section of the form is bound to the field
x
from the view, the respective total field in the form footer is bound to
the
formula Sum([x])

The questions is why do the total fields in the form footer of the sub
form
show "$Error" when the main form is opened, but display the correct totals
when the sub form is opened by itself.

Maybe the text field is not wide enough to hold the result? Instead of
using a View, you could also use a SP who would compute all the required
values. Finally, it's not clear from your description what is what.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I have a form with a subform embedded in it. The sub form is continuous
forms and uses a view as the data source. In the footer of the form I
have
several fields that are total fields for the sum of fields in the main
body
of the report. ie in the detail I have a field which has x as its
source,
and in the footer the field is set to "=Sum([x])". When I open the sub
form
on its own the totals display correctly, but when I open the main form
the
totals all come up as $Error. I notice that if I do something which
causes
the sub form to requery that the correct totals briefly flash into the
fields
before being replaced with $Error again.

Can anyone tell me what can cause this.

 

[Sylvain Lafontaine]

Maybe some confusion between a control and its bound field if both are using
the same name (ie. the bound field is [IdRepas] and the name of the control
is also [IdRepas] instead of something else like [TextIdRepas]).

Also, you don't say how the main form is applying its restrictions on the
subform's records source. Maybe the method used change the name of the
fields returned by the query. You should take a look with the SQL-Server
Profiler; maybe this will tell you something about this problem.

Another possibility is that you are filtering the local recordset instead of
requerying the server. It's quite possible that a local filter is
incompatible with an expression inside a control.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks for your reply.

Just to clarify, all the fields are in the sub form, it never occured to
me
that you might think otherwise from what I wrote, so sorry about that.

I took your suggestion and changed the source from a view to a stored
procedure, which has solved the problem. I think the problem was
something
to do with the code from the main form manipulating the view, so having
replaced it with the sp it now works fine.

In the main form I had some code which restricted the source of the
subform
by using an SQL statement to only select certain records. This works fine
as
far as the body of the main form is concerned, it correctly displays the
rows
according to the criteria. But the total fields in the footer of the sub
form had the problem described where the total value would appear briefly
in
the fields immediataly after a requery, but then be replaced by the string
Error$.

Anyway changing it to a SP with parameter to drive it solved the problem,
and is also a more efficient way of executing the code. However I still
don't know why it didn't work correctly in the first place.

Sorry for the delay but I'm still unable to understand your post. You
said
that:

Some of the fields in the detail section of the sub form are bound to
fields
with numeric values.
For each of these fields their is a total field in the form footer.
So where the field in the detail section of the form is bound to the
field
x

The problem is that I absolutely don't know if you are talking about the
form footer of the subform or about the form footer of the main form.

I have created a subform. For this subform, there is a field [IdRepas],
this field is associated with a textbox whose name [TextIdRepas]. On the
footer section of this subform, I have added a control with the following
source:

=Sum([IdRepas])

This summation display correctly. Now, if I replace it with the
following
source:

=Sum([TextIdRepas])

it doesn't display correctly anymore and display the #Error message. So
I
changed back the source to =Sum([IdRepas]). The name for the textbox
control holding this summation is [TextSumIdRepas]. I remember that this
textbox is located in the footer section of the subform.

After that, I've inserted this subform onto a main form. The name of the
control holding this subform is SubformChoixEleves. On the footer
section
of this main form, I've added the following textbox:

=[SubformChoixEleves].[Form].[TextSumIdRepas]

Notice that I'm now refering from the main form the control located in
the
footer section of the subform that is containing the expression
"=Sum([IdRepas])" as its data source. This work properly. If I replace
with a mathematical expression such as:


=[SubformChoixEleves].[Form].[TextSumIdRepas]+[SubformChoixEleves].[Form].[TextSumIdRepas]

it still works properly. However, if instead of trying to access the
control locate in the footer section of the subform and containing the
summation expression, I try to directly access the individual controls of
the detail section of the subform or the recordset of the subform, this
doesn't work anymore; for example with:

=Sum([SubformChoixEleves].[Form].[IdRepas])

=Sum([SubformChoixEleves].[Form].[Recordset].[IdRepas])

These two last expressions don't seem to work. Maybe it's possible to
make
them work, I don't know how at this moment.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

To Clarify the questions you raised
1. Yes the field is wide enough to hold the displayed value;
2. I did think of using an SP with a separate subform to calculate the
values, but I haven't done that yet as it will be a lot more
complicated,
and
it seems from the fact that the values are being initially calculated
that
the data source is not the problem. I just wish to know if anyone has
come
accross this before and can tell me whether there is a simple solution
or
not;
3. I am not sure what is not clear to you but I noticed that I
inadvertantly
used the word report instead of form. I hope this explanation will
clarify
things

The sub form is a bound to a view.
Some of the fields in the detail section of the sub form are bound to
fields
with numeric values.
For each of these fields their is a total field in the form footer.
So where the field in the detail section of the form is bound to the
field
x
from the view, the respective total field in the form footer is bound
to
the
formula Sum([x])

The questions is why do the total fields in the form footer of the sub
form
show "$Error" when the main form is opened, but display the correct
totals
when the sub form is opened by itself.


:

Maybe the text field is not wide enough to hold the result? Instead
of
using a View, you could also use a SP who would compute all the
required
values. Finally, it's not clear from your description what is what.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I have a form with a subform embedded in it. The sub form is
continuous
forms and uses a view as the data source. In the footer of the form
I
have
several fields that are total fields for the sum of fields in the
main
body
of the report. ie in the detail I have a field which has x as its
source,
and in the footer the field is set to "=Sum([x])". When I open the
sub
form
on its own the totals display correctly, but when I open the main
form
the
totals all come up as $Error. I notice that if I do something which
causes
the sub form to requery that the correct totals briefly flash into
the
fields
before being replaced with $Error again.

Can anyone tell me what can cause this.

 

[Axis]

I already tried changed the name of the controls so they are different from
the field, to see if that makes a difference, but it didn't. Remember the
totals are calculated correctly by the the process, because thay flash on the
screen. The correct data is displayed in the rows on the screen, so the
source is worling correctly from that point of view.

To answer your question I was changing the source of the sub form
programatically. So the Record Source property was being reset to an SQL
statement "SELECT MyView.* From MyView Where X=1"

FYI I am using Access 2003 and SQL Server 2000 using MSDE engine on this
particular PC.

In the end I decided that it was more efficient to use an SP with a
paramater, as what I was doing breaks the true client server rules. I am
more currious than anything as to why it didn't work. Maybe I discovered a
bug in Access.

Maybe some confusion between a control and its bound field if both are using
the same name (ie. the bound field is [IdRepas] and the name of the control
is also [IdRepas] instead of something else like [TextIdRepas]).

Also, you don't say how the main form is applying its restrictions on the
subform's records source. Maybe the method used change the name of the
fields returned by the query. You should take a look with the SQL-Server
Profiler; maybe this will tell you something about this problem.

Another possibility is that you are filtering the local recordset instead of
requerying the server. It's quite possible that a local filter is
incompatible with an expression inside a control.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks for your reply.

Just to clarify, all the fields are in the sub form, it never occured to
me
that you might think otherwise from what I wrote, so sorry about that.

I took your suggestion and changed the source from a view to a stored
procedure, which has solved the problem. I think the problem was
something
to do with the code from the main form manipulating the view, so having
replaced it with the sp it now works fine.

In the main form I had some code which restricted the source of the
subform
by using an SQL statement to only select certain records. This works fine
as
far as the body of the main form is concerned, it correctly displays the
rows
according to the criteria. But the total fields in the footer of the sub
form had the problem described where the total value would appear briefly
in
the fields immediataly after a requery, but then be replaced by the string
Error$.

Anyway changing it to a SP with parameter to drive it solved the problem,
and is also a more efficient way of executing the code. However I still
don't know why it didn't work correctly in the first place.

Sorry for the delay but I'm still unable to understand your post. You
said
that:

Some of the fields in the detail section of the sub form are bound to
fields
with numeric values.
For each of these fields their is a total field in the form footer.
So where the field in the detail section of the form is bound to the
field
x

The problem is that I absolutely don't know if you are talking about the
form footer of the subform or about the form footer of the main form.

I have created a subform. For this subform, there is a field [IdRepas],
this field is associated with a textbox whose name [TextIdRepas]. On the
footer section of this subform, I have added a control with the following
source:

=Sum([IdRepas])

This summation display correctly. Now, if I replace it with the
following
source:

=Sum([TextIdRepas])

it doesn't display correctly anymore and display the #Error message. So
I
changed back the source to =Sum([IdRepas]). The name for the textbox
control holding this summation is [TextSumIdRepas]. I remember that this
textbox is located in the footer section of the subform.

After that, I've inserted this subform onto a main form. The name of the
control holding this subform is SubformChoixEleves. On the footer
section
of this main form, I've added the following textbox:

=[SubformChoixEleves].[Form].[TextSumIdRepas]

Notice that I'm now refering from the main form the control located in
the
footer section of the subform that is containing the expression
"=Sum([IdRepas])" as its data source. This work properly. If I replace
with a mathematical expression such as:


=[SubformChoixEleves].[Form].[TextSumIdRepas]+[SubformChoixEleves].[Form].[TextSumIdRepas]

it still works properly. However, if instead of trying to access the
control locate in the footer section of the subform and containing the
summation expression, I try to directly access the individual controls of
the detail section of the subform or the recordset of the subform, this
doesn't work anymore; for example with:

=Sum([SubformChoixEleves].[Form].[IdRepas])

=Sum([SubformChoixEleves].[Form].[Recordset].[IdRepas])

These two last expressions don't seem to work. Maybe it's possible to
make
them work, I don't know how at this moment.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


To Clarify the questions you raised
1. Yes the field is wide enough to hold the displayed value;
2. I did think of using an SP with a separate subform to calculate the
values, but I haven't done that yet as it will be a lot more
complicated,
and
it seems from the fact that the values are being initially calculated
that
the data source is not the problem. I just wish to know if anyone has
come
accross this before and can tell me whether there is a simple solution
or
not;
3. I am not sure what is not clear to you but I noticed that I
inadvertantly
used the word report instead of form. I hope this explanation will
clarify
things

The sub form is a bound to a view.
Some of the fields in the detail section of the sub form are bound to
fields
with numeric values.
For each of these fields their is a total field in the form footer.
So where the field in the detail section of the form is bound to the
field
x
from the view, the respective total field in the form footer is bound
to
the
formula Sum([x])

The questions is why do the total fields in the form footer of the sub
form
show "$Error" when the main form is opened, but display the correct
totals
when the sub form is opened by itself.


:

Maybe the text field is not wide enough to hold the result? Instead
of
using a View, you could also use a SP who would compute all the
required
values. Finally, it's not clear from your description what is what.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I have a form with a subform embedded in it. The sub form is
continuous
forms and uses a view as the data source. In the footer of the form
I
have
several fields that are total fields for the sum of fields in the
main
body
of the report. ie in the detail I have a field which has x as its
source,
and in the footer the field is set to "=Sum([x])". When I open the
sub
form
on its own the totals display correctly, but when I open the main
form
the
totals all come up as $Error. I notice that if I do something which
causes
the sub form to requery that the correct totals briefly flash into
the
fields
before being replaced with $Error again.

Can anyone tell me what can cause this.