Display SubForm Record Count On SubForm

[ReillyAlbany]

I have a Form with a Subform on a the second Page of a Tab Control. The Subform displays 1 child record at a time for all related records. I want to show the total number of child records without using the Record Selectors. The Text Box that I'm trying to place the record count in is called "Text36".

With the following code on the On Current property of the subform I am able to get the desired result but only after I've moved off of the first record in the subform. Until then it displays "1".

Can someone tell me how to refresh the subform recordset from the forms On Current property.

Thank you.

 

[Darrylles]

Hi Reilly,

If you're getting the reqd. result after you move from the first record, then you need to use a different event.

(You didn't supply the 'following' code).

Have you tried 'on open', 'on load' or 'on activate' events of the sub-form (dependant on your code)?

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[ReillyAlbany]

Darryles,

Thanks for stepping up to the plate. Sorry about leaving that code out. I think I was exausted from wrestling with the problem. to answer your question, yes I tried a variety of statements on those events and nonthing worked.

I think that the root of the problem lies in the fact that the recordset is loaded last so any code on the subform that references the recordset is getting the parent recordset upon load, open, and activate. The parent recordset is always "1".

The good news is I found a workable solution today. What I've done is force a recordset requery after the subform is fully loaded by using code associated with fields on the subform.

Here's my notes on what I got to work:

To show the record count of a subform on the subform:

> You need a text box to hold the value

> The first field in the subforms tab order needs to be one of the link fields and it must be visable.

> The Link Field needs the following coding:
On Got Focus: NextField.SetFocus
("NextField is the field that you really want to be the first in the Tab Order&quot
On Lost Focus: Me.RecordSet.Requery

> The NextField needs the followong coding:
On Got Focus: Forms!Parent!Child!TextBoxForRecordCount =
Forms!Parent!Child.Form.Recordset.RecordCount

> The parent form needs the followong coding:
On Current: Me!Child!TextBoxForRecordCount = Forms!Parent!Child.Form.Recordset.RecordCount

It may not be the most elegant approach, but, it works.

 

[ReillyAlbany]

Okay, I spoke too soon. After I posted my reply I went back to my subform and reset the Navigation Buttons property "No". Now the it doesn't work anymore. Apparently, it doesn't load the the subforms entire recordset until it has a good reason to. So now my question is.. How do I make Access load the full recordset?

 

[Rolliee]

As a programmer, I do as rs.movelast and then an rs.movefirst. This c auses the program to load that last record and then go back to the first - thus fully populating the recordset.

rollie@bwsys.net

 

[SgtJarrow]

Rolliee is right.....use to give you the count of the recordset.:

rs.MoveLast
rs.MoveFirst
Forms![Name Of Form]![Text36] = rs.RecordCount

The secret to creativity is knowing how to hide your sources. - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[ReillyAlbany]

Where do you put this code?

 

[cuok]

hi friends,

try this:

in the "Control Source" of the TextBox fill: =Count([id])

it's works!

 

[ReillyAlbany]

Couk - Thank you sooo much! I knew that there had to be a simpler way.