Getting #error on calculating a subform summary field

[Guest]

I have a main form and a subform.
In the subform (continuous form) there is a field [text15] that summarises a
field in the subform. The control source of [text15] =sum([asubformfield])
In the main form i want to use this value to do some other calculations.
for instance with control source =[formname subform].Form!Text15 - [Text22]
My problem is that if there are records in the subform my code works
perfectly but if there are no records in the subform the calculation gives
#error.


Is there any way to prevent this and give me 0 instead of #error....
All these fields are supposed to be currency, but
Ccur(NZ([text15],0)) does nothing

Any help ??

 

[Allen Browne]

If there are no records in the subform, and no records can be added, the
Detail section of the form goes completely blank. Then trying to refer to
the non-existent controls in the subform generates an error.

To avoid that error, try:
=IIf([formname subform].Form.RecordsetClone.RecordCount > 0,
Nz([formname subform].Form!Text15,0), 0)

 

[Al Camp]

Rli,
Try...
=(NZ([formname subform].Form!Text15) - [Text22]

Tip: Use control names that properly describe the field contents.
Not OK... [Text4] = [Text12] * [Text22]
OK... [LineTotal] = [ItemPrice] * [ItemQty]
Believe me... you'll go nuts trying to read your code if you use such
non-descript naming.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Super...thanks

If there are no records in the subform, and no records can be added, the
Detail section of the form goes completely blank. Then trying to refer to
the non-existent controls in the subform generates an error.

To avoid that error, try:
=IIf([formname subform].Form.RecordsetClone.RecordCount > 0,
Nz([formname subform].Form!Text15,0), 0)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

I have a main form and a subform.
In the subform (continuous form) there is a field [text15] that summarises
a
field in the subform. The control source of [text15]
=sum([asubformfield])
In the main form i want to use this value to do some other calculations.
for instance with control source =[formname subform].Form!Text15 -
[Text22]
My problem is that if there are records in the subform my code works
perfectly but if there are no records in the subform the calculation gives
#error.


Is there any way to prevent this and give me 0 instead of #error....
All these fields are supposed to be currency, but
Ccur(NZ([text15],0)) does nothing

Any help ??

 

[Guest]

I amtrying to get this to work as well, but don't have the RecordsetClone
function. Is there something I need to add to the MSAccess install to get
this?
-b

If there are no records in the subform, and no records can be added, the
Detail section of the form goes completely blank. Then trying to refer to
the non-existent controls in the subform generates an error.

To avoid that error, try:
=IIf([formname subform].Form.RecordsetClone.RecordCount > 0,
Nz([formname subform].Form!Text15,0), 0)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

I have a main form and a subform.
In the subform (continuous form) there is a field [text15] that summarises
a
field in the subform. The control source of [text15]
=sum([asubformfield])
In the main form i want to use this value to do some other calculations.
for instance with control source =[formname subform].Form!Text15 -
[Text22]
My problem is that if there are records in the subform my code works
perfectly but if there are no records in the subform the calculation gives
#error.


Is there any way to prevent this and give me 0 instead of #error....
All these fields are supposed to be currency, but
Ccur(NZ([text15],0)) does nothing

Any help ??