Solved

I don't get Access recordcount function to work.

Posted on 2014-09-18
8
289 Views
Last Modified: 2014-09-24
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
Comment
Question by:Fritz Paul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40331269
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
 
LVL 75
ID: 40331271
maybe  this:

      Set rst1 = CurrentDB.OpenRecordset("s_qryLinearInfo")
      If rst1.RecordCount = 0 Then
            FindRecordCount =0
      Else
      rst1.MoveLast
           FindRecordCount = rst1.RecordCount
       End If
0
 
LVL 57
ID: 40331305
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Accepted Solution

by:
Fritz Paul earned 0 total points
ID: 40331378
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
 
LVL 57
ID: 40333141
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
 

Author Comment

by:Fritz Paul
ID: 40333214
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
 
LVL 57
ID: 40333226
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
 

Author Closing Comment

by:Fritz Paul
ID: 40341127
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question