record count mismatch

[ravindarjobs]

hello friends,

i have a table named "Technology"
it has few columns.
of them two are
1.Name
2. Type



i have written a query and exectued like this
my code goes like this

dim db as database
dim rs as Recordcount
set db=CurrentDb
set rs=db.OpenRecordset("select * from Technology where Technology.Type="Water" ")

if i execute this query in query wizard i get record count correctly.
but when i implement this query in my code, it displays record count as 2 every time.

same query , but why i am facing the problem?


Thank you

[FishVal]

hello friends,

i have a table named "Technology"
it has few columns.
of them two are
1.Name
2. Type



i have written a query and exectued like this
my code goes like this

dim db as database
dim rs as Recordcount
set db=CurrentDb
set rs=db.OpenRecordset("select * from Technology where Technology.Type="Water" ")

if i execute this query in query wizard i get record count correctly.
but when i implement this query in my code, it displays record count as 2 every time.

same query , but why i am facing the problem?


Thank you

Hi, there.

What does the Recordcount type / classname mean? Did you mean DAO.Recordset?

Anyway Recordset opening is asynchronous to VBA code executing. Recordset.RecordCount property doesn't return right value immediately after Recordset was opened. To force Recordset object to determine number of records use Recordset.MoveLast method.

e.g.

dim db as database
dim rs as DAO.Recordset
set db=CurrentDb
set rs=db.OpenRecordset("select * from Technology where Technology.Type='Water';")
rs.MoveLast
Debug.Print rs.RecordCount
 

[ADezii]

Hi, there.

What does the Recordcount type / classname mean? Did you mean DAO.Recordset?

Anyway Recordset opening is asynchronous to VBA code executing. Recordset.RecordCount property doesn't return right value immediately after Recordset was opened. To force Recordset object to determine number of records use Recordset.MoveLast method.

e.g.

dim db as database
dim rs as DAO.Recordset
set db=CurrentDb
set rs=db.OpenRecordset("select * from Technology where Technology.Type='Water';")
rs.MoveLast
Debug.Print rs.RecordCount
 

As indicated by FishVal, many times it is a good idea, if practical, to 'traverse' the Recordset for an accurate RecordCount. What I usually do is:

'RecordCount will always be valid with this Method
rs.MoveLast:rs.MoveFirst