Displaying Number of Records in a Text Box

[dmuroff]

Hi,

I have a filter on a form.
I would like to get rid of the record selector at the bottom and have a text box that displays the number of records that have been filtered and the total records.

For example if the filter outputs 25 of the 600 total records I would like the text box to display "25 of 600 records."

I have this so far but this displays the current record that is selected:

Private Sub Form_Current()
 Dim recPosition As Recordset
    Set recPosition = Me.RecordsetClone
    recPosition.Bookmark = Me.Bookmark
    Me![txtCount] = "Record " & (recPosition.AbsolutePosition + 1) & " of " & DCount("[InternalCode]", "Products")
    recPosition.Close
End Sub

Any help would be greatly appreciated. Thanks!

 

[txaccess]

I display a record count in the caption of the form - the code is:

Dim FrmRecCnt As String
Dim FrmCurRec As String

FrmRecCnt = Me.Recordset.RecordCount
FrmCurRec = Me.CurrentRecord

Me.RecCnt = FrmRecCnt
Me.CrntRec = FrmCurRec

' Caption the form & display record sequence
If IsNull(Me.EditRecCtrl) Then
Me.Caption = "***Read Only*** Accounts - " & Me.AccountName & " (Record " & Me.CrntRec & " of " & Me.RecCnt & ")"
Else:
Me.Caption = "Accounts - " & Me.AccountName & " (Record " & Me.CrntRec & " of " & Me.RecCnt & ")"
End If

Hope this helps

I haven't failed, I have just found 10,000 ways that it won't work!

 

[dmuroff]

I have gotten to this point:

Private Sub Form_Current()
 Dim recPosition As Recordset
  Set recPosition = Me.RecordsetClone
    Me![txtCount] = (recPosition.RecordCount) & " of " & DCount("[InternalCode]", "Products") & " Records"
    recPosition.Close
End Sub

This works to an extent but here's the problem:

I have 683 Records in total. When there are no filter applied the text box originally says "501 of 683 Records".
When I scroll down and select the last record it displays "683 of 683 Records"

Same thing happens when a filter is applied. It will say "10 of 683 Records" when it should really say "12 of 683 Records" and when I select the last record it updates itself to "12 of 683 Records".

Any ideas?

 

[RoyVidar]

There's no filter on the DCount - so it counts all the records in the table.

Try for instance something like this:

[tt]Me![txtCount]=me.recordset.absoluteposition+1 & " of " & _
me.recordset.recordcount[/tt]

- note that after setting a filter, code like this in for instance the on current event, may have finished it's count prior to the recordset being fully populated (providing wrong count), so sometimes one may have to tweak a little, here's one tweak

[tt]dim rs as dao.recordset
set rs=me.recordsetclone
rs.movelast
Me![txtCount]=me.recordset.absoluteposition+1 & " of " & _
rs.recordcount
set rs=nothing[/tt]

Roy-Vidar

 

[dmuroff]

Roy,

I do not want to show the position of the record selected.
Rather I want to show the total amount of the records that are being filtered.

 

[dmuroff]

Ah..
this works:

Private Sub Form_Current()
 Dim recPosition As Recordset
 Set recPosition = Me.RecordsetClone
 recPosition.MoveLast
  Me![txtCount] = (recPosition.RecordCount) & " of " & DCount("[InternalCode]", "Products") & " Records"
 recPosition.Close
   Dim rs As DAO.Recordset

End Sub

Cheers!