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.
Dim rst as Dao.Recordset
Set rst = CurrentDb.Openrecordset("S
If rst.Recordcount = 0 then
' No records
Else
rst.MoveLast
Msgbox rst.Recordcount ' ** correct record count will be displayed
End If
mx
Set rst = CurrentDb.Openrecordset("S
If rst.Recordcount = 0 then
' No records
Else
rst.MoveLast
Msgbox rst.Recordcount ' ** correct record count will be displayed
End If
mx
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:
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
"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
Well, you do not need to go back to the first record to get the record count.
mx
If speed matters, then see the tread with some cool ways to get the recordcount:
https://www.experts-exchange.com/questions/23381022/Fastest-way-to-get-recordcount.html
In particular the LPurvis post at 21512509
mx
https://www.experts-exchange.com/questions/23381022/Fastest-way-to-get-recordcount.html
In particular the LPurvis post at 21512509
mx
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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