Subform RecordCount in Main Form TextBox

Hi All

WinXPPro (SP2)
Access 2002 (SP3) and Access 2007

I've a main form with subform. On the main form is a selection combobox that builds SQL and applies it to the subform's recordsource... the number of returned records in the subform can be zero.

I want to display the number of records found and so I have added a hidden textbox 'record_count_textbox' in the footer of the subform whose controlsource is:-

=Count(*)

On my main form I have a textbox whose controlsource is:-

=NZ([My_Subform].[Form].[Record_Count_TextBox])

When there is more than one record for the subform to display, all is well. However, when there are no records to display, the textbox on my main form displays

#Error

... I guess this is because the subform is 'empty' and therefore the subform hidden textbox doesn't actually exist as such.

I'd like to replace #Error with 0 (zero) when req'd.

How do I test for an #Error in my textbox controlsource please?

Thanks

P.S. I've tried various schemes to do this recordcount... I abandoned the '=NZ(Dcount....' method because the SQL is quite complex... just much easier to use the subform's recordcount... I hope

Try this: http://www.mvps.org/access/forms/frm0022.htm

JR

Or you could try this:

=IIF([Form].[Recordset].[RecordCount] > 0, Count(*), 0)

Hey!

Thanks to both for those answers: I've been plagued by subform problems when no records exist in them and the article I was pointed at http://www.mvps.org/access/forms/frm0022.htm clears a lot of it up for me...

For anyone else out there struggling with this here's my controlsource for the textbox... it also permits some text too...

=IIf(Not (IsNumeric([My_Subform].[Form].[Record_Count_TextBox])),"Record Count (0)","Record Count (" & [My_Subform].[Form].[Record_Count_TextBox] & ")")


Thanks