Filter Total Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a form that filters based on dates provided. Now my question is how
would you find the total number of records of the filtered set? Is there a
specific command or do i need to create a recordset and then do a movelast
and then count that way?

Thanks,
Tim
 
Hi, Tim.
I have a form that filters based on dates provided. Now my question is how
would you find the total number of records of the filtered set?
Click to expand...

One way to do it is to read the form's recordset's recordcount in the
Current( ) event to determine if the filter has reset the count back to 1.
Try the following:

' * * * * Start Code * * * *

Private Sub Form_Current()

On Error GoTo ErrHandler

If (Me.Recordset.RecordCount = 1) Then
Me.Recordset.MoveLast
Me.Recordset.MoveFirst
End If

Me!RecCount.Value = Me.Recordset.RecordCount

Exit Sub

ErrHandler:

MsgBox "Error in Form_Current( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

.... where RecCount is a text box that displays the form's "filtered" record
count (or total record count when no filter is applied to the form).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
 
Gunny,
Thanks for the help, i used what you gave me and put something together to
figure out the record total. Inside my search after i do the filteron i do
this:

Me.Subform.Form.filter = where
Me.Subform.Form.FilterOn = True
`test to make sure there is records if not recordset is 0
If (Me.Subform.Form.Recordset.RecordCount <> 0) Then
Me.Subform.Form.Recordset.MoveLast
MsgBox (Me.Subform.Form.Recordset.RecordCount)
Me.Subform.Form.Recordset.MoveFirst
End If

I'll replace the msgbox part with the variable i want to hold it in. Thanks
for the help!

Tim
 
Back
Top