Code to count records

[sparlaman]

Does anyone out there have any vba code that will allow me to count (verify) the number of records returned by a query?

Thanks,
Shel

 

[modest]

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim rsCount As Integer
Dim queryNameOrSQL As String

queryNameOrSQL = "<Query Name or the SQL Statement goes here>"

Set db = CurrentDb
Set rs = db.OpenRecordset(queryNameOrSQL)

rsCount = rs.RecordCount

something like that should work

-modest

 

[sparlaman]

Thanks!

Thanks for the quick reply. I'll give that a whirl!

 

[modest]

let us know if it worked or if you need further assistance

 

[adevera]

You can also use the recordset clone

i.e : dim as rst as dao.recordset
set rst = me.recordsetclone
msgbox rst.recordcount

 

[KenHigg]

Or you could use a dcount().

Ken

 

[sparlaman]

FYI Solution I used

This worked exactly as I wanted and it is VERY simple.

nRecords = DCount("*", "Query1")
If nRecords = 0 Then
MsgBox "zero records.", vbInformation, "System Error"
End If


Thanks ALL!

 

[KenHigg]

Or:

If DCount("*", "Query1") = 0 Then
MsgBox "zero records.", vbInformation, "System Error"
End If

Ken

 

[modest]

Kenhigg, great job at stealing Sparlaman's post

Though yours is shorter, Sparla's is better for programming... the purpose of a variable is also to help you go back and edit your code, that's why a variable's name is very important (especially when you're looking at someone else's code)

intRecords or intRcdCount is a good variable to use... using just dcount() makes you look back at it's definition, when you've forgotten what it does.

 

[ChrisO]

intRecords or intRcdCount is a good variable to use...

Not if you’re using a good naming convention…

Sorry, the devil made me do it.

Regards,
Chris.

 

[KenHigg]

Kenhigg, great job at stealing Sparlaman's post

I just did squeeze it in

Though yours is shorter, Sparla's is better for programming... the purpose of a variable is also to help you go back and edit your code, that's why a variable's name is very important (especially when you're looking at someone else's code)

intRecords or intRcdCount is a good variable to use... using just dcount() makes you look back at it's definition, when you've forgotten what it does.

Duely noted. Hold habits die hard

Ken

 

[ChrisO]

Love? Then why not love ‘all things both great and small’…

intRecords or intRcdCount will raise an error when the number of records exceed 32,767 if ‘int’ is used correctly as a naming convention.

Ken’s use of the function DCount() directly does not have that restriction but leaves it open to be type cast by the system depending on the return value.

Therefore Ken’s solution was, and still remains, better than yours…prevention of runtime errors.

But maybe you’re correct…we’ve just got to love it.

Regards,
Chris.

 

[modest]

Ken’s use of the function DCount() directly does not have that restriction but leaves it open to be type cast by the system depending on the return value.

Therefore Ken’s solution was, and still remains, better than yours…prevention of runtime errors.

Hmmm.... well put... for the devil's advocate.

If you're implying that not having a variable is a solution, you must have never debugged someone else's code. Not to mention, you must not have been programming that long.

While you are correct an integer can hold a value between -32768 and 32767, it would be suggested that the programmer replace integer with a more appropriate datatype... not get rid of the variable altogether. If you don't think variable names are important, go back to binary.

You should read or try to comprehend why we have variables.. we could replace variables throughout the program with just function calls and typedefs, but we don't because: 1) It takes too much space and 2) we go back and re-read our code and make changes.

Sure you might understand what's going on in your code right now... but go back after a few months and look at it, especially after doing several other projects. Leaving good comments and having appropriate variable names is very important with regards to understanding in a given timeframe.

 

[modest]

I did make a mistake in my recordset post however. Whether you choose to use the recordset or the dcount, it's always important to have the correct code.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim rsCount As Integer
Dim queryNameOrSQL As String

queryNameOrSQL = "<Query Name or the SQL Statement goes here>"

Set db = CurrentDb
Set rs = db.OpenRecordset(queryNameOrSQL)
[B]rs.MoveLast[/B]

rsCount = rs.RecordCount

