[AccessD] RecordSet question...

Stuart,

<<As Darren points out, unless you populate the recordset with a .MoveLast,
Access has no 
way to tell how many records there are.>>

  That's not quite true as it depends on the recordset type and size.  I
would have thought though that with only 11 records it should have been
fully populated it right off.  Maybe the behavior has changed.

  Any way, a .MoveLast will always guarantee the right number.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Tuesday, May 27, 2008 1:10 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] RecordSet question...

See coments in line.

On 27 May 2008 at 14:37, Darryl Collins wrote:
...
> Set rs1 = db.OpenRecordset(sSQL)
> 
> iRSCount = rs1.RecordCount
> ' ----- CODE END --------
> 
> However the rs1.RecordCount is returning 1, not 11, which doesn't seem
correct to me(?).  Also what I want to do is this:

As Darren points out, unless you populate the recordset with a .MoveLast,
Access has no 
way to tell how many records there are.

...

> Find out how many records are in the RS (there should always be 12, except
for FY08 where there is 11 - don't ask).
> Loop Thru all the records and return the data in field "FYP" as a variable
and then write the value to a label in a report.
> 
> The bit I am stuck on is looping thru the recordset.  or is there a better
way altogether.  There are so many ways to make Access do stuff, sometimes I
think I choose one that is a fashion of the day for me and flog it, not
realising there is a much better/easier way.
> 

You don't need to find out how many records there are in advance for this
Just use:

While No rs1.EOF
    strText = rs1!FYP
   ..... 'Do what you want with the data in strText
    rs1.MoveNext
Wend


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com