Replace text using recordset

[Billy B]

I am trying to loop through a table (tblPlants) using a recordset and update
part of the text fields text value. The variables strOld and strNew
represent the root paths that I am trying to find and replace in the PicPath
field of the table. I am getting an error message at the replace line of the
loop. Thank you.

Within the procedure...

With rsFnd
Do While Not rstfnd.EOF
If rsfnd!PicPath <> "" Then
replace(rsFnd!PicPath, strOld, strNew, 1, 3, vbTextCompare)
As String
rsFnd.MoveNext
Loop
End With

rsFnd.Close

[dhauck]

is your recordset readonly?
the "As String" syntax seems wrong, out of place
what is the error msg?

[Marshall Barton]

Billy B wrote:

You need to set the field to the modified value and save it
back to the table.

You are using two recordset variables, Since you only
mentioned one recordset, I assume that one of the recordset
variable names is a typo. With that caveat, the code would
look more like:

With rsFnd
Do While Not .EOF
If !PicPath <> "" Then
!PicPath = Replace(!PicPath, strOld, strNew, _
1, 3, vbTextCompare)
End If
rsFnd.MoveNext
Loop
.Close : Set rsFnd = Nothing
End With

Note that using a record set to do this job is the slow,
inefficient way. Better to use an UPDATE query:

UPDATE thetable
SET
PicPath=Replace(PicPath,strOld,strNew,1,3,vbTextCompare)
WHERE PicPath Is Not Null

--
Marsh
MVP [MS Access]

[Billy B]

Here is my recordset statement:
rsFnd.Open strSQLFnd, conn, adOpenForwardOnly, adLockPessimistic

The error for the code I posted is:
Statement invalid outside type block

If I remove the 'As String' I get the error message:
Expected =