Count Records in a Continuous Form

[Weekleyba]

I have a continuous subform that I want to display the record count in a text box outside the continuous form.
See below "Projects found". Note in the footer I made visible the text box "Count"

In "Count" is =Count(*) text box name is txtCount.
In "Projects found" is =[F_ProjectContract].[Form]![txtCount]

This works until I have no records then give me an #Error.
How do I fix this?

 

[Minty]

Try putting an NZ() in txtCount

=Nz(Count (*) , 0 )

 

[Weekleyba]

It gives the exact same error.

And does not give the 0 in the Count text box. ?

 

[Minty]

Sorry I hadn't realised the subform was empty.

Do you have a saved query for your sub form row source?
If you do you can use a dcount() directly on that query using the same criteria as your subform link/search criteria.

 

[Weekleyba]

Well...that's a bit tricky for me. I have a dynamic search that requeries the subform.
I think I'll just leave the Count in the footer as that at least does not show the error.
May I'll work in this another time.

I did try this but didn't work.

 

[CJ_London]

you can use a listbox - use a rowsource something like

SELECT Count(*) FROM (subformrecordsource)

subsitute subformrecordsource with whatever your dynamic sql is

set the list rows to 1, suggest also disable and lock

you'll need to replace it each time

lstProjects.rowsource="SELECT Count(*) FROM (" & dynamic sql & ")"
which you can do at the same time you assign your dynamic sql to the subform recordsource

gets a bit more complicated if users can filter the subform but can be done

probably want to set the rowsource to "" on first open to not display anything or to something like "SELECT TOP 1 0 FROM msysObjects" to display a zero

 

[Pat Hartman]

Assuming the white areas are the footer of the subform, use the following as the ControlSource of a control in the subform's footer section:

=Count(*)

 

[CJ_London]

@Pat Hartman - that was the original problem per post #1 - you get an error if there are no records

 

[LarryE]

I have a continuous subform that I want to display the record count in a text box outside the continuous form.
See below "Projects found". Note in the footer I made visible the text box "Count"

View attachment 103774

In "Count" is =Count(*) text box name is txtCount.
In "Projects found" is =[F_ProjectContract].[Form]![txtCount]

This works until I have no records then give me an #Error.
How do I fix this?

View attachment 103775

Try In "Projects found"
=Nz(Forms![F_ProjectContract]![txtCount],0)