Drew Wutka
DWUTKA at Marlow.com
Tue May 27 09:26:33 CDT 2008
Are you actually using the record count for something? If not, the best way to loop through is to just do this: If rs1.EOF=False then rs1.MoveFirst 'If there are records go to the first one Do Until rs1.EOF=True 'Go until we hit the end 'do what you want with the recordset here Rs1.MoveNext Loop Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins Sent: Monday, May 26, 2008 11:37 PM To: Access Developers discussion and problem solving Subject: [AccessD] RecordSet question... Sorry for all the easy ones today (heh, at least I hope they are easy). ok.. When I run the following code in the SQL query window I get 11 records returned - which is what I want and expect for the given input variables. ' ----- CODE START -------- iYEAR_VAR = iYEAR1 sSQL = "" sSQL = "SELECT tbl_FY.FY_ID, tbl_FY_Period.FY_P " sSQL = sSQL & "FROM tbl_FY INNER JOIN tbl_FY_Period ON tbl_FY.FY_ID = tbl_FY_Period.FY " sSQL = sSQL & "WHERE (((tbl_FY.FY_ID)=" & iYEAR_VAR & "));" 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: 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. regards Darryl. This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses. No warranty is made that this material is free from computer virus or any other defect or error. Any loss/damage incurred by using this material is not the sender's responsibility. The sender's entire liability will be limited to resupplying the material. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.