Solved

I don't get Access recordcount function to work.

Posted on 2014-09-18
8
290 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 58
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 58
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 58
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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

630 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