Counting records in subform

  • Thread starter Thread starter Alex
  • Start date Start date
A

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
 
Alex said:
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
Click to expand...

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
 
Alex said:
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
Click to expand...

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 said:
Alex said:
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
Click to expand...

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]
Click to expand...

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.
 
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
 
Alex said:
Arvin - Dirk

Thanks very much - that worked a treat
Click to expand...

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,
Click to expand...

You do, after all, have a database at your disposal. :-)
Hey, Ho - Happy New Year to you both
Click to expand...

And to you, Alex.
 
Back
Top