Solved

I don't get Access recordcount function to work.

Posted on 2014-09-18
8
287 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 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
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…

825 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