Recordcount returns -1 even after MoveLast

[TTThio]

Hi all,

I just did a Select Unique to return a recordset.
I'm using rst.movelast
then rst.recordcount to return the total records.
It keeps giving me -1 instead of the real number.
I know it's not much , only 100, so how come it didn't work as it's supposed to be?

Thanks ahead,
Tin Tin

 

[stoolpigeon]

The code you use to open the recordset would be helpful.

Odds are you are not using a recordset type that support the recordcount property. When that is the case- it will always return -1.

 

[TTThio]

I'm taking the data from ODBC used in Excel.
The one makes me confused is,
I'm setting a criteria for the data pulled, exp.
if Name = A, Name = B, etc.
When it goes to Name = A, the rst.recordcount does return a number ( = 5), also for B ( = 15). But only when Name = E, the rst.recordcount returns -1 (C numbers does outstand others' numbers = 100).

Also, what kind of recordset supports recordcount property? I've tried to declare it as DAO.Recordset, still didn't work.

Thanks again.

 

[MrEGuest]

TTThio

You have to set the CursorLocation property to adUseClient if you want to use the recordcount property for an ADO rs.

Hope this helps!
Josh

 

[TTThio]

I'm using CreateWorkspace and OpenConnection to go directly to the data source (ODBC). I guess it's not an ADO nor DAO.

Thanks though.

 

[barryna]

Do you use a select unique for the names(A,B) that do return a recordcount? If not, your problem could lie in the Select unique statement you are using. Try a general Select statement and see if it returns a recordcount for Name = E.

If that isn't it, it is also helpful to try to movelast and then movefirst. Sometimes things get screwy and the recordcount won't read right if you don't span all the way across the recordset.

Good luck.

 

[TTThio]

Thank you for the reply.
There's nothing wrong with the Select Unique statement. And I've also tried movelast, movefirst. It still returns -1.

However, I've found another way to return the total record by using Do...Loop Until, MoveNext, and then saving the total = total + 1 until rst.EOF.

Thanks again everybody.

Tin Tin