Ask a new question

Count of records in a sub form displays #error when there are no returns

Hi 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

Instead, check the number of records in the record source of your subform before calculating on the Main Form.

 

=IIF(DCount("FieldinSubFormRecordSource","SubFormRecordSouce")= 0 ,0,,[sub_member_payments].[Form]![Text107])

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Instead, check the number of records in the record source of your subform before calculating on the Main Form.



If you are going to do it like that then you can cut out the middleman of the hidden text box completely.  Say the subform's RecordSource property is a query named MyQuery, and the LinkMasterFields and LinkChildFields properties of the subform control are each MyID, which is a number data type, then the text box's Controlsource property would be:

=DCount("*", "MyQuery", "MyID = " & Nz(My[ID],0))

If the MyID columns were of text data type it would be:

=DCount("*", "MyQuery", "MyID = """ & [MyID] & """")

Unlike referencing a control in the subform, however, this does not allow for the rows within the subform being filtered by the user, but will return the number of rows in its complete recordset regardless of any temporary filter the user might apply to the subform.


_____________________
Ken Sheridan,
Newport, Shropshire, England

"Don't write it down until you understand it!" - Richard Feynman

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Try soomething more like this:

In the subform text box:

   =Count(*)

and in the main form text box:

   =IIf([sub_member_payments].[Form].Recordset.RecordCount > 0, [sub_member_payments].[Form]![Text107], 0)

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 2,930 Applies to: