Avatar of bejhan
bejhan asked on

Recordcount = 1 when there are more records

In my Access database when I open a table or recordset via dbOpenDynaset the recordcount property returns 1 even when there are more records. If I am going through the loop, it will go through the first iteration and then recordcount changes to the accurate amount at the top of the loop. Why is this? How can I fix it? I rely on the recordcount so not using it isn't really an option.
Microsoft Access

Avatar of undefined
Last Comment
mbizup

8/22/2022 - Mon
Thomasian

hi bejhan,

"The RecordCount property doesn't indicate how many records are contained in a dynaset, snapshot, or forwardonlytype Recordset object until all records have been accessed."

"To force the last record to be accessed, use the MoveLast method on the Recordset object."

http://msdn.microsoft.com/en-us/library/bb208624.aspx
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Dim rst as Dao.Recordset
Set rst = CurrentDb.Openrecordset("SomeTableOrQuery")


If rst.Recordcount = 0 then
    ' No records
Else
    rst.MoveLast
    Msgbox  rst.Recordcount    ' ** correct record count will be displayed
End If

mx
Vitor Montalvão

That's happen because when you open RecordSet, only first record it's loaded. You need to go through end of recordset to know the number of records.
DatabaseMX solution it's good, but then you need to move to first record again to do what you need. So you can use SQL to retrieve only number of records. Like:
Dim rst as Dao.Recordset
Set rst = CurrentDb.Openrecordset("SELECT COUNT(1) FROM YourTableNameHere")
'NOTE: You can add criteria using WHERE clause
 
If rst(0).Value = 0 then
    Msgbox  "There is no records"
Else
    Msgbox  "There is " & rst(0).Value  & " records"
End If

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"but then you need to move to first record again "
Well, you do not need to go back to the first record to get the record count.

mx
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

If speed matters, then see the tread with some cool ways to get the recordcount:

https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23381022.html

In particular the LPurvis post at 21512509

mx
Vitor Montalvão

The reason to go back to first record isn't to get the record count but to do whatever it needs (some data process or whatever).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question