Converting Plain text to RTF problems (1 Viewer)

[SmallTime]

Using 2010 (accdb format) on windows 7

I'm trying to convert a memo field's format from Plain Text to Rich text. The problem is that every time I try to select the Rich Text format option the field immediately changes back to Plain Text.

Just as a test a created a new memo field in the same table and set this to Rich Text (worked fine) then copied the records for the Plain text memo field to the new memo field. Unfortunately this removed all line breaks so instead of my text looking like

Mr. Paul Rogers
11 Test House
Test City
Test Area
Test Post Code

Dear Mr. Simpson,

Thank you for your letter dated

it lokks like

Mr. Paul Rogers 11 Test House Test City Test Area Test Post Code Dear Mr. Simpson, Thank you for your letter dated

This is really breaking head and I've come to standstill over the last 3 days:banghead: so ANY ideas would be much appreciated.

SmallTime

 

[StarGrabber]

You can do it using some lines of code:

Private Sub CopyPlainTextToRichTextField()
On Error GoTo Err_Copy

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset( _
        "SELECT yourTableName.yourPlainTextField, yourTableName.yourRichTextField " & _
        "FROM yourTableName;", dbOpenDynaset)

    rs.MoveFirst
    
    Do
        If Not IsNull(rs(0).Value) Then
            rs.Edit
            rs(1).Value = Application.HtmlEncode(rs(0).Value)
            rs.Update
        End If
        
        rs.MoveNext
    Loop While Not rs.EOF
    
    rs.Close

Exit_Copy:
    Set rs = Nothing
    Exit Sub
    
Err_Copy:
    MsgBox Err.Description
    Resume Exit_Copy
    
End Sub

 

[SmallTime]

Thanks so much for your kind help stargababer.

I'm going to put that very useful snippet into my library. very nice piece of code.

I think there must have been some corruption creeping in my db (2010 has a propensity to corrupt even if I sneeze quietly). Since I didn't see many other posts on the same subject I suspected that there must be something offbeat with my db. After much messing about I finally imported everything into a couple of new dbs (Fe & BE) and now RTF seems to be behaving.

I was previously warned by a respected poster that changing to formatted text could cause problems for me later if I need to upsize to SQL server, so am stepping into RTF a little cautiously.


SmallTime

 

[StarGrabber]

You're welcome. I'm glad to see I could help.

I was able to reproduce the copy & paste behaviour described by you. But it's only that way if you mark entire columns. If you highlight the text in one single record you can do the copy & paste without loosing the line breaks. Of course, this is not an option in a table with more than 10 records.