No Current Record at end of loop?

[Steven Britton via AccessMonster.com]

For some reason I get an error message saying there is no current record
and I hit debug it take me into the code and highlights the line in Case
Else .MoveNext??? The Value of the .EOF is True, why is it still in the
loop?

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDesc As String

Public Sub ParseECN()

Set db = CurrentDb()
Set rs = db.OpenRecordset("Import")

With rs
.MoveFirst
Do While Not .EOF

If InStr(1, !Field1 & "", ":") <> 0 Then
strDesc = Left(!Field1, InStr(1, !Field1, ":") - 1)
Else: .MoveNext
End If

Select Case strDesc

Case "Description"
MsgBox "here"
.MoveNext

Case Else
'ignore
.MoveNext

End Select

Loop

End With

End Sub

 

[Dirk Goldgar]

For some reason I get an error message saying there is no current
record and I hit debug it take me into the code and highlights the
line in Case Else .MoveNext??? The Value of the .EOF is True, why is
it still in the loop?

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDesc As String

Public Sub ParseECN()

Set db = CurrentDb()
Set rs = db.OpenRecordset("Import")

With rs
.MoveFirst
Do While Not .EOF

If InStr(1, !Field1 & "", ":") <> 0 Then
strDesc = Left(!Field1, InStr(1, !Field1, ":") - 1)
Else: .MoveNext
End If

Select Case strDesc

Case "Description"
MsgBox "here"
.MoveNext

Case Else
'ignore
.MoveNext

End Select

Loop

End With

End Sub

Suppose that when you pass through the logic below, you are currently
positioned at the last record in the recordset.

If InStr(1, !Field1 & "", ":") <> 0 Then
strDesc = Left(!Field1, InStr(1, !Field1, ":") - 1)
Else: .MoveNext
End If

Now suppose that the content of Field1 in that record is such that
execution takes the "Else" path. Now the recordset is positioned at
EOF.

Now execution proceeds on down to the statement:

Select Case strDesc

No matter which of these cases is true:

Case "Description"
MsgBox "here"
.MoveNext

Case Else
'ignore
.MoveNext

.... you're going to attempt to move the recordset to the next record.
But the recordset is already at EOF, so you can't do that -- there is no
current record, so you can't move to the next one.

 

[Ken Snell [MVP]]

You have two movenext commands inside the do loop. You are not testing for
EOF after the first movenext, so it's possible that that first movenext
takes you to EOF but the second one doesn't get "bypassed" if you're at EOF.

I think perhaps the Else: .MoveNext and End If lines are in the wrong place.
Try this replacement code:
----------------

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDesc As String

Public Sub ParseECN()

Set db = CurrentDb()
Set rs = db.OpenRecordset("Import")

With rs
.MoveFirst
Do While Not .EOF

If InStr(1, !Field1 & "", ":") <> 0 Then
strDesc = Left(!Field1, InStr(1, !Field1, ":") - 1)


Select Case strDesc

Case "Description"
MsgBox "here"
.MoveNext

Case Else
'ignore
.MoveNext

End Select

Else: .MoveNext
End If
Loop

End With

End Sub