You'll notice I inserted the movelast. I'm surprised no one had caught me on this. Best said in my bood "Access 2003 VBA Programmer's Reference" by Cardoza, Hennig, Seach, and Stein: "Recordsets do not always return their entire dataset immediately; they can take quite some time to populate; the more rows they have to return, the longer they take. DAO returns a pointer to the recordset early, so you can get on with doing whatever it is you want to do, assuming that the latter rows will have been returned by the time you get to them."

So RecordCount returns the number of records that has been processed so far. The .MoveLast insures that all records will be processed before the program continues =)

 

[ChrisO]

If you're implying that not having a variable is a solution, you must have never debugged someone else's code.”

Who cares about someone else's code?… I just ‘debugged’ yours.


Oh! Another post while I was replying. But same error…use of integer for record set count.

Possible Overflow…

Seconds out…round two. (ding)

 

[ChrisO]

“So RecordCount returns the number of records that has been processed so far. “

This is also incorrect, processing of records has nothing whatsoever to do with it…it is the number of records traversed.

 

[modest]

Chris,

This isn't the place to show how elementary your vocabulary is. Traversed, processed, accessed... it is all done through the processor. For my purpose they all mean the same thing. Additionally, I am not the one that you want to be technical with, especially in a post over a simple matter. However, I stand by my word choice "process" was used in the correct context.

Furthermore, I do not know what error you are receiving. "Possible Overflow" HA! How hard is it to implement whether to use an integer or a double? I continue to think that with each of your post, your age is becoming clearer and clearer. Do you not understand what the purpose of variables are? I thought I explained this fairly well (at least the highlight of it).

Finally, please debug your own code for now on. I have no further patience with you on this forum. I seek not to answer any more of your questions or look for you to answers to any of mine. If there was a block function on this site, I would use it. Debugging is an important process in coding any sort of application. Maybe if an employer ever sees potential in you, you'll have the ability to work for a company where others work as well. In doing so, you may actually get to work on programs that have already been built. These are the times that debugging is most important.

Go read up on the difference between a high-level language and a low-level language.

Goodnight.

 

[ChrisO]

Modest, there appears to be nothing of your name in your name.

Not that long ago I said, on this site, that something that Pat Hartman said was crap. Pat and I survived that exchange simply for the reason that Pat Hartman understands her knowledge and does not resent worthwhile criticism (if it was), from anyone… least of all me.

But you flinched and therefore you failed… using words of personal resentment which Pat and I never did.

If you believe you are a good coder, which I assume you are (I think you might be between 40 and 50), then argue your case in code and not what seems like some vindictive resentment.

For example…

"Possible Overflow" HA! How hard is it to implement whether to use an integer or a double?

What would be the point of converting an integer to a double for a recordset count?

I have said before that there are levels of knowledge, seems almost ridiculous to say so, and those of us just starting out need to be cut a little more ‘slack’. Conversely, those of us that have been around for a while need, and require, less ‘slack’.

If you wish to be taken seriously in this business then you must be prepared to take what comes at you. The simple fact is that we all, starting out or been around for a while, learn not from our triumphs but from our failures.

Start by changing your name Modest, because that you ain’t.

 

[Rich]

... using just dcount() makes you look back at it's definition, when you've forgotten what it does.

How can you forget what DCount does when the clue is in the function name?

 

[RoyVidar]

For the interest of those who may read this thread to get information on recordcount, I'd suggest also including a test for whether the recordset returns records or not, prior to issuing a movelast, for instance:

Set rs = db.OpenRecordset(queryNameOrSQL)
if ((not rs.bof) and (not rs.eof)) then
    rs.MoveLast
   ' continue processing...
else
    msgbox "no records returned..."
end if

else it may give the 3021.

modest, you may find that several have spotted your error, but for some reason have not posted it. Reasons for that could of course be because of respect, or perhaps because the original questioner seems to be happy with the DCount function, or because one isn't interested in a useless fight, persuing the matter, wasn't found worthwhile.

Grumpy as I am, I seldom listen to anybody, but I do find it worthwhile to listen to ChrisO (and several others on this site).

To put in my tuppence on variables - I use variables when there's a need to use the resulting value more than once. If this DCount is used only to test whether there are records or not, I would not use a variable (as in KenHigg's suggestion).

If I need the recordcount in the further processing, I'd assign it to a variable in stead of calling the function more than once.

Edit: As per ChrisO's comment, since the .RecordCount property return value is Long, I'd use a matching declaration of the receiving variable, if a variable is necessary -> Long.