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?

 

[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 =

 

[Marshall Barton]

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

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