VBA ADO recordset returning one less record than actual query

This might fix it:
Do

Loop Until .EOF

Do it this way:

' open the recordset
With rs
 
      ' open the recordset
      .Open strSql, cn, adOpenStatic 'adLockOptimistic, adCmdText
      '                        .MoveLast
 
   
      '                        If .RecordCount > 0 Then
      '                              .MoveFirst
      Do While Not .EOF
          'print data here
 
      Loop
      '                        End If
End With

With rs
      ' open the recordset
      .Open strSql, cn, adOpenStatic 'adLockOptimistic, adCmdText
      Do While Not .EOF
          'print data here
 
      Loop
End With

acperkins,

I'm not sure what was different in what you did.  Either way, neither way has fixed this issue.  Any other ideas?

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?

How about using for next instead of do loop?
Dim i As Integer
For i = 1 To .RecordCount

Next i

thenelson,
The problem with that is .recordcount would equal 2 so it would be incorrect.  What's the safest way to load a recordset if I have the query?  This obviously has problems associated with it.

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?

Is strDate a string and rngDates a date? So you are converting a date to a string then converting it look to a dote again in strSQL.  Try this:

strSql = "Select " & strDetailFields & " from qryTradeData where [date]=#" & rngDates & "#"

Is rngDates coming from a spreadsheet?  If so, then perhaps there is a timing issue.   Try placing a DoEvents before the .Open strSql line:

DoEvents
.Open strSql, cn, adOpenStatic 'adLockOptimistic, adCmdText

Tried both, same issue.  Again, the only thing that seems to work is inserting a msgbox.

After further testing, it seems that even with a larger data set, it always misses the last record UNLESS I break with a break point or a msgbox.

thenelson,

That worked.  Any idea why though?  Also, do you think the msgbox approach is at all flawed.  Please explain, and thanks.

<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)

or better than  Loop While IsNull(rs!SomeField)
Loop Until Len(rs!SomeField & "") > 0
Takes care of zero length string and is empty.

>>I'm not sure what was different in what you did.<<
You did not notice how I commented out the use of the RecordCount porperty?  You reliance on that property is what is causing all your grief.  If you are into pain, that is fine, otherwise do not use it. Period.

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.

Whatever works for you.

Good luck.

There is some timing issue, but this is not the ideal solution.