[AccessD] RecordSet question...

 Here is some code to do find # of records.
 If no records the counter will be zero.

    ' *****************************************************
    '   Get the Number of Sales Records.
    '
    Set rsPull = dbsV2H.OpenRecordset(sSQL)

    lKnt2 = 0

    On Error Resume Next

    ' *****************************************************
    '   Get to the last Record
    '
    rsPull.MoveLast

    ' *****************************************************
    '   See how many we have.
    '
    lKnt2 = rsPull.RecordCount

    ' *****************************************************
    '   Get bact to the top of the record set
    '
    rsPull.MoveFirst

    On Error GoTo 0


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stuart
McLachlan
Sent: Monday, May 26, 2008 10:10 PM
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