#error returned in a text box

[Nate_on_Target]

Hey All

I am attempting to convert an Access database from 2003 to 2007. I am having trouble convert on formula in the control source on a form. In a sub form I have a hidden Text box that counts the records returned in the sub form. This then is displayed on the main form and used in other calculations. When there is one or more records return everything is working fine. However, when there are no records returned, the Hidden Text box on the sub form returns a null value and the text box that is displayed on the main form returns a #Error message.
I have searched and applied a work around that can be found here - http://allenbrowne.com/RecordCountError.html.

Here are the different formulas that I have used.



Text107 is the hidden text box which counts the records in the sub form.

Formula used in the hidden text box (Text107) =IIf(FormHasData([Form]),Count([PaymentAmount]),0) - note - when there are no records returned this still displays a null value.

Formulas that I have tried to display this on the main form -
=[sub_member_payments].[Form]![Text107]
=NZ([sub_member_payments].[Form]![Text107],0)
=IIf([sub_member_payments].[Form]![Text107]=1 Or [sub_member_payments].[Form]![Text107]>1,[sub_member_payments].[Form]![Text107],0)

Any Help on this is appreciated!
Thanks all

[pbaldy]

I'm not familiar with Allan's method, but I wonder if "[Form]" needs to be the full reference:

Forms!MainFormName.sub_member_payments

I use this method:

nnz function