I don't get Access recordcount function to work.
I have the following code. My recordset has one or more records. I can loop through them, but I am making an error with counting the records. Can you spot the error?
The value of FindRecordCount just remains 0
Dim FindRecordCount As Long
Dim rst1 As DAO.Recordset
Set rst1 = db.OpenRecordset("s_qryLinearInfo")
If rst1.EOF Then
FindRecordCount = 0
Else
rst1.MoveLast
FindRecordCount = rst1.RecordCount
End If
MsgBox "rst1.RecordCount = ", FindRecordCountThe value of FindRecordCount just remains 0
maybe this:
Set rst1 = CurrentDB.OpenRecordset("s
If rst1.RecordCount = 0 Then
FindRecordCount =0
Else
rst1.MoveLast
FindRecordCount = rst1.RecordCount
End If
Set rst1 = CurrentDB.OpenRecordset("s
If rst1.RecordCount = 0 Then
FindRecordCount =0
Else
rst1.MoveLast
FindRecordCount = rst1.RecordCount
End If
You can use DCount() as cap has shown, which will be the fastest if your just trying to get the count.
You can also do it like Joe has shown, but need to watch out for recordsets that don't return a count, in which case, the RecordCount will be -1.
Jim.
You can also do it like Joe has shown, but need to watch out for recordsets that don't return a count, in which case, the RecordCount will be -1.
Jim.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Fritz,
You may not be aware, but you can accept your own comment as answer, and I think you should have. You can still give assists to others if you feel it helped.
Given that, I'm going to re-open this and let you re-close it.
Jim.
You may not be aware, but you can accept your own comment as answer, and I think you should have. You can still give assists to others if you feel it helped.
Given that, I'm going to re-open this and let you re-close it.
Jim.
ASKER
Hi Jim,
Thanks, some times I do choose my own answer as the solution, but in this case I felt that you people did bring value to the table and I really learnt from you. Except for pointing out the Dcount function which was actually what I should have used in the first place and, I did not feel alone in my struggle.
What I really like of EE is that I can feel free to ask, without being afraid that someone will scold me for asking a question that has already been asked or for being stupid. Also the complete answers that you people try to give. Some times in other forums one can just pick up more questions and frustration that what you went in with.
In this case, thanks then I will accept your nice gesture.
Thanks, some times I do choose my own answer as the solution, but in this case I felt that you people did bring value to the table and I really learnt from you. Except for pointing out the Dcount function which was actually what I should have used in the first place and, I did not feel alone in my struggle.
What I really like of EE is that I can feel free to ask, without being afraid that someone will scold me for asking a question that has already been asked or for being stupid. Also the complete answers that you people try to give. Some times in other forums one can just pick up more questions and frustration that what you went in with.
In this case, thanks then I will accept your nice gesture.
Fritz,
Appreciate you taking the time to make the comments and enjoyed hearing them. I'm glad we are able to help you out no matter what the issue is, small or large.
We've all been there!
Jim.
Appreciate you taking the time to make the comments and enjoyed hearing them. I'm glad we are able to help you out no matter what the issue is, small or large.
We've all been there!
Jim.
ASKER
See Jim's comment. This was actually my solution. And believe me it took me a long time. (Quite shy about it.)
if dcount("*","s_qryLinearInf
msgbox "No records to show"
else
'open recordset here
end if