Sum function on subform error

[Steve]

Hi
I have a main form that contains a subform
the main form is an invoice and the subform is the parts of the invoice and
on the footer of the subform I have a sum function that gives the total.
=Sum([SPQty]*[SPUnitPrice])
my problem is stability. Somtimes it works fine and somtimes I get an Error
that seems to go on endlessly like its in some kind of calculating error loop.
when I run the subform on its own and not inside the main form the sum
function always works fine every time
but when its within the main form as a subform it works only somtimes.
any comments or help please
Im using VIsta and Access 2003
Thanks
Steve - from a land down under

 

[Paul Shapiro]

Access sometimes seems to get confused when the field and the control have
the same name. That's unfortunate since the field name is the default
control name assigned by Access. I've generally modified all the control
names on a form to keep them distinct. It could also be that this was only
necessary in an older Access version. I didn't test recent versions to see
if it's still an issue.

 

[Marshall Barton]

Paul,

That has always been, and will always be a problem, BUT only
in the case where a text box expression refers to a field
name that is also the name of the text box with the
expression. For example, a text named Price has an
expression like = Price - Discount
In other words the text box expression is refering to
itself. This is called a circular reference.

Note that the aggregate functions (Count, Sum, etc) are
evaluated by a different mechanism that is totally unaware
of controls so this issue is moot in Steve's case.
--
Marsh
MVP [MS Access]

Access sometimes seems to get confused when the field and the control have
the same name. That's unfortunate since the field name is the default
control name assigned by Access. I've generally modified all the control
names on a form to keep them distinct. It could also be that this was only
necessary in an older Access version. I didn't test recent versions to see
if it's still an issue.

I have a main form that contains a subform
the main form is an invoice and the subform is the parts of the invoice
and
on the footer of the subform I have a sum function that gives the total.
=Sum([SPQty]*[SPUnitPrice])
my problem is stability. Somtimes it works fine and somtimes I get an
Error
that seems to go on endlessly like its in some kind of calculating error
loop.
when I run the subform on its own and not inside the main form the sum
function always works fine every time
but when its within the main form as a subform it works only somtimes.

Im using VIsta and Access 2003

 

[Marshall Barton]

I have a main form that contains a subform
the main form is an invoice and the subform is the parts of the invoice and
on the footer of the subform I have a sum function that gives the total.
=Sum([SPQty]*[SPUnitPrice])
my problem is stability. Somtimes it works fine and somtimes I get an Error
that seems to go on endlessly like its in some kind of calculating error loop.
when I run the subform on its own and not inside the main form the sum
function always works fine every time
but when its within the main form as a subform it works only somtimes.

Im using VIsta and Access 2003

I am unaware of any special problems with that kind of
calculation. The calculation could conceivably be slow to
update the screen, but without knowing what error you're
seeing, it's difficult to guess at a cause ot it.

One (remote?) possibility is if the subform's record source
is a query that includes a subquery or GROUP BY clause.

 

[Steve]

After reading all your helpful notes all of which I was unaware of and could
not find any refrence to by microsoft I decided to change a few names and test
changing the text box name so they were not the same as the field name did
nothing it was still unstable.
but I did find that the subform name "subfrmSaleParts" was the same as the
subform control name on the main form.
So I change the subform control name on the main form to "subSalesParts" and
left the subform as "subfrmSaleParts" and it seems now to have become stable
again.
thanks for your help and if it proves to still be unstable I shall yell out
again but for now at least the last 10 times I have run the form it works.
Scary when things happen like this.

Steve - from aland down under

Paul,

That has always been, and will always be a problem, BUT only
in the case where a text box expression refers to a field
name that is also the name of the text box with the
expression. For example, a text named Price has an
expression like = Price - Discount
In other words the text box expression is refering to
itself. This is called a circular reference.

Note that the aggregate functions (Count, Sum, etc) are
evaluated by a different mechanism that is totally unaware
of controls so this issue is moot in Steve's case.
--
Marsh
MVP [MS Access]

Access sometimes seems to get confused when the field and the control have
the same name. That's unfortunate since the field name is the default
control name assigned by Access. I've generally modified all the control
names on a form to keep them distinct. It could also be that this was only
necessary in an older Access version. I didn't test recent versions to see
if it's still an issue.

I have a main form that contains a subform
the main form is an invoice and the subform is the parts of the invoice
and
on the footer of the subform I have a sum function that gives the total.
=Sum([SPQty]*[SPUnitPrice])
my problem is stability. Somtimes it works fine and somtimes I get an
Error
that seems to go on endlessly like its in some kind of calculating error
loop.
when I run the subform on its own and not inside the main form the sum
function always works fine every time
but when its within the main form as a subform it works only somtimes.

Im using VIsta and Access 2003

 

[Steve]

