update recordset issue

[David]

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 ";"
substituted 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

 

[Marshall Barton]

You forgot the rst.Edit before you change the field.

I don't know what you're trying to do here but you're
substituting in a newline character. If you plan on
displaying that field in Access, you should use Chr(13) &
Chr(10) in that order (or the predefined VBA constant
vbCrLf).

 

[John Vinson]

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

A New Line in VBA (and much of Windows) is not a linefeed Chr(10) but
rather a PAIR of characters - Carriage Return, Chr(13), followed by
linefeed Chr(10). For convenience, this pair of characters is given a
constant, vbCrLf. Either

Replace(txt, ";", ";" & Chr(13) & Chr(10), ...

or

Replace(txt, ";", ";" & vbCrLf...

 

[David]

Great. 10,000 thanks!

You forgot the rst.Edit before you change the field.

I don't know what you're trying to do here but you're
substituting in a newline character. If you plan on
displaying that field in Access, you should use Chr(13) &
Chr(10) in that order (or the predefined VBA constant
vbCrLf).
--
Marsh
MVP [MS Access]

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 ";"
substituted 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