Count Records in Subform

I have a simple question, but I cannot get it to work.  I need code to put in a main form that counts records in a subform.  The subform is located in a tab on the main form.  I would like to have the record count equal to txt1.

Thank you for your help.

csallAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
One last comment... sorry for all the posts.

The Public function can be placed directly in the main form's module, in which case that syntax will work.

If the function is placed in a seperate module, you would need to specify the full path to the subform:

 GetCount = Forms![MainFormName].[subform name].[Form].[Recordset].[RecordCount]
0
 
coffeeshopCommented:
One way is to count the records in the subform: =Count([Yourfield])

Name it and put a reference to your mainform:

=[frmYourSubName]![YourSumfField]
0
 
coffeeshopCommented:
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.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
GRayLCommented:
in the control source of the text box:

=sfmName.Form.RecordCount
0
 
rockiroadsCommented:
As GRayL says but would try

=<<nameofsubform>.Form.Recordset.RecordCount
0
 
GRayLCommented:
Right you are rocki.  I even tested it that way but instead of copy/paste, I tried to use memory, which is failing;-)
0
 
coffeeshopCommented:
@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?
0
 
GRayLConnect With a Mentor Commented:
It uses the property directly as a lookup - there should not be any problem unless you get to the point where the subform does not have a recordset as opposed to a record set with zero records. Proper error checking should mitigate that error source.
0
 
coffeeshopCommented:
OK, thank you for reply! I will give this another attempt next time I need it!
0
 
csallAuthor Commented:
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.
0
 
csallAuthor Commented:
I am guessing I have Zero Records and that is causing the error.

Carl
0
 
GRayLCommented:
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.
0
 
GRayLCommented:
Or do they both have the same name?
0
 
csallAuthor Commented:
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
0
 
coffeeshopCommented:
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
...

 
0
 
mbizupCommented:
<= [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
0
 
mbizupCommented:
Correction to the above...

       = GetCount()         <---- the function call requires the parentheses
0
 
csallAuthor Commented:
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
0
 
csallAuthor Commented:
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.

0
 
csallAuthor Commented:
When I get the "External Name not Defined" it is highlighting the subform name.

Carl
0
 
rockiroadsCommented:
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

0
 
csallAuthor Commented:
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.



0
 
csallAuthor Commented:
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(*)

0
 
rockiroadsConnect With a Mentor Commented:
If you want to go the vba route, you will have to remember to reset it each time the subform adds a record or delete's one. Thats why the suggeston of setting the controlsource was made

in form design, go to txt2, look at properties and change controlsource to

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

If u want it in vba then you need to specify mainform, look at what mbizup specified in the example of GetCount

in in vba during the on load event, try this

Me.txt2 = me. ...

with ... when u hit . after me, you get the intellisense menu. From there, find the variable used for your subform control, most likely Subtasks_Continuous
select that then select after that the rest i.e. .Form.Recordset.Recordcount




0
 
mbizupConnect With a Mentor Commented:
Carl,

Placing the function in a seprate module should work fine...

You need to use the full path to the subform if you do this, however:

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

The control source of your textbox would look like this:
= GetCount()

Also, make certain that you do not give your module the same name as your function.  Call it something like modGetCount to avoid problems with ambiguous names.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.