Recordset contains only one record - should contain more

[Anita Weiler]

I am developing an application in Word that pulls data
from an Access database to insert into a Word document.
I've done this successfully many times in the past but
now I am encountering a problem that has me stymied.

When I open a recordset directly from a table, I get all
the records. However, when I open the recordset from a
query, I get only the first record. The query works fine
in Access. I also get only the first record if I open
directly from the table using the OpenSnapshot constant.
I'm using Word 2002 and Access 2002. I have the latest
update for Microsoft Jet installed.

This code works:

Sub GetRecords()
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("c:\resolutions\ATA
Resolutions.mdb")
Set rs = db.OpenRecordset("tblResolution")
MsgBox rs.RecordCount & " records"
End Sub

The following two examples give me only one record:
Sub GetRecords()
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("c:\resolutions\ATA
Resolutions.mdb")
Set rs = db.OpenRecordset("tblResolution", dbOpenSnapshot)
MsgBox rs.RecordCount & " records"
End Sub

Sub GetRecords()
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("c:\resolutions\ATA
Resolutions.mdb")
Set rs = db.OpenRecordset("qWordResolution")
MsgBox rs.RecordCount & " records"
End Sub

Any ideas? I'm desperate - I have to have this done
tomorrow!

Thanks,
Anita

 

[Allen Browne]

RecordCount reports the number or records accessed so far.
For a recordset based on a query or attached table, that's either zero (if
there are no records) or one (if there are any records).

If you need an accurate count, MoveLast to force Access to load all the
records.