Counting recordsets

[Regnab]

The problem I'm having is the rsa.RecordCount always returns a '1' when
I know there should be about 1600 records returned. I read somewhere
that one should use '.MoveLast' before recordcount but that returns ar
error 'Invalid Operation'. And when I do the bottom loop, the counter
happily clicks over the right number of times. Me no understand...
I've got the following code (this is just a simplified example - I want
to use the recordcount in an If Statement):

Dim db As DAO.Database
Dim rsa As DAO.Recordset
Dim strsql As String

strsql = "SELECT * " & _
"FROM tblInductionLots;"

Set db = DBEngine(0)(0)
Set rsa = db.OpenRecordset(strsqla, dbOpenForwardOnly)

MsgBox rsa.RecordCount

Counter = O
Do Until rsa.EOF
Counter = Counter + 1
rsa.MoveNext
MsgBox Counter
Loop

[Allen Browne]

The MoveLast should work, unless there are no records at all.

It is not very useful to open a forward-only recordset and jump to the end,
so try:
Set rsa = db.OpenRecordset(strsqla, dbOpenDynaset)
If rsa.RecordCount > 0 Then
rsa.MoveLast
MsgBox rsa.RecordCount
End If

I also notice you have a variable named Counter that is not Dim'd at the top
of the procedure, and your code seems to be setting this undeclared variant
to the letter O (which is probably treated as another undeclared empty
variant). You can avoid these problems by adding this to the top of every
module:
Option Explicit
To get Access to do this for you for future modules, choose Options from the
Tools menu (in the code window). On the Editor tab, click the box for:
Require Variable Declaration

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

The problem I'm having is the rsa.RecordCount always returns a '1' when
I know there should be about 1600 records returned. I read somewhere
that one should use '.MoveLast' before recordcount but that returns ar
error 'Invalid Operation'. And when I do the bottom loop, the counter
happily clicks over the right number of times. Me no understand...
I've got the following code (this is just a simplified example - I want
to use the recordcount in an If Statement):

Dim db As DAO.Database
Dim rsa As DAO.Recordset
Dim strsql As String

strsql = "SELECT * " & _
"FROM tblInductionLots;"

Set db = DBEngine(0)(0)
Set rsa = db.OpenRecordset(strsqla, dbOpenForwardOnly)

MsgBox rsa.RecordCount

Counter = O
Do Until rsa.EOF
Counter = Counter + 1
rsa.MoveNext
MsgBox Counter
Loop

[PC Datasheet]

Change this part of your code:
MsgBox rsa.RecordCount

Counter = O
Do Until rsa.EOF
Counter = Counter + 1
rsa.MoveNext
MsgBox Counter
Loop

To:
If rsa.RecordCount = 0 Then
rsa.MoveLast
End If
MsgBox rsa.RecordCount
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!


"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

The problem I'm having is the rsa.RecordCount always returns a '1' when
I know there should be about 1600 records returned. I read somewhere
that one should use '.MoveLast' before recordcount but that returns ar
error 'Invalid Operation'. And when I do the bottom loop, the counter
happily clicks over the right number of times. Me no understand...
I've got the following code (this is just a simplified example - I want
to use the recordcount in an If Statement):

Dim db As DAO.Database
Dim rsa As DAO.Recordset
Dim strsql As String

strsql = "SELECT * " & _
"FROM tblInductionLots;"

Set db = DBEngine(0)(0)
Set rsa = db.OpenRecordset(strsqla, dbOpenForwardOnly)

MsgBox rsa.RecordCount

Counter = O
Do Until rsa.EOF
Counter = Counter + 1
rsa.MoveNext
MsgBox Counter
Loop

[scooper]

Allen
Why don'y you use the DCOUNT() funcion to check for records?
Bob

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

The MoveLast should work, unless there are no records at all.

It is not very useful to open a forward-only recordset and jump to the end, so try:
Set rsa = db.OpenRecordset(strsqla, dbOpenDynaset)
If rsa.RecordCount > 0 Then
rsa.MoveLast
MsgBox rsa.RecordCount
End If

I also notice you have a variable named Counter that is not Dim'd at the top of the procedure, and your code seems to be setting this undeclared variant to the letter O (which is probably treated as another undeclared empty
variant). You can avoid these problems by adding this to the top of every
module:
Option Explicit
To get Access to do this for you for future modules, choose Options from the Tools menu (in the code window). On the Editor tab, click the box for:
Require Variable Declaration

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

The problem I'm having is the rsa.RecordCount always returns a '1' when
I know there should be about 1600 records returned. I read somewhere
that one should use '.MoveLast' before recordcount but that returns ar
error 'Invalid Operation'. And when I do the bottom loop, the counter
happily clicks over the right number of times. Me no understand...
I've got the following code (this is just a simplified example - I want
to use the recordcount in an If Statement):

Dim db As DAO.Database
Dim rsa As DAO.Recordset
Dim strsql As String

strsql = "SELECT * " & _
"FROM tblInductionLots;"