I seem to have posted to early and after continued testing I have found I
also required to change the names on the text box so they dont match the
field names and then in the sum function use the field names and not the
textbox names.
Now seems stable yet again.
so to round up
I had the subform name the same as the subform control name on the main form.
I had the texbox names the same as the field names.
after changing both it has become stable again and no error showing and does
not do continious calculation in endless loop.

Steve - from a land down under

After reading all your helpful notes all of which I was unaware of and could
not find any refrence to by microsoft I decided to change a few names and test
changing the text box name so they were not the same as the field name did
nothing it was still unstable.
but I did find that the subform name "subfrmSaleParts" was the same as the
subform control name on the main form.
So I change the subform control name on the main form to "subSalesParts" and
left the subform as "subfrmSaleParts" and it seems now to have become stable
again.
thanks for your help and if it proves to still be unstable I shall yell out
again but for now at least the last 10 times I have run the form it works.
Scary when things happen like this.

Steve - from aland down under

Paul,

That has always been, and will always be a problem, BUT only
in the case where a text box expression refers to a field
name that is also the name of the text box with the
expression. For example, a text named Price has an
expression like = Price - Discount
In other words the text box expression is refering to
itself. This is called a circular reference.

Note that the aggregate functions (Count, Sum, etc) are
evaluated by a different mechanism that is totally unaware
of controls so this issue is moot in Steve's case.
--
Marsh
MVP [MS Access]

Access sometimes seems to get confused when the field and the control have
the same name. That's unfortunate since the field name is the default
control name assigned by Access. I've generally modified all the control
names on a form to keep them distinct. It could also be that this was only
necessary in an older Access version. I didn't test recent versions to see
if it's still an issue.

I have a main form that contains a subform
the main form is an invoice and the subform is the parts of the invoice
and
on the footer of the subform I have a sum function that gives the total.
=Sum([SPQty]*[SPUnitPrice])
my problem is stability. Somtimes it works fine and somtimes I get an
Error
that seems to go on endlessly like its in some kind of calculating error
loop.
when I run the subform on its own and not inside the main form the sum
function always works fine every time
but when its within the main form as a subform it works only somtimes.

Im using VIsta and Access 2003

 

[Marshall Barton]

The subform control's name and its Source Object are totally
independent so changing that should not make a difference.

My personal guideline is to change the name of any control
that I reference in a VBA procedure or in a control source
expression (by prefixing the field name with a three
character abbreviation of the control type (e.g. txt for
text box, cbo for combo boxes, etc). If I never refer to a
control, then I don't care what it's name happens to be.

As I tried to point out in an earlier reply, the aggregate
functions only operate on field names and will probably
result in some kind of error if you use a control name, the
same as using a control name in a query. Everywhere else,
if a control and a field have the same name, the control
will be used.

Anyway, it's nice to hear that you've managed to sort it
out, even if I have no idea what may have cause the problem.
--
Marsh
MVP [MS Access]

I seem to have posted to early and after continued testing I have found I
also required to change the names on the text box so they dont match the
field names and then in the sum function use the field names and not the
textbox names.
Now seems stable yet again.
so to round up
I had the subform name the same as the subform control name on the main form.
I had the texbox names the same as the field names.
after changing both it has become stable again and no error showing and does
not do continious calculation in endless loop.

Steve - from a land down under

After reading all your helpful notes all of which I was unaware of and could
not find any refrence to by microsoft I decided to change a few names and test
changing the text box name so they were not the same as the field name did
nothing it was still unstable.
but I did find that the subform name "subfrmSaleParts" was the same as the
subform control name on the main form.
So I change the subform control name on the main form to "subSalesParts" and
left the subform as "subfrmSaleParts" and it seems now to have become stable
again.
thanks for your help and if it proves to still be unstable I shall yell out
again but for now at least the last 10 times I have run the form it works.
Scary when things happen like this.

Steve - from aland down under

Paul,

That has always been, and will always be a problem, BUT only
in the case where a text box expression refers to a field
name that is also the name of the text box with the
expression. For example, a text named Price has an
expression like = Price - Discount
In other words the text box expression is refering to
itself. This is called a circular reference.

Note that the aggregate functions (Count, Sum, etc) are
evaluated by a different mechanism that is totally unaware
of controls so this issue is moot in Steve's case.


Paul Shapiro wrote:
Access sometimes seems to get confused when the field and the control have
the same name. That's unfortunate since the field name is the default
control name assigned by Access. I've generally modified all the control
names on a form to keep them distinct. It could also be that this was only
necessary in an older Access version. I didn't test recent versions to see
if it's still an issue.

I have a main form that contains a subform
the main form is an invoice and the subform is the parts of the invoice
and
on the footer of the subform I have a sum function that gives the total.
=Sum([SPQty]*[SPUnitPrice])
my problem is stability. Somtimes it works fine and somtimes I get an
Error
that seems to go on endlessly like its in some kind of calculating error
loop.
when I run the subform on its own and not inside the main form the sum
function always works fine every time
but when its within the main form as a subform it works only somtimes.

Im using VIsta and Access 2003