OpenRecordset returns less number of rows.

[nandhinimoorthy]

Hi ,

I have a query called "SubQuery". It should return 3 records.When I run that query alone,it is returning 3 records.But when I run through this vba code,and have it's recordcount it gives me 1 record.

The subquery is a simple select query.

Dim dbs As Database
Dim rs1 As Recordset, qdf1 As QueryDef

Set dbs = CurrentDb
Set qdf1 = dbs.QueryDefs(" SubQuery")
Set rs1 = qdf1.OpenRecord set(dbOpenSnaps hot)
rs1.recordcount

Please can anybody say me how to resolve it.It's needed immediately.

Thanks in advance for your help.

[ajalwaysus]

Try This,

Dim dbs As Database
Dim rs1 As Recordset, qdf1 As QueryDef

Set dbs = CurrentDb
Set qdf1 = dbs.QueryDefs("SubQuery")
Set rs1 = qdf1.OpenRecordset(dbOpenSnapshot)
rs1.MoveLast
rs1.MoveFirst
rs1.recordcount

To save space and time, VBA usually won't scan your entire recordset unless you tell it to. (i.e. MoveLast) So as you had it, VBA only sits on the first record, hence only one record is the recordcount, but if you tell it to MoveLast then it will scan all records which in turn returns a full count.

-AJ

[nandhinimoorthy]

Hi ,

Thank you for ur reply..It worked fine...