Subform/Main Form Calculation Problem

On my subform I have a field in the footer that totals the value of a
field. On the main form I have referenced this field. I'm using this
field in a calculation on my main form. The problem occurs when there
are no records in the subform. The field on the main form that
references the subform total field returns #Error. How can I make
this field show the total when there are records and 0 when there are
no records???

Thank you,
Chris Vettese

re: Subform/Main Form Calculation Problem

A stab in the dark here, but try NZ function.

i.e.
=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)

Don't know if it will work, but give it a shot.

Regards,

Fraser.

chrisvettese@yahoo.com (chris vettese) wrote:[color=blue]
>On my subform I have a field in the footer that totals the value of a
>field. On the main form I have referenced this field. I'm using this
>field in a calculation on my main form. The problem occurs when there
>are no records in the subform. The field on the main form that
>references the subform total field returns #Error. How can I make
>this field show the total when there are records and 0 when there are
>no records???
>
>Thank you,
>Chris Vettese[/color]

re: Subform/Main Form Calculation Problem

Thanks for the response, unfortunatley it did not work. Any other ideas?

Regards,
Chris
"4Fraza" <fraza@clear.net.nz> wrote in message news:<40199fcb$1@clear.net.nz>...[color=blue]
> A stab in the dark here, but try NZ function.
>
> i.e.
> =nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)
>
> Don't know if it will work, but give it a shot.
>
> Regards,
>
> Fraser.
>
> chrisvettese@yahoo.com (chris vettese) wrote:[color=green]
> >On my subform I have a field in the footer that totals the value of a
> >field. On the main form I have referenced this field. I'm using this
> >field in a calculation on my main form. The problem occurs when there
> >are no records in the subform. The field on the main form that
> >references the subform total field returns #Error. How can I make
> >this field show the total when there are records and 0 when there are
> >no records???
> >
> >Thank you,
> >Chris Vettese[/color][/color]

re: Subform/Main Form Calculation Problem

On 30 Jan 2004 11:59:42 -0800, chris vettese wrote:
[color=blue]
> =nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)[/color]

I believe the refference to the field may be incorrect. Try

=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)
--
Mike Storr
veraccess.com

re: Subform/Main Form Calculation Problem

This did not work either, is there a VB solution?

Chris


Mike Storr <storrboy@sympatico.ca> wrote in message news:<9k9ihau7ub5n$.112ypxzar4ey9.dlg@40tude.net>. ..[color=blue]
> On 30 Jan 2004 11:59:42 -0800, chris vettese wrote:
>[color=green]
> > =nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)[/color]
>
> I believe the refference to the field may be incorrect. Try
>
> =nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)[/color]

re: Subform/Main Form Calculation Problem

chris vettese wrote:
[color=blue][color=green][color=darkred]
>>>=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)[/color][/color][/color]
[color=blue][color=green]
>>=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)[/color][/color]

I understand why Nz doesn't help you out--if there are no records, there
is no subform control present to refer to. No amount of Null checking
will help.

Is it possible to lift the calculation out of the subform? Into the main
form control, that is.

--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

re: Subform/Main Form Calculation Problem

I can not bring the calculation to the main form. There has to be a
way to do this. What about HasData? Is there a way I can use this
in a form?


Chris

Bas Cost Budde <bas@heuveltop.org> wrote in message news:<bvots4$r6q$4@news2.solcon.nl>...[color=blue]
> chris vettese wrote:
>[color=green][color=darkred]
> >>>=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)[/color][/color]
>[color=green][color=darkred]
> >>=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)[/color][/color]
>
> I understand why Nz doesn't help you out--if there are no records, there
> is no subform control present to refer to. No amount of Null checking
> will help.
>
> Is it possible to lift the calculation out of the subform? Into the main
> form control, that is.[/color]

re: Subform/Main Form Calculation Problem

On 4 Feb 2004 13:23:25 -0800, chrisvettese@yahoo.com (chris vettese) wrote:
[color=blue]
>I can not bring the calculation to the main form. There has to be a
>way to do this. What about HasData? Is there a way I can use this
>in a form?
>
>
>Chris
>
>Bas Cost Budde <bas@heuveltop.org> wrote in message news:<bvots4$r6q$4@news2.solcon.nl>...[color=green]
>> chris vettese wrote:
>>[color=darkred]
>> >>>=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)[/color]
>>[color=darkred]
>> >>=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)[/color]
>>
>> I understand why Nz doesn't help you out--if there are no records, there
>> is no subform control present to refer to. No amount of Null checking
>> will help.
>>
>> Is it possible to lift the calculation out of the subform? Into the main
>> form control, that is.[/color][/color]

Create a custom function that first checks for the existance of records in the subform. If records exist return the total else return 0.

Something like -

Function fGetTotal()

With Me.frmNameOfSubform.Form.RecordsetClone
IF .RecordCount<>0 Then
fGetTotal=Me.frmNameOfSubform.Form!NameOfTotalsCon trol
Else
fGetTotal=0
End IF
End With

Exit Function

Set the ControlSource of the control on your main form to =fGetTotal()

Wayne Gillespie
Gosford NSW Australia

re: Subform/Main Form Calculation Problem

chris vettese wrote:
[color=blue]
> I can not bring the calculation to the main form. There has to be a
> way to do this. What about HasData? Is there a way I can use this
> in a form?[/color]

No, it applies only during Print or Preview.

Maybe you can formulate the condition where there will be no records in
the subform, and wrap the formula on the main form control in such a test?

What is your goal? Maybe it helps if I can think myself through the
structure.
--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

re: Subform/Main Form Calculation Problem

THANK YOU very much for everyone who posted!!!

Wayne's post provided me with the solution I needed.

Again, I appreciate everyone's time and effort.

Best Regards,
Chris vettese

Wayne Gillespie <bestfit@NObestfitsoftwareSPAM.com.au> wrote in message news:<e1r220hd3399f95nir9uliismonk8f7nqv@4ax.com>. ..[color=blue]
> On 4 Feb 2004 13:23:25 -0800, chrisvettese@yahoo.com (chris vettese) wrote:
>[color=green]
> >I can not bring the calculation to the main form. There has to be a
> >way to do this. What about HasData? Is there a way I can use this
> >in a form?
> >
> >
> >Chris
> >
> >Bas Cost Budde <bas@heuveltop.org> wrote in message news:<bvots4$r6q$4@news2.solcon.nl>...[color=darkred]
> >> chris vettese wrote:
> >>
> >> >>>=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)[/color][/color]
>[color=green][color=darkred]
> >> >>=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)
> >>
> >> I understand why Nz doesn't help you out--if there are no records, there
> >> is no subform control present to refer to. No amount of Null checking
> >> will help.
> >>
> >> Is it possible to lift the calculation out of the subform? Into the main
> >> form control, that is.[/color][/color]
>
> Create a custom function that first checks for the existance of records in the subform. If records exist return the total else return 0.
>
> Something like -
>
> Function fGetTotal()
>
> With Me.frmNameOfSubform.Form.RecordsetClone
> IF .RecordCount<>0 Then
> fGetTotal=Me.frmNameOfSubform.Form!NameOfTotalsCon trol
> Else
> fGetTotal=0
> End IF
> End With
>
> Exit Function
>
> Set the ControlSource of the control on your main form to =fGetTotal()
>
> Wayne Gillespie
> Gosford NSW Australia[/color]