subform total behaving strangely

[Paul]

I'm trying to display a total from a subform in a main form, and I keep
getting #Error in the main form control.

Until today, however, this same control was displaying the subform total
correctly. I first total the values in the subform in a control in the
subform footer, where I use the following expression in the Control Source
property:

=Sum([Rent])

where Rent is both the name of the control and the name of the field in the
query Record Source.

I then have a control in the main form that references the control in the
subform footer with the expression

=[Forms]![frm_find_lease]![ctl_Leases].[Form]![txt_total_rent]

which is =[Forms]![main form]![subform control name].[Form]![text box
control being referenced].

As I said, until today, it worked fine, then I added an additional table to
the subform's Record Source query, and then I started getting the #Error in
the main form control. I can't imagine why this happened, because the
individual fields in the subform display the correct values - so you would
think the text box in the subform would still obtain the correct value. I
can't view the subform total from the main form because the subform is in
datasheet view, and the footer is not visible. But when I open the subform
separately in Form view, the total in the subform footer also displays
#Error.

If the individual records in the subform field dispay the correct values for
rent, why would =Sum([Rent]) display #Error?

At first I thought the form might be getting confused because the Control
had the same name as the underlying field, so I tried renaming it to
txtRent, but that didn't make any difference. It still displays #Error.

Any idea why this is happening?

Thanks in advance.

Paul

 

[Allen Browne]

Okay, what you're doing make sense, so why did adding another table to the
subform's Record Source mess it up?

Perhaps there are now 2 fields named Rent in the source query, so the name
is not unique? If so, you could alias one of them in the source query (or
return only the desired one.)

Or perhaps the source query no longer allows new records to be added, and
the problem occurs when there are not records to display in the subform,
such that its Detail section goes completely blank? If that sounds like it,
see:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html

 

[Paul]

You were right about the field names, Alan. As soon as I removed the
ambiguity by using an alias, the problem went away.

Thanks for showing me how to solve this problem.

Paul

Okay, what you're doing make sense, so why did adding another table to the
subform's Record Source mess it up?

Perhaps there are now 2 fields named Rent in the source query, so the name
is not unique? If so, you could alias one of them in the source query (or
return only the desired one.)

Or perhaps the source query no longer allows new records to be added, and
the problem occurs when there are not records to display in the subform,
such that its Detail section goes completely blank? If that sounds like
it, see:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html

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

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

I'm trying to display a total from a subform in a main form, and I keep
getting #Error in the main form control.

Until today, however, this same control was displaying the subform total
correctly. I first total the values in the subform in a control in the
subform footer, where I use the following expression in the Control
Source property:

=Sum([Rent])

where Rent is both the name of the control and the name of the field in
the query Record Source.

I then have a control in the main form that references the control in the
subform footer with the expression

=[Forms]![frm_find_lease]![ctl_Leases].[Form]![txt_total_rent]

which is =[Forms]![main form]![subform control name].[Form]![text box
control being referenced].

As I said, until today, it worked fine, then I added an additional table
to the subform's Record Source query, and then I started getting the
#Error in the main form control. I can't imagine why this happened,
because the individual fields in the subform display the correct values -
so you would think the text box in the subform would still obtain the
correct value. I can't view the subform total from the main form because
the subform is in datasheet view, and the footer is not visible. But
when I open the subform separately in Form view, the total in the subform
footer also displays #Error.

If the individual records in the subform field dispay the correct values
for rent, why would =Sum([Rent]) display #Error?

At first I thought the form might be getting confused because the Control
had the same name as the underlying field, so I tried renaming it to
txtRent, but that didn't make any difference. It still displays #Error.

Any idea why this is happening?

Thanks in advance.

Paul