Solved

I don't get Access recordcount function to work.

Posted on 2014-09-18
8
279 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
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Accepted Solution

by:
Fritz Paul earned 0 total points
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now