Need calculated control to work

[JJ]

I have a subform with fields: first name, last name, phone, expiration date,
membership status. I want a calculated control to display the total number of
records that appear in the subform. How do I do this?

 

[Allen Browne]

The simplest way to do this is to set the subform's NavigationButtons
property to Yes. The left end of the horizontal scrollbar will then display
something like:
4 of 199 records

If you don't want to do that, life is not so simple. If the form is in Form
view or Continuous View, you could add a text box to its Form Footer
section, and set the Control Source to:
=[Form].[Recordset].[RecordCount]

However, several things can go wrong with that:

a) It may initially show 1 for a while when the form first loads. This is
because it takes Access some time to load the form's records, and some
further time before it must update this text box.

b) This fails in Access 2007, where you must write a function to get the
count.

c) In older versions (before Access 2000) you might need to use:
=[Form].[RecordsetClone].[RecordCount]

 

[Dale Fye]

Allen,

Have not started using 2007 yet. Do I understand correctly that in 2007 you
actually have to write a function to get the record count and cannot referr
to the Forms recordsetclone.recordcount property?

The simplest way to do this is to set the subform's NavigationButtons
property to Yes. The left end of the horizontal scrollbar will then
display something like:
4 of 199 records

If you don't want to do that, life is not so simple. If the form is in
Form view or Continuous View, you could add a text box to its Form Footer
section, and set the Control Source to:
=[Form].[Recordset].[RecordCount]

However, several things can go wrong with that:

a) It may initially show 1 for a while when the form first loads. This is
because it takes Access some time to load the form's records, and some
further time before it must update this text box.

b) This fails in Access 2007, where you must write a function to get the
count.

c) In older versions (before Access 2000) you might need to use:
=[Form].[RecordsetClone].[RecordCount]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a subform with fields: first name, last name, phone, expiration
date,
membership status. I want a calculated control to display the total
number of
records that appear in the subform. How do I do this?

 

[Allen Browne]

A VBA function is the only way I know to get around Access 2007's inability
to handle:
=[Form].[Recordset].[RecordCount]
in the Control Source of a text box.

It's trivial. Without error handling:

Function RecordsInForm(frm As Form) As Long
If frm.RecordSource <> vbNullString Then
With frm.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
RecordsInForm = .RecordCount
End If
End With
End If
End Function

Set the Control Source of a text box to literally this:
=RecordsInForm([Form])
(i.e. you do not replace the Form part.)

If you only need to know whethere there any records or not, you can improve
performance hugely by omitting the MoveLast, so that's what I prefer to do.
It's the FormHasData() function on this page:
http://allenbrowne.com/RecordCountError.html

(I realize this is probably obvious to you, Dale, but others may be
researching this too.)

 

[Dale Fye]

Thanks, Allen.

Am getting ready to venture into the 2007 realm, and am starting to read all
of the 2007 threads to identify "features" in advance.

A VBA function is the only way I know to get around Access 2007's inability
to handle:
=[Form].[Recordset].[RecordCount]
in the Control Source of a text box.

It's trivial. Without error handling:

Function RecordsInForm(frm As Form) As Long
If frm.RecordSource <> vbNullString Then
With frm.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
RecordsInForm = .RecordCount
End If
End With
End If
End Function

Set the Control Source of a text box to literally this:
=RecordsInForm([Form])
(i.e. you do not replace the Form part.)

If you only need to know whethere there any records or not, you can
improve performance hugely by omitting the MoveLast, so that's what I
prefer to do. It's the FormHasData() function on this page:
http://allenbrowne.com/RecordCountError.html

(I realize this is probably obvious to you, Dale, but others may be
researching this too.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Have not started using 2007 yet. Do I understand correctly that in 2007
you actually have to write a function to get the record count and cannot
referr to the Forms recordsetclone.recordcount property?