Subform Record Count displayed on main form

[Garindan]

Hi all, I have a search form with search combo boxes "frmCustomerSearch" which displays search results in a subform "fsubCustomerSearchDetails".

I would like to display the number of records found from the search at the bottom of the main form, but I can't work out how. Many thanks for all your help, I'm very grateful for this forum and the people on it!

 

[vbaInet]

Put this in the Control Source of a textbox on the main form:

=[fsubCustomerSearchDetails].[Form].[RecordsetClone].[RecordCount]

 

[Garindan]

Thanks vbaInet, I have done that. Unfortunately the text box just displays '#Name?'

 

[vbaInet]

Then that means fsubCustomerSearchDetails is not the name of the subform control, it is the name of the subform. Click on the subform control ONCE and look in the name property to get its name.

 

[Garindan]

Hi vbaInet, the name property IS already fsubCustomerSearchDetails.

The forms are unbound, will that make a difference?

 

[vbaInet]

I see.

In that case use a DCount() function:

=DCount("*", "TableName")

NameOfSubform - this time I mean the name of the subform, not the the name of the subform control.

But if you apply a filter to the subform the count won't update.

So you can drop a textbox in the footer section of the subform and put =Count(*). In the main form drop a textbox in the footer section and put

=[fsubCustomerSearchDetails]![Name of textbox in Subform Footer]

 

[Garindan]

Yes that works great, many thanks!!

 

[vbaInet]

Glad to hear!

 

[Garindan]

Actually could I trouble you for a bit more help? I have =Count(*) in my subform footer, and =[fsubCustomerSearchDetails]![txtDCount] in a text box on my main form. It works perfectly, except when the subform shows no results... instead of 0 (records) it displays #Error. How can I change it to show a zero? Many thanks!!

 

[vbaInet]

You can use something like:

=IIF(IsError(Count(*)), Null, Count(*))

OR

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

 

[Garindan]

I'm really sorry to take up your time!!! I've tried both of these and they both still show #Error on 0 (zero) results. Am I doing something wrong? Sorry and thanks again

 

[vbaInet]

You can upload your db and I'll take a look.

 

[Garindan]

Ok cheers. The search form is frmCustomerSearch.

 

[vbaInet]

=IIf(IsError([fsubCustomerSearchDetails]![txtDCount]),Null,[fsubCustomerSearchDetails]![txtDCount])

 

[Garindan]

=IIf(IsError([fsubCustomerSearchDetails]![txtDCount]),Null,[fsubCustomerSearchDetails]![txtDCount])

So this goes in the text box at the bottom of frmCustomerSearch, and =Count(*) in box at bottom of fsubCustomerSearchDetails? I'm just checking because i'm still getting #Error

 

[vbaInet]

That goes into Record Count textbox, nothing to do with the subform.

The record count textbox had =[fsubCustomerSearchDetails]![txtDCount] in it.

 

[Garindan]

Yeah actually I tried it again and it works great! Many thanks for your time! I really appreciate it!!

 

[vbaInet]

Good man!

You're welcome!