record count problem

[lana]

Hi there,
I have a table which has 4 records. I use the followings to count the records:
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tbl_calc")
rst.MoveLast
aaa = rst.RecordCount
rst.MoveFirst

this returns 44 instead of 4 !!!!!
Any ideas?
Thanks

 

[isladogs]

You're seeing double? Too much alcohol?

Try this:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tbl_calc")
rst.MoveFirst
rst.MoveLast
aaa = rst.RecordCount

 

[MarkK]

both will fail if the recordset it empty, try...

with currentdb.openrecordset(sql)
   if not .eof then .movelast
   GetCount = .recordcount
   .close
end with

To get a RecordCount you want to check for EOF, and you never need to MoveFirst.

 

[isladogs]

Hi

Well actually I meant to write

Code:
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tbl_calc")
rst.MoveLast
rst.MoveFirst
aaa = rst.RecordCount

That's how I normally do it
However, having read the last post from MarkK, I've checked and he is indeed right.
I've been doing an unnecessary MoveFirst line for years!

 

[static]

aaa = currentdb.OpenRecordset("select count(1) from tbl_calc")(0)

 

[lana]

Thank you all.

Actually my own code works. I compacted the database then it worked?!

Table in question is a temp one and the data in it is cleared all the time.
Maybe something happens when data is deleted.

Thanks a lot.