Record count in form view subforms

[Someone]

Hello

I use the following to get the record count in subforms.

Forms!MainFormName.TextBoxName =
Forms!MainFormName!SubformName.Form.RecordsetClone.RecordCount

However, if I try and use this in a subform where the subform is in Single
Form view, rather than Datasheet view, the code only returns 1. When I
cycle through subsequent records, that figure 1 increments by as many
records that I cycle through.

How can I change the code so that it can count all the records where the
subform is in Single Form view?

Many thanks
M

 

[Arvin Meyer [MVP]]

Instead of using the form's recordsetclone, use a recordset that is the same
as the one used to build the subform. For instance:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intCount As Integer

Set db = CurrentDb
Set rst = dbOpenRecordSet("Select * From MyTable Where ForeignKey =" &
Me.PrimaryKey)
rst.MoveLast
intCount = rst.Recordcount

where MyTable is the table used for the subform's recordsource.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Van T. Dinh]

Try:

****Untested****
Dim rsd As DAO.Recordset

set rsd = Forms!MainFormName!SubformName.Form.RecordsetClone
rsd.MoveLast
Forms!MainFormName.TextBoxName = rsd.RecordCount
rsd.Close

Set rsd = Nothing
********

You may need to add the DAO Library to the References Collection of your
database.

 

[Someone]

Hi

Thank you for this - it worked perfectly. I just needed to add in an extra
line to take account of situations where there were no records to movelast.

Many thanks
M

Try:

****Untested****
Dim rsd As DAO.Recordset

set rsd = Forms!MainFormName!SubformName.Form.RecordsetClone
rsd.MoveLast
Forms!MainFormName.TextBoxName = rsd.RecordCount
rsd.Close

Set rsd = Nothing
********

You may need to add the DAO Library to the References Collection of your
database.

 

[Someone]

Hi Marvin

Thanks for your help
M

Instead of using the form's recordsetclone, use a recordset that is the same
as the one used to build the subform. For instance:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intCount As Integer

Set db = CurrentDb
Set rst = dbOpenRecordSet("Select * From MyTable Where ForeignKey =" &
Me.PrimaryKey)
rst.MoveLast
intCount = rst.Recordcount

where MyTable is the table used for the subform's recordsource.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Van T. Dinh]

Good catch ... I forgot aboutempty filtered Recordset.