Set db = DBEngine(0)(0)
Set rsa = db.OpenRecordset(strsqla, dbOpenForwardOnly)

MsgBox rsa.RecordCount

Counter = O
Do Until rsa.EOF
Counter = Counter + 1
rsa.MoveNext
MsgBox Counter
Loop

[Terry Kreft]

If you want an accurate recordcount then it is easier and frequently quicker
to do the following

Dim db As DAO.Database
Dim rsa As DAO.Recordset
Dim strsql As String
Dim Counter As Long

strsql = "SELECT Count(*) " & _
"FROM 3036023_A;"

Set db = DBEngine(0)(0)

Set rsa = db.OpenRecordset(strsql, dbOpenSnapshot)

MsgBox rsa.Fields(0) & ""
--
Terry Kreft

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

The problem I'm having is the rsa.RecordCount always returns a '1' when
I know there should be about 1600 records returned. I read somewhere
that one should use '.MoveLast' before recordcount but that returns ar
error 'Invalid Operation'. And when I do the bottom loop, the counter
happily clicks over the right number of times. Me no understand...
I've got the following code (this is just a simplified example - I want
to use the recordcount in an If Statement):

Dim db As DAO.Database
Dim rsa As DAO.Recordset
Dim strsql As String

strsql = "SELECT * " & _
"FROM tblInductionLots;"

Set db = DBEngine(0)(0)
Set rsa = db.OpenRecordset(strsqla, dbOpenForwardOnly)

MsgBox rsa.RecordCount

Counter = O
Do Until rsa.EOF
Counter = Counter + 1
rsa.MoveNext
MsgBox Counter
Loop

[Keith W]

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Dr*****************@newsread1.news.atl.earthl ink.net...

To:
If rsa.RecordCount = 0 Then
rsa.MoveLast
End If
MsgBox rsa.RecordCount

Erm, I don't think so.

[Allen Browne]

Yes, DCount() would be perfectly fine if you want to check for records, or
even DLookup() if you just want to know if there are any matches.

I assumed the aim of OP here was to learn how to walk through/handle
recordsets.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"scooper" <ro*******@optusnet.com.au> wrote in message
news:43***********************@news.optusnet.com.a u...

Allen
Why don'y you use the DCOUNT() funcion to check for records?
Bob

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

The MoveLast should work, unless there are no records at all.

It is not very useful to open a forward-only recordset and jump to the

end,

so try:
Set rsa = db.OpenRecordset(strsqla, dbOpenDynaset)
If rsa.RecordCount > 0 Then
rsa.MoveLast
MsgBox rsa.RecordCount
End If

I also notice you have a variable named Counter that is not Dim'd at the

top

of the procedure, and your code seems to be setting this undeclared

variant

to the letter O (which is probably treated as another undeclared empty
variant). You can avoid these problems by adding this to the top of every
module:
Option Explicit
To get Access to do this for you for future modules, choose Options from

the

Tools menu (in the code window). On the Editor tab, click the box for:
Require Variable Declaration
"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

> The problem I'm having is the rsa.RecordCount always returns a '1' when
> I know there should be about 1600 records returned. I read somewhere
> that one should use '.MoveLast' before recordcount but that returns ar
> error 'Invalid Operation'. And when I do the bottom loop, the counter
> happily clicks over the right number of times. Me no understand...
>
>
> I've got the following code (this is just a simplified example - I want
> to use the recordcount in an If Statement):
>
> Dim db As DAO.Database
> Dim rsa As DAO.Recordset
> Dim strsql As String
>
> strsql = "SELECT * " & _
> "FROM tblInductionLots;"
>
> Set db = DBEngine(0)(0)
> Set rsa = db.OpenRecordset(strsqla, dbOpenForwardOnly)
>
> MsgBox rsa.RecordCount
>
> Counter = O
> Do Until rsa.EOF
> Counter = Counter + 1
> rsa.MoveNext
> MsgBox Counter
> Loop

[paii, Ron]

DCOUNT() is very slow compared to movelast on an open recordset.

"scooper" <ro*******@optusnet.com.au> wrote in message
news:43***********************@news.optusnet.com.a u...

Allen
Why don'y you use the DCOUNT() funcion to check for records?
Bob

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

The MoveLast should work, unless there are no records at all.

It is not very useful to open a forward-only recordset and jump to the

end,

so try:
Set rsa = db.OpenRecordset(strsqla, dbOpenDynaset)
If rsa.RecordCount > 0 Then
rsa.MoveLast
MsgBox rsa.RecordCount
End If

I also notice you have a variable named Counter that is not Dim'd at the

top

of the procedure, and your code seems to be setting this undeclared

variant

to the letter O (which is probably treated as another undeclared empty
variant). You can avoid these problems by adding this to the top of every module:
Option Explicit
To get Access to do this for you for future modules, choose Options from

the

Tools menu (in the code window). On the Editor tab, click the box for:
Require Variable Declaration

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

The problem I'm having is the rsa.RecordCount always returns a '1' when I know there should be about 1600 records returned. I read somewhere
that one should use '.MoveLast' before recordcount but that returns ar
error 'Invalid Operation'. And when I do the bottom loop, the counter
happily clicks over the right number of times. Me no understand...
I've got the following code (this is just a simplified example - I want to use the recordcount in an If Statement):

Dim db As DAO.Database
Dim rsa As DAO.Recordset
Dim strsql As String

strsql = "SELECT * " & _
"FROM tblInductionLots;"

Set db = DBEngine(0)(0)
Set rsa = db.OpenRecordset(strsqla, dbOpenForwardOnly)

MsgBox rsa.RecordCount

Counter = O
Do Until rsa.EOF
Counter = Counter + 1
rsa.MoveNext
MsgBox Counter
Loop

[StopThisAdvertising]

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:Dr*****************@newsread1.news.atl.earthl ink.net...

Change this part of your code:
MsgBox rsa.RecordCount

Counter = O
Do Until rsa.EOF
Counter = Counter + 1
rsa.MoveNext
MsgBox Counter
Loop

To:
If rsa.RecordCount = 0 Then
rsa.MoveLast
End If
MsgBox rsa.RecordCount
Incorrect answer (as Keith already mentioned) and still continuously advertising as a so-called resource ???
If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
These 1000 (if at all a real figure..) is only the result of
-- 4 years abusing the newsgroups.
-- 4 years blatantly advertising and job hunting.

You only care about making money, and you act as if the groups are your private hunting ground.
So why would ANYBODY ever trust a person like you and hire you?
************************************************** ******
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

Need a psychiatrist ... Contact me!

Arno R

[David W. Fenton]

"paii, Ron" <pa**@packairinc.com> wrote in
news:tr******************************@athenet.net:

DCOUNT() is very slow compared to movelast on an open recordset.

SELECT COUNT(*)

will be faster than either of them.

If you want eo know exactly how many records are in a recordset that
you're going to use for some other purpose, then using .MoveLast to
check the number of records is valid.

On the other hand, if you want to see if your recordset returns some
records, you don't need an exact count, in any event -- you need
only check if .RecordCount > 0.

If you need to know exactly how may records you're going to act
upon, then there's no reason to .MoveLast first, just do your
operation and when finished, you should be at the last record, and
then .RecordCount will be accurate.

So, I'm basically having some difficulting imagining a scenrio where
you'd need the exact recordcount that couldn't be very efficiently
done ay anything *other* than DCount(). DCount() is only really a
valid choice if all you need is a mere count of the records
returned, and should have no advantages over SELECT COUNT(*).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Keith W" <he**@there.com> wrote in
news:43**********@glkas0286.greenlnk.net:

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Dr*****************@newsread1.news.atl.earthl ink.net...

To:
If rsa.RecordCount = 0 Then
rsa.MoveLast
End If
MsgBox rsa.RecordCount

Erm, I don't think so.

Why not? It looks completely valid to me.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Wayne Gillespie]

On Thu, 10 Nov 2005 15:33:12 -0600, "David W. Fenton" <dX********@bway.net.invalid> wrote:

"Keith W" <he**@there.com> wrote in
news:43**********@glkas0286.greenlnk.net:

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Dr*****************@newsread1.news.atl.earthl ink.net...

To:
If rsa.RecordCount = 0 Then
rsa.MoveLast
End If
MsgBox rsa.RecordCount

Erm, I don't think so.

Why not? It looks completely valid to me.

Because if rsa.RecordCount = 0 you already know there are no records in the recordset so why do a MoveLast?

It should be -

If rsa.RecordCount > 0 Then
rsa.MoveLast
End If
MsgBox rsa.RecordCount

[David W. Fenton]

Wayne Gillespie <be*****@NOhotmailSPAM.com.au> wrote in
news:uq********************************@4ax.com:

On Thu, 10 Nov 2005 15:33:12 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Keith W" <he**@there.com> wrote in
news:43**********@glkas0286.greenlnk.net:

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Dr*****************@newsread1.news.atl.earthl ink.net...

To:
If rsa.RecordCount = 0 Then
rsa.MoveLast
End If
MsgBox rsa.RecordCount

Erm, I don't think so.

Why not? It looks completely valid to me.

Because if rsa.RecordCount = 0 you already know there are no
records in the recordset so why do a MoveLast?

It should be -

If rsa.RecordCount > 0 Then
rsa.MoveLast
End If
MsgBox rsa.RecordCount

Well, er, why didn't you just say that in your reply?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Wayne Gillespie]

On Fri, 11 Nov 2005 20:50:46 -0600, "David W. Fenton" <dX********@bway.net.invalid> wrote:

Erm, I don't think so.

Why not? It looks completely valid to me.

Because if rsa.RecordCount = 0 you already know there are no
records in the recordset so why do a MoveLast?

It should be -

If rsa.RecordCount > 0 Then
rsa.MoveLast
End If
MsgBox rsa.RecordCount

Well, er, why didn't you just say that in your reply?

Well, er, maybe because I didn't post a reply.