Wrong records count in subfom

[Silvio]

Hello there, I have a text box in my subform1 that count all the records in
subform2 - continuous form - (subform2 is a subform of subform1). The text
box has the following code in the control source:
=frmSearchSub.Form.RecordsetClone.RecordCount

The problem is that the count of records is wrong, it gives me 27 when I
instead have over 200 records. 27 appear to be the number of records you can
see without using the vertical scroll bar in order to see the rest of the
records. Any idea how to solve this problem?

Thank you,
Silvio

 

[Al Campagna]

Silvio,
Say you have a key field in Subform2 named EmpID
Try this calculated text control in the subform2 footer.
= Count(EmpID)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Silvio]

Hi Al, I know that would work, however I would like to display the total on
subfom1. I have tried to lookup the count field you suggested from subform1
in oder to read the total in subfom2, however if the count in subform 2 is 0
then the control in subfom1 will display an error#.

 

[Dirk Goldgar]

Hi Al, I know that would work, however I would like to display the total
on
subfom1. I have tried to lookup the count field you suggested from
subform1
in oder to read the total in subfom2, however if the count in subform 2 is
0
then the control in subfom1 will display an error#.

Wrap your reference in Keri Hardwick's nnz() function:

http://www.mvps.org/access/forms/frm0022.htm
Forms: #Error when the Subform has no records

 

[Silvio]

Hi Dirk, I think you have found the solution however, I am not sure how to
implement it, can you be more specific?

 

[Dirk Goldgar]

Hi Dirk, I think you have found the solution however, I am not sure how to
implement it, can you be more specific?

Copy the code from that page (between the "Code Start" and "Code End" lines)
and paste it into a standard module (not a form, report, or class module).
If you create a new standard module for the purpose, don't give the module
the same name as the function. Save the module and compile your database to
make sure you haven't introduced any errors.

With that function now defined in your database, you can modify the
controlsource of your text box on subform1 to use the function. I don't
know the names of your controls, but if that text box's controlsource was
previously this:

=[Parent]![Subform2]![txtCount]

You would change it to:

=nnz([Parent]![Subform2]![txtCount])

 

[Silvio]

Great! It works just fine. I really appreciate you patience. Thank you!

Hi Dirk, I think you have found the solution however, I am not sure how to
implement it, can you be more specific?

Copy the code from that page (between the "Code Start" and "Code End" lines)
and paste it into a standard module (not a form, report, or class module).
If you create a new standard module for the purpose, don't give the module
the same name as the function. Save the module and compile your database to
make sure you haven't introduced any errors.

With that function now defined in your database, you can modify the
controlsource of your text box on subform1 to use the function. I don't
know the names of your controls, but if that text box's controlsource was
previously this:

=[Parent]![Subform2]![txtCount]

You would change it to:

=nnz([Parent]![Subform2]![txtCount])

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Mike Painter]

Hello there, I have a text box in my subform1 that count all the
records in subform2 - continuous form - (subform2 is a subform of
subform1). The text box has the following code in the control source:
=frmSearchSub.Form.RecordsetClone.RecordCount

The problem is that the count of records is wrong, it gives me 27
when I instead have over 200 records. 27 appear to be the number of
records you can see without using the vertical scroll bar in order to
see the rest of the records. Any idea how to solve this problem?

Thank you,
Silvio

Most of the time you will need to move to the last record for Access to get
a proper count.