By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Subform/Main Form Calculation Problem

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a

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.

ch**********@yahoo.com (chris vettese) wrote:
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


Nov 12 '05 #2

P: n/a
Thanks for the response, unfortunatley it did not work. Any other ideas?

Regards,
Chris
"4Fraza" <fr***@clear.net.nz> wrote in message news:<40********@clear.net.nz>...
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.

ch**********@yahoo.com (chris vettese) wrote:
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

Nov 12 '05 #3

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


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

=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)
--
Mike Storr
veraccess.com
Nov 12 '05 #4

P: n/a
This did not work either, is there a VB solution?

Chris
Mike Storr <st******@sympatico.ca> wrote in message news:<9k*****************************@40tude.net>. ..
On 30 Jan 2004 11:59:42 -0800, chris vettese wrote:
=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)


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

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

Nov 12 '05 #5

P: n/a
chris vettese wrote:
=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)
=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.

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

Nov 12 '05 #6

P: n/a
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 <ba*@heuveltop.org> wrote in message news:<bv**********@news2.solcon.nl>...
chris vettese wrote:
=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)=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.

Nov 12 '05 #7

P: n/a
On 4 Feb 2004 13:23:25 -0800, ch**********@yahoo.com (chris vettese) wrote:
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 <ba*@heuveltop.org> wrote in message news:<bv**********@news2.solcon.nl>...
chris vettese wrote:
>>>=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)

>>=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.


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
Nov 12 '05 #8

P: n/a
chris vettese wrote:
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?


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

Nov 12 '05 #9

P: n/a
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 <be*****@NObestfitsoftwareSPAM.com.au> wrote in message news:<e1********************************@4ax.com>. ..
On 4 Feb 2004 13:23:25 -0800, ch**********@yahoo.com (chris vettese) wrote:
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 <ba*@heuveltop.org> wrote in message news:<bv**********@news2.solcon.nl>...
chris vettese wrote:

>>>=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)>=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.


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

Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.