Count Records in Subform

One way is to count the records in the subform: =Count([Yourfield])

Name it and put a reference to your mainform:

=[frmYourSubName]![YourSumfField]

Additional comment for my above solution: both are unbound fields on the form/subform, best place it in the header/footer of your forms. The advantage is that the calculation works without placing code.

in the control source of the text box:

=sfmName.Form.RecordCount

As GRayL says but would try

=<<nameofsubform>.Form.Recordset.RecordCount

Right you are rocki.  I even tested it that way but instead of copy/paste, I tried to use memory, which is failing;-)

@rocki/grayl: is the method with RecordCount as fast/stable as my above idea? I didn't used this a couple of years because in earlier apps I considered some problems resulting in #error?

OK, thank you for reply! I will give this another attempt next time I need it!

I have Coffeeshop solution working, except I made the subform field =Count(*) to count the records.

I cannot get Rockieroads solution to work.  I put in the following code:

= [subform name].[Form].[Recordset].[RecordCount]
and I tried
= [subform name].[Name of Parent Form].[Recordset].[RecordCount]
But no luck on either solution.  It would be nice to have a second means to count the records.

I am guessing I have Zero Records and that is causing the error.

Carl

This worked for me.  Confirm you have a text box in which you enter the control source as follows:

= [subform name].[Form].[Recordset].[RecordCount]
---------^--this is the name of the subform control, not the source object of the subform control.

Or do they both have the same name?

This is getting complicated.  Here is what I have

Subform is located in the tab labeled "Subtask" of the Form "Action Items"
The actual name of the subform is "Subtasks Continous"
The record source is from Query "Subtasks"

To complicate things, I have cases where there are no records, so even Coffeeshop's solution gives me the answer ############## if there are no records.

I am using the record count as a control for another action.  So I have a statement that is a standard If statement:

If [Text17] = 0 or [Text17] = "" or IsNull([Text17]) Then
(Rest of code below)

Any suggestions on how to get the code to work?

Carl

When and where is your code called? The problem can be (if you call it from code) that your sub isn't loaded yet, so getting the recordcount with both above methods results in an error. You can handle this with set and check some object variables, this is a bit more afford.

Or you can check on error in [Test17] with

If IsError([Text17) Then
...

 

<= [subform name].[Form].[Recordset].[RecordCount] >

I have never been able to get this to work outside of the VBA environment (ie: I've found that this method needs to be placed in code).  I think is is a problem with recordset and recordcount.

When using this syntax from a property sheet, I create a public function and call that instead:

Public Function GetCount() as Long
   GetCount = [subform name].[Form].[Recordset].[RecordCount]
End Function

and then call it from the control source of a textbox like this:
   = GetCount

Correction to the above...

       = GetCount()         <---- the function call requires the parentheses

I am not sure what is happening.  I put the code below in the "On Open" event property of the main form, but it pulls up the code saying there is an error.

txt1 =  [Subtasks Continous].[Form].[Recordset].[RecordCount]

To make sure it is in the right location I replaced code with:
txt1 = "Hello"
Text71 = txt1
On the form that worked fine and the "Text71" text box showed "Hello"

Mbizup:  Where should I place the GetCount code?  I tried in the "On Open" of the main form and it caused an error.

Carl

I put the GetCount in a Module.  I tested it and still is not working.  I get the following Compile error:

External Name not Defined.

I have a filter on the Query that limits the data.  Would this be causing the problem?  If there are subtasks assigned to an Action Item, then there would be no records.  So the code suggested by Coffeeshop at the top of this quesiton works fine unless there are no subtasks assigned.  Then I get the ########## as a result.

When I get the "External Name not Defined" it is highlighting the subform name.

Carl

Carl, when u added the code regarding =[subform name], you did enter the name of your subform right and not left it as [subform name] right?

=[Subtasks Continous].[Form].[Recordset].[RecordCount]

If you do have zero records, it makes no difference as this will not create an error. I gave this a quick test to confirm and no error occurred.

Also, if you want to use GetCount, it will not work in the On Open event as the underlying recordsource would not of been loaded. You can put it in form_load. Just need to ensure if you do add or delete records in subform then count gets updated accordingly

Rockieroads:

Subtasks Continous is the name of the subform, so the code I have in "on Load" reads:

txt2 = [Subtasks Continous].[Form].[Recordset].[RecordCount]

I still get an error - Run-time 438:  Object doesn't support this property or method.

why is it that if I make a put the following in for the countrol source of a text box I get a number if there are records and ########## if there are no records.  Why is the count not "0"?

text17 = Count(*)