Full Version: Counting the records in a recordset
UtterAccess Forums > Microsoft® Access > Access Forms
I have hidden the MS Navigation buttons employing new cmd buttons over which I have more control..... (first, previous,next, last and new) for reason too numerous to list. They work well.
I have lost the advantage of the record count within the MS navigation button cluster.
I have placed a text box "txtCounter" in my new bottons but I am having trouble trying to populate this box with the record count. I have tried DCount but this seems to only allow me to count items in a table. I would like it to count the records in the recordset with which the form is populated.
I frequently change the query in the form's rowsource and would like it to be a current count.
Why is the simple stuff so hard.
thanks for any comments.
Ken Mac
Try this :
Dim recClone as object
Set recClone = Me.RecordsetClone
Me.txtCounter = recClone.RecordCount
Thanks Yves
I have placed this code in the form current event.
When the form is opened, txtCounter displays a count of 2 (not correct).
When I change to the next record it flips to 429 (correct)
From here on, it is correct
Where does the two come from?
Do you change the recordsource of the form when you open it?
hat's the only thing I can think of which would cause the count of 2 when viewing the first record.
On my website, see sig below, is a small sample database which illustrates how to exactly emulate the Navigation Bar with buttons, and it includes the record count. It is called "RecordNavigation.mdb"
Yes I do change it on load.
I have just placed your code in "on load" to run after I create the query that is placed into the form's rowsource and after(I think) it is run.
Same result, 2 on the first record
429 after "on current" has fired.
Record set clone has always been a mystery to me. I must do some homework.
Am I being irresponsible placing this code in on current event. (will it load the server as the user flips through records)
I have employed similar methods in the past. The reason your record count is incorrect initially is because it is a DAO recordset. with DAO the recordcount is unknown until you reach the end of the recordset. What you can do is this in the code for each Nav button. This will give the recordcount in txtCounter as well as the current record position in txtPos
Dim rec as DAO.Recordset
Set rec = Me.RecordsetClone
If Not rec.EOF Then
    Me.txtCounter = recClone.RecordCount
    rec.BookMark = Me.BookMark
    Me.txtPos = rec.AbsolutePosition + 1
    Me.txtCounter = 0
    Me.txtPos = 1
End If
set rec = nothing

OBelieve that Absolute position is zero-based meaning that record 1 would have an Absolute Position of 0
Try this:
dim rs as recordset
set rs = currentdb.openrecordset("QueryName",dbOpenDynaset)
if rs.recordcount <> 0 then
   me.txtCounter = rs.recordcount
end if
et rs = nothing
I am overwhelmed with the replies (and very thankful)
It will take my some time to test and explore.
That example of "form navigation" behaves well. It did throw me a couple of type mismatch errors but it does work.
Other tasks are monopolizing available time at the moment. I will reply on each.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.