Subform Record Count displayed on main form (1 Viewer)

G

Garindan

Registered User.
Local time
Today, 02:10
Joined
May 25, 2004
Messages
250
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! :D
 
V

vbaInet

AWF VIP
Local time
Today, 02:10
Joined
Jan 22, 2010
Messages
26,374
Put this in the Control Source of a textbox on the main form:

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

Garindan

Registered User.
Local time
Today, 02:10
Joined
May 25, 2004
Messages
250
Thanks vbaInet, I have done that. Unfortunately the text box just displays '#Name?'
 
V

vbaInet

AWF VIP
Local time
Today, 02:10
Joined
Jan 22, 2010
Messages
26,374
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.
 
G

Garindan

Registered User.
Local time
Today, 02:10
Joined
May 25, 2004
Messages
250
Hi vbaInet, the name property IS already fsubCustomerSearchDetails.

The forms are unbound, will that make a difference?
 
V

vbaInet

AWF VIP
Local time
Today, 02:10
Joined
Jan 22, 2010
Messages
26,374
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]
 
G

Garindan

Registered User.
Local time
Today, 02:10
Joined
May 25, 2004
Messages
250
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!!
 
V

vbaInet

AWF VIP
Local time
Today, 02:10
Joined
Jan 22, 2010
Messages
26,374
You can use something like:

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

OR

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

Garindan

Registered User.
Local time
Today, 02:10
Joined
May 25, 2004
Messages
250
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
 
G

Garindan

Registered User.
Local time
Today, 02:10
Joined
May 25, 2004
Messages
250
Ok cheers. The search form is frmCustomerSearch.
 

Attachments

  • Blank DB.accdb
    1.8 MB · Views: 1,096
V

vbaInet

AWF VIP
Local time
Today, 02:10
Joined
Jan 22, 2010
Messages
26,374
Code: 
=IIf(IsError([fsubCustomerSearchDetails]![txtDCount]),Null,[fsubCustomerSearchDetails]![txtDCount])
 
G

Garindan

Registered User.
Local time
Today, 02:10
Joined
May 25, 2004
Messages
250
vbaInet said:
Code: 
=IIf(IsError([fsubCustomerSearchDetails]![txtDCount]),Null,[fsubCustomerSearchDetails]![txtDCount])
Click to expand...

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 :(
 
V

vbaInet

AWF VIP
Local time
Today, 02:10
Joined
Jan 22, 2010
Messages
26,374
That goes into Record Count textbox, nothing to do with the subform.

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

Garindan

Registered User.
Local time
Today, 02:10
Joined
May 25, 2004
Messages
250
Yeah actually I tried it again and it works great! Many thanks for your time! I really appreciate it!! :D
 
You must log in or register to reply here.

Similar threads

M
Display subform confrol total to mainform
Replies
18
Views
456
Cotswold
Cotswold
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
410
gemma-the-husky
gemma-the-husky
M
Entwr records with form and subform.
Replies
11
Views
457
moi
M
M
Subform won't allow data entry/display form fields when record is empty
Replies
9
Views
441
Pat Hartman
P
B
Solved Data entry form how can I only show new record
Replies
2
Views
162
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom