Edward S Zuris
edzedz at comcast.net
Tue May 27 01:22:49 CDT 2008
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