Updating DAO Record Moves it to the End of the Recordset

I am trying to loop through a DAO recordset and replace characters as
needed. However, when the replacement consists of just appending a
character, the first record in the recordset seems to move to the end
of the recordset after the first iteration through the loop and has the
append done a second time. Here is the code (slightly modified to get
rid of the extra crud). This is legacy code so bare with me.

dim TempData as string, FindString as string, ReplaceString as string
FindString = "B.701"
ReplaceString = "B.7011"
Set DAORecord = m_SRDatabase.OpenRecordset( "SELECT MyField FROM
MyTable", dbOpenDynaset)
Do While Not DAORecord.EOF
TempData = DAORecord.Fields(0) & ""
TempString = Replace(TempData, FindString , ReplaceString )
If TempData <> TempString Then
DAORecord.Edit
DAORecord.Fields(0).Value = TempString
DAORecord.Update
End If
DAORecord.MoveNext
Loop

The preceding code will result in the first item in the recordset
having the string "B.70111" while all the others only have "B.7011".
I've traced through the code and this record is changed on the first
and then the last iterations of the loop.

I've gotten around this by replacing the Do While with this For/Next:

DAORecord.MoveLast
DAORecord.MoveFirst
For Count = 1 To DAORecord.RecordCount
.....same code inside
Next

So my fix does work, but I still want to know WHY it was happening in
the first place. Is this just a "feature" of DAO or was there some
error in the logic?

Thanks for your help.

Nothing obviously wrong jumps out at me. However, it's almost always more
efficient to use a query, rather than VBA.

UPDATE MyTable
SET MyField = Replace([MyField], "B.701", "B.7011")
WHERE MyField LIKE "*B.701*"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"frodereck" wrote in message

>I am trying to loop through a DAO recordset and replace characters as
> needed. However, when the replacement consists of just appending a
> character, the first record in the recordset seems to move to the end
> of the recordset after the first iteration through the loop and has the
> append done a second time. Here is the code (slightly modified to get
> rid of the extra crud). This is legacy code so bare with me.
>
> dim TempData as string, FindString as string, ReplaceString as string
> FindString = "B.701"
> ReplaceString = "B.7011"
> Set DAORecord = m_SRDatabase.OpenRecordset( "SELECT MyField FROM
> MyTable", dbOpenDynaset)
> Do While Not DAORecord.EOF
> TempData = DAORecord.Fields(0) & ""
> TempString = Replace(TempData, FindString , ReplaceString )
> If TempData <> TempString Then
> DAORecord.Edit
> DAORecord.Fields(0).Value = TempString
> DAORecord.Update
> End If
> DAORecord.MoveNext
> Loop
>
> The preceding code will result in the first item in the recordset
> having the string "B.70111" while all the others only have "B.7011".
> I've traced through the code and this record is changed on the first
> and then the last iterations of the loop.
>
> I've gotten around this by replacing the Do While with this For/Next:
>
> DAORecord.MoveLast
> DAORecord.MoveFirst
> For Count = 1 To DAORecord.RecordCount
> .....same code inside
> Next
>
> So my fix does work, but I still want to know WHY it was happening in
> the first place. Is this just a "feature" of DAO or was there some
> error in the logic?
>
> Thanks for your help.
>