Full Version: Counting the records in a recordset
UtterAccess Forums > Microsoft® Access > Access Forms
kenmac77
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
yvesdekort
Try this :
CODE
Dim recClone as object
Set recClone = Me.RecordsetClone
Me.txtCounter = recClone.RecordCount
kenmac77
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?
Ken
yvesdekort
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.
Roger_Carlson
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"
kenmac77
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)
thanks
Ken
BrianS
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
!--c1-->
CODE
Dim rec as DAO.Recordset
Set rec = Me.RecordsetClone
If Not rec.EOF Then
    rec.MoveLast
    rec.MoveFirst
    Me.txtCounter = recClone.RecordCount
    rec.BookMark = Me.BookMark
    Me.txtPos = rec.AbsolutePosition + 1
Else
    Me.txtCounter = 0
    Me.txtPos = 1
End If
rec.Close
set rec = nothing

OBelieve that Absolute position is zero-based meaning that record 1 would have an Absolute Position of 0
bennyl23
Try this:
CODE
dim rs as recordset
set rs = currentdb.openrecordset("QueryName",dbOpenDynaset)
if rs.recordcount <> 0 then
   rs.movelast
   me.txtCounter = rs.recordcount
end if
et rs = nothing
kenmac77
Wow
I am overwhelmed with the replies (and very thankful)
It will take my some time to test and explore.
Roger
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.
thanks
Ken
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.