Solved

Count Records in Subform

Posted on 2009-05-04
25
731 Views
Last Modified: 2013-11-27
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.

0
Comment
Question by:csall
  • 8
  • 5
  • 5
  • +2
25 Comments
 
LVL 7

Expert Comment

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

Name it and put a reference to your mainform:

=[frmYourSubName]![YourSumfField]
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24294460
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24294709
in the control source of the text box:

=sfmName.Form.RecordCount
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24294797
As GRayL says but would try

=<<nameofsubform>.Form.Recordset.RecordCount
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24294841
Right you are rocki.  I even tested it that way but instead of copy/paste, I tried to use memory, which is failing;-)
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24295042
@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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 100 total points
ID: 24295123
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
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24295196
OK, thank you for reply! I will give this another attempt next time I need it!
0
 

Author Comment

by:csall
ID: 24295361
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
 

Author Comment

by:csall
ID: 24295388
I am guessing I have Zero Records and that is causing the error.

Carl
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24295416
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24295457
Or do they both have the same name?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:csall
ID: 24295734
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
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24295972
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24296423
<= [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
 
LVL 61

Expert Comment

by:mbizup
ID: 24296431
Correction to the above...

       = GetCount()         <---- the function call requires the parentheses
0
 
LVL 61

Accepted Solution

by:
mbizup earned 225 total points
ID: 24296481
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
 

Author Comment

by:csall
ID: 24297666
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
 

Author Comment

by:csall
ID: 24297786
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
 

Author Comment

by:csall
ID: 24297805
When I get the "External Name not Defined" it is highlighting the subform name.

Carl
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24297844
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
 

Author Comment

by:csall
ID: 24298085
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
 

Author Comment

by:csall
ID: 24298200
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 175 total points
ID: 24298451
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 225 total points
ID: 24298852
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now