This might fix it:
Do
Loop Until .EOF
Main Topics
Browse All TopicsI'm currently utilizing a function that creates a worksheet, and then pulls data from an access database query to populate the new worksheet. In particular, I'm querying an Access query based on a date field. I'm expecting 3 records, which is what I get when I print the sql and paste in an Access query. I'm only receiving the first 2 records, however. What is odd is that if I break in the middle of the code, and then resume, I will often get the proper number of records (3). I'm wondering how I can consistently ensure that I'm pulling an accurate recordset. Any ideas?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
To give you an idea, it's not really the looping that is incorrect. Like I said, if I break in the middle, or rahter, if I put a break point, and run it, it will show the correct record count of 3. If I let it just run through, it will only show 2. I think it has something to do with it not having enough time to catch all 3 records upon opening the recordset, but it's very strange. Other thoughts?
As others have indicated, .RecordCount with ADO Recordsets aren't guaranteed to be accurate. Using Do loops, or some other method that checks for EOF, is the correct way to do that.
Could it be that you're comparing dates incorrectly? Access stores the time with your dates (unless you specifically format that date BEFORE it's stored in the table), so
sometimes comparing
MyDateField = SomeDateValue
won't work IF you have value like this:
MyDAteField = 01/01/2009 12:13:33
SomeDateValue = 01/01/2009
You can try Format:
SELECT * FROM SomeTable WHERE Format("mm/dd/yyyy", MyDateField) = #01/01/2009#
LSM,
This is a good thought, but still isn't getting to the core issue (works when I break, but not other times). As a workaround, I entered:
msgbox "Loading Trades"
directly after I assign strSql. This seems to work, however, I'd like to determine how to ensure that I always load the correct number of records. Other thoughts?
<Any idea why though?>
It is as timing issue. Some things are done asynchronously like links to spreadsheets. This means that some operations occur in parallel to the code execution. The do loop is providing some time for the asynchronous possess to complete. I suggested the do loop based on time as a quick debug tool. Using a set time delay is not a good idea since the timing can change for different computers, networks, etc. Better to wait until you know the process is complete. For this, something like:
.Open strSql, cn, adOpenStatic 'adLockOptimistic, adCmdText
Do
Loop Until rs!SomeField = Something expected or Loop While IsNull(rs!SomeField)
the .recordcount property was not the problem. I was not controlling the loop by using the .recordcount property so why would that cause any trouble? Plus, it's not the loop itself that is ending prematurely, but rather the data set being pulled was recognizing only 2 rows, and therfore .eof at the 2nd record. I'm not sure that a "better loop" really takes care of this issue.
<but this is not the ideal solution.>
Yes, see http:#a25116167 or http:#25116149 for a better solution.
Business Accounts
Answer for Membership
by: thenelsonPosted on 2009-08-16 at 15:43:10ID: 25110750
Try changing
Do While Not .EOF
To
Do Until .EOF
(Probably not the problem.)
What is between?
Do While Not .EOF
Loop