X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Updating Text Format Field Property In Table, Access 2016    
post Mar 2 2018, 03:23 PM

Posts: 75
Joined: 11-January 12


I need to import an Excel file into Access. My last two columns ('Description' and 'Objectives') contain rich text (line breaks, bullet points, etc.). When I import the data into a new table, the formatting for these two Long Text fields is not retained. However, I can go into the table design view and change the value of the Text Format property from Plain Text to Rich Text. This restores the correct formatting.

As an experiment, I created a new table and set the Text Format property to Rich Text for the Description and Objectives fields. I then performed an input that appended my data to this existing table. Again, the formatting was not retained.

As a third attempt, I imported the data to a new temporary table. I adjusted the Text Format property for the two fields and then used a query to append data from the temporary table to my existing table. This worked.

I was hoping to replicate my third attempt in VBA. For updating the Text Format property, I tried the following:

Sub SetRichText1()

Dim db  As DAO.Database
Dim td  As DAO.TableDef
Dim fd  As DAO.Field
Dim prp As DAO.Property

    Set db = CurrentDb
    Set td = db.TableDefs("TEMPTable")
    Set fd = td.Fields("Description")
    'tdf.Fields(fld.Name).Properties.Append fld.CreateProperty("TextFormat", dbByte, acTextFormatHTMLRichText)
    td.Fields("Description").Properties.Append fd.CreateProperty("TextFormat", dbByte, 1)
    'fd.Properties("TextFormat") = 0
    Set fd = td.Fields("Objectives")
    td.Fields("Objectives").Properties.Append fd.CreateProperty("TextFormat", dbByte, 1)

    Set prp = Nothing
    Set fd = Nothing
    Set td = Nothing
    Set db = Nothing

End Sub

This does, in fact, change the value of the property. However, it doesn't format the text correctly! The only way I can format the data correctly is to manually update the property. Am I doing something wrong?


Go to the top of the page
post Mar 3 2018, 06:14 AM

Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)

The data as it resides in the table is of whatever type it is declared. As I understand it, the format property will not change that. The format is for viewing/display purposes. I'm running 2010 so i have no rich text. But I believe your rich text is in the table in all cases but you do not see it unless you choose the correct format. And remember, there is a format property both on the table and on the textbox...perhaps your are not setting the proper format on the textbox on your form/subform.
Go to the top of the page
post Mar 5 2018, 09:30 AM

Posts: 75
Joined: 11-January 12

I'm actually not using any textboxes or forms/subforms--I'm using the table as a data source for a modified version of Allen Browne's Export data to web pages utility.

I did notice that when manually selecting the Rich Text option in the table design view, Access brings up a warning: "This field will be converted to Rich Text, and all the data it contains will be HTML encoded. If your data already contains valid HTML Rich Text, you can remove any extra HTML encoding by using the PlainText function in an update query. Do you want to convert the column to Rich Text?" Updating the property through VBA does not cause this warning to occur.

This message did give me a thought, and I discovered that there is a counterpart to the PlainText function in the expression builder called HtmlEncode. I ended up using my temporary table as a data source for an append query--I replaced the temporary table's Description and Objectives fields with the following and appended the data to my existing table. This gives me the correct result (in both my existing table and in the utility's HTML output file).

Description: HtmlEncode([TEMPTable]![Description])


Objectives: HtmlEncode([TEMPTable]![Objectives])

Many thanks for taking a look at my problem and commenting!

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2018 - 02:14 PM