426,222 Members | 1,129 Online
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
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 afield. On the main form I have referenced this field. I'm using thisfield in a calculation on my main form. The problem occurs when thereare no records in the subform. The field on the main form thatreferences the subform total field returns #Error. How can I makethis field show the total when there are records and 0 when there areno 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" 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 afield. On the main form I have referenced this field. I'm using thisfield in a calculation on my main form. The problem occurs when thereare no records in the subform. The field on the main form thatreferences the subform total field returns #Error. How can I makethis field show the total when there are records and 0 when there areno 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 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 wrote in message news:... 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 away to do this. What about HasData? Is there a way I can use thisin a form? ChrisBas Cost Budde wrote in message news:... 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 wrote in message news:. .. 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 away to do this. What about HasData? Is there a way I can use thisin a form? ChrisBas Cost Budde wrote in message news:... 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.