Recordset update issue

[Dave]

Hello,

First, thanks for the help everyone has provided in the past; I've learned a
lot just by reading other's messages, and you helped me through one other
issue.

The bit of code is not updating the table, and I'm going to pull out what
little remains of my hair if ...

Anyways, here's the code. What I want it to do is open a recordset, read in
a value for the first record, create a new string that has the text ";"
substitued with ";" and the hard return. I can get a message box with what
I want, so the [newtext] field is working, but the update to the table with
the [newtext] string is failing. I've written to tables before with a
module, so I'm at a loss.

dim Td As Fields

Dim Tbl As TableDef

Dim rst As Recordset

Dim txt As String

Dim tblname As String

Dim i As Long

Dim newtext As String

Function testing()

Set db = CurrentDb()

For Each Tbl In db.TableDefs

If Tbl.Attributes = 0 Then


tblname = Tbl.Name


Set rst = db.OpenRecordset(tblname, dbOpenDynaset)


For i = 1 To 1000

On Error Resume Next

txt = rst![valid response]

newtext = Replace(txt, ";", ";" & Chr(10), , , 1)


rst![valid response] = newtext

rst.Update

MsgBox newtext

MsgBox rst![valid response]


txt = ""

newtext = ""

rst.MoveNext


Next

End If

Next

End Function

 

[Graham R Seach]

Dave,

If you're using Access 2000 or prior, add the following function in a
standard module:
Public Function Repl(sExpression As String, sFind As String, sReplace As
String) As String
Repl = Replace(sExpression, sFind, sReplace)
End Function

Then make the change to all records in the table at the same time, like so:
Dim sSQL As String

sSQL = "UPDATE tblMyTable SET [valid response] = Repl([valid response],
';', ';' & Chr(10)"
CurrentDb.Execute sSQL, dbFailOnError

If you're using Access 2002 or later, don't create the Repl() function, just
use this:
Dim sSQL As String

sSQL = "UPDATE tblMyTable SET [valid response] = Replace([valid
response], ';', ';' & Chr(10)"
CurrentDb.Execute sSQL, dbFailOnError

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Graham R Seach]

Sorry, I forgot, in Access 97 and prior, this is the Repl() function you
need to add:
Public Function Repl(sExpression As String, _
sFind As String, sReplace As String) As String
Dim iPos As Integer

If Len(sExpression) > 0 And Len(sFind) > 0 Then
'Check to see if sFind exists
iPos = InStr(1, sExpression, sFind)
Do While iPos > 0
'It exists - replace it
Repl = Left(sExpression, iPos-1) & sReplace & _
Mid(sExpression, iPos + Len(sFind))

'As long as we're not at the end of sExpression,
'check to see if there is room for more instances
If iPos < Len(sExpression) Then
'Yep, there's room - check for sFind again
iPos = InStr(iPos + 1, sExpression, sFind)
Else
'Nope, there's no more room, so there can't be
'any more instances
iPos = 0
End If
Loop
Else
'There are no instances - set the return value = sExpression
Repl = sExpression
End If
End Function
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Dave,

If you're using Access 2000 or prior, add the following function in a
standard module:
Public Function Repl(sExpression As String, sFind As String, sReplace As
String) As String
Repl = Replace(sExpression, sFind, sReplace)
End Function

Then make the change to all records in the table at the same time, like so:
Dim sSQL As String

sSQL = "UPDATE tblMyTable SET [valid response] = Repl([valid response],
';', ';' & Chr(10)"
CurrentDb.Execute sSQL, dbFailOnError

If you're using Access 2002 or later, don't create the Repl() function, just
use this:
Dim sSQL As String

sSQL = "UPDATE tblMyTable SET [valid response] = Replace([valid
response], ';', ';' & Chr(10)"
CurrentDb.Execute sSQL, dbFailOnError

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hello,

First, thanks for the help everyone has provided in the past; I've

learned

a
lot just by reading other's messages, and you helped me through one other
issue.

The bit of code is not updating the table, and I'm going to pull out what
little remains of my hair if ...

Anyways, here's the code. What I want it to do is open a recordset,

read

in
a value for the first record, create a new string that has the text ";"
substitued with ";" and the hard return. I can get a message box with what
I want, so the [newtext] field is working, but the update to the table with
the [newtext] string is failing. I've written to tables before with a
module, so I'm at a loss.

dim Td As Fields

Dim Tbl As TableDef

Dim rst As Recordset

Dim txt As String

Dim tblname As String

Dim i As Long

Dim newtext As String

Function testing()

Set db = CurrentDb()

For Each Tbl In db.TableDefs

If Tbl.Attributes = 0 Then


tblname = Tbl.Name


Set rst = db.OpenRecordset(tblname, dbOpenDynaset)


For i = 1 To 1000

On Error Resume Next

txt = rst![valid response]

newtext = Replace(txt, ";", ";" & Chr(10), , , 1)


rst![valid response] = newtext

rst.Update

MsgBox newtext

MsgBox rst![valid response]


txt = ""

newtext = ""

rst.MoveNext


Next

End If

Next

End Function