Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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?

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 = ", FindRecordCount

Open in new window


The value of FindRecordCount just remains 0
0
Fritz Paul
Asked:
Fritz Paul
1 Solution
 
Rey Obrero (Capricorn1)Commented:
check if your query "s_qryLinearInfo" returns any records

if dcount("*","s_qryLinearInfo") = 0 then
   msgbox "No records to show"
else

'open recordset here

end if
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
maybe  this:

      Set rst1 = CurrentDB.OpenRecordset("s_qryLinearInfo")
      If rst1.RecordCount = 0 Then
            FindRecordCount =0
      Else
      rst1.MoveLast
           FindRecordCount = rst1.RecordCount
       End If
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Fritz PaulAuthor Commented:
Thanks for your help guys!

The error was with the MsgBox :-)

Was MsgBox "rst1.RecordCount = ", FindRecordCount
Now MsgBox "rst1.RecordCount = " & FindRecordCount

The difference between a " , " and a " & "
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
Fritz PaulAuthor Commented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
Fritz PaulAuthor Commented:
See Jim's comment. This was actually my solution. And believe me it took me a long time. (Quite shy about it.)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now