When I run this code here is what is happening. Everything works fine but the loop keeps going forever. EOF keeps showing False. If I interrupt VBA and check out the immediate window:
?myResult
It shows the same record (the first one out of ONLY THREE that the query should be returning), showing up like 100 times. And EOF=False.
Since I've also changed the actual query defs (maybe not best practice for efficiency, but good for this reason): I also get the benefit of running the query manually after interrupting VBA. Sure enough, the query only returns 3 results.
Why is my Loop Until EOF=True behaving like this?
CODE
Dim myDb As DAO.Database
Dim myResult As String
Dim myRs As DAO.Recordset
Dim myPause As Boolean
Dim strSql As String
Dim myDate As String
myDate = Format(Now, "mm/dd/yyyy")
Set myDb = DAO.OpenDatabase("C:\Documents and Settings\" & GetUserID & "\Desktop\Sharepoint_Report_Request.accdb")
myDb.TableDefs("Report Request").RefreshLink 'sharepoint link, have to actually refresh, it doesn't refresh itself
strSql = "SELECT [Report Request].[Description of Request], [Report Request].[Date Stamp] " _
& " From [Report Request] WHERE [Report Request].[Date Stamp]>=#" & myDate & "# And " _
& " [Report Request].[Date Stamp]<=#" & myDate & " 23:59:59#;"
myDb.QueryDefs("qry_KPI_REQUEST").Sql = strSql
On Error Resume Next 'the rare instance when I'll use this generally-bad-idea code,
'and it's used very sparingly here (wrapping a couple lines only)
'in this case I WANT an error, but not run-time, just for code's sake
Set myRs = myDb.OpenRecordset("qry_KPI_REQUEST")
Do Until myRs.EOF
myResult = myResult & vbNewLine & myRs.Fields("Description of Request").Value
Loop
Dim myResult As String
Dim myRs As DAO.Recordset
Dim myPause As Boolean
Dim strSql As String
Dim myDate As String
myDate = Format(Now, "mm/dd/yyyy")
Set myDb = DAO.OpenDatabase("C:\Documents and Settings\" & GetUserID & "\Desktop\Sharepoint_Report_Request.accdb")
myDb.TableDefs("Report Request").RefreshLink 'sharepoint link, have to actually refresh, it doesn't refresh itself
strSql = "SELECT [Report Request].[Description of Request], [Report Request].[Date Stamp] " _
& " From [Report Request] WHERE [Report Request].[Date Stamp]>=#" & myDate & "# And " _
& " [Report Request].[Date Stamp]<=#" & myDate & " 23:59:59#;"
myDb.QueryDefs("qry_KPI_REQUEST").Sql = strSql
On Error Resume Next 'the rare instance when I'll use this generally-bad-idea code,
'and it's used very sparingly here (wrapping a couple lines only)
'in this case I WANT an error, but not run-time, just for code's sake
Set myRs = myDb.OpenRecordset("qry_KPI_REQUEST")
Do Until myRs.EOF
myResult = myResult & vbNewLine & myRs.Fields("Description of Request").Value
Loop
by the way, the reason I have On Error Resume next (I revert back to other err handling shortly thereafter), is just to trap for "no current record". I realize this probably can be improved but that is another story. I tried commenting it out, and this same result still happened w/out a run time error.