Count of Recs in Subform

[DEI]

I am using the following code to make a control in a form
count records in its subform. It works great (thanks Jim)
except I get an error when the subform has no (zero)
records. How do I debug it.

Also-where is the best place to put this code? So it
updates the fastest?

Thanks.

Private Function fSubFormRecCount() As Long
Dim frm As Form

Set frm = Me.SubformControlName.Form
frm.RecordsetClone.MoveLast
fSubFormRecCount = frm.RecordsetClone.RecordCount
Set frm = Nothing

End Function

 

[Ron Weiner]

Its the MoveLast that is giving the error when there are no records. So the
trick is to MoveLast only when ther are records. Try:

If Not frm.RecordsetClone.BOF Or Not frm.RecordsetClone.EOF Then
frm.RecordsetClone.MoveLast
End If

As for where to put it for the best performance, it dosent matter. It will
always run at the same speed reguardless of where it is fired from. You
need to make the call for WHEN is the best time to update your display. The
more often you update the display the slower your app will appear to be from
the users perspective.

Ron W

 

[Jeff Boyce]

It appears that your code would require loading the recordset to find the
last one, generating a count.

Another approach would be to use a simple SQL statement something like
(actual syntax may vary):

SELECT Count(*) FROM YourTable

This should be MUCH faster than opening a recordset, especially if you have
a very large table.

Good luck

Jeff Boyce
<Access MVP>

 

[Jim Allensworth]

It appears that your code would require loading the recordset to find the
last one, generating a count.

Another approach would be to use a simple SQL statement something like
(actual syntax may vary):

SELECT Count(*) FROM YourTable

This should be MUCH faster than opening a recordset, especially if you have
a very large table.

Good luck

Jeff Boyce
<Access MVP>

Jeff,
The only problem with that approach is that - using it on a subform -
you aren't going to filter the records with the linked fields. You
would always get the *complete* count of the unfiltered records.
I suppose you could hob together a WHERE clause from the link
properties of the subform. That could be problematic depending on the
recordset.

BTW, isn't the recordsetclone already loaded?

- Jim

 

[Jim Allensworth]

I am using the following code to make a control in a form
count records in its subform. It works great (thanks Jim)
except I get an error when the subform has no (zero)
records. How do I debug it.

Also-where is the best place to put this code? So it
updates the fastest?

Thanks.

Private Function fSubFormRecCount() As Long
Dim frm As Form

Set frm = Me.SubformControlName.Form
frm.RecordsetClone.MoveLast
fSubFormRecCount = frm.RecordsetClone.RecordCount
Set frm = Nothing

End Function

You know I just did a quick test of this and I am getting 0 when there
are no records - so I can't say what is causing your error. However, I
think in this instance you would be safe in adding an On Error Resume
Next at the top of the function.

- Jim