Counting records in subform

[Alex]

Hi, I want to count the number of records in a subform. andf siplay it on
the main form.

What is the best way to do this please

Thanks

Alex

 

[Arvin Meyer]

Hi, I want to count the number of records in a subform. andf siplay it on
the main form.

What is the best way to do this please

Assume a textbox (txtCount) to display the value. In the main form's On
Current event:

Sub Form_Current()
Me.txtCount = Me.SubformName.Form.RecordsetClone.RecordCount
End Sub

SubformName being the name of the subform control.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Dirk Goldgar]

Hi, I want to count the number of records in a subform. andf siplay
it on the main form.

What is the best way to do this please

One relatively easy way is to put a calculated text box in the form
header or footer section of the subform, with the Visible property of
either the section or the text box itself set to No/False, and the
ControlSource property of the text box set to

=Count(*)

Then have a *visible* calculated text box on the main form that pulls
the value from the hidden one on the subform. For example, if the
subform control (which displays the subform) is named "sfMySubform", and
text box on the subform is named "txtRecordCount", then the control on
the main form would have its ControlSource set to

=[sfMySubform]![txtRecordCount]

 

[Dirk Goldgar]

Hi, I want to count the number of records in a subform. andf siplay
it on the main form.

What is the best way to do this please

One relatively easy way is to put a calculated text box in the form
header or footer section of the subform, with the Visible property of
either the section or the text box itself set to No/False, and the
ControlSource property of the text box set to

=Count(*)

Then have a *visible* calculated text box on the main form that pulls
the value from the hidden one on the subform. For example, if the
subform control (which displays the subform) is named "sfMySubform",
and text box on the subform is named "txtRecordCount", then the
control on the main form would have its ControlSource set to

=[sfMySubform]![txtRecordCount]

I just tried something, and it seems (at first blush, at least) that a
combination of the method Arvin suggested and the one I suggested works
even better. If you're using Access 2000 or later, try this:

Don't bother creating an invisible control on the subform. Instead, use
an expression like this as the ControlSource for a text box on the main
form:

=[sfMySubform].[Form].[Recordset].[RecordCount]

In my quick test, it worked beautifully, and updated immediately as
records were added to the subform.

 

[Alex]

Arvin - Dirk

Thanks very much - that worked a treat

If I had to wish for one facility in Access it would be a snippets
functions, where I can store, logically organised, all these wonderful
pieces of code that you need from time to time,

Hey, Ho - Happy New Year to you both

Thanks again

Alex

 

[Dirk Goldgar]

Arvin - Dirk

Thanks very much - that worked a treat

You're welcome.

If I had to wish for one facility in Access it would be a snippets
functions, where I can store, logically organised, all these wonderful
pieces of code that you need from time to time,

You do, after all, have a database at your disposal.

Hey, Ho - Happy New Year to you both

And to you, Alex.