help with text handling

[comeng]

Hi

Have the following code that works on Access 2000

Dim myForenameBefore As String
Dim myForenameAfter As String
With CurrentDb.OpenRecordset("Names", dbOpenDynaset)
Do Until .EOF
myForenameBefore = .Fields("Forename")
myForenameAfter = Replace(.Fields("Forename"), "Elizh", "Elizabeth")
MsgBox myForenameBefore & " " & myForenameAfter
.MoveNext
Loop
.Close
End With

So that I can replace any abbreviated names in a table, however I cannot figure out how to put the value of myForenameAfter back into the table replacing the existing data.
Must be particularly thick today

 

[RuralGuy]

Have you tried...
.Fields("Forename") = myForenameAfter
...before your .MoveNext line?

 

[comeng]

yes but get following error message

Runtime error 3020
Update or CanceUpdate without AddNew or edit

which I do not undestand because I use dbOpenDynaset

 

[RuralGuy]

My bad ... try:

.Edit
.Fields("Forename") = myForenameAfter
.Update

 

[comeng]

Thanks very much that works, also found another way round as shown. As always with Access more ways than one of skinning the cat

Public Sub AbbrevF(AbbrevForename As String, FullForename As String)
Dim rstTable As DAO.Recordset
Dim myForenameBefore As String
Dim myForenameAfter As String

With CurrentDb.OpenRecordset("Names", dbOpenDynaset)
Do Until .EOF
myForenameBefore = .Fields("Forename")
myForenameAfter = Replace(.Fields("Forename"), AbbrevForename, FullForename)
.Edit
.Fields("Forename") = myForenameAfter
.Update
.MoveNext
Loop
.Close
End With
******
or
******
Set rstTable = CurrentDb.OpenRecordset("Names")

rstTable.MoveFirst
Do Until rstTable.EOF
myForenameBefore = rstTable!Forename
myForenameAfter = Replace(rstTable!Forename, AbbrevForename, FullForename)
rstTable.Edit
rstTable!Forename = myForenameAfter
rstTable.Update

rstTable.MoveNext
Loop
End Sub

 

[RuralGuy]

You're welcome. Thanks for posting back with your success.