UtterAccess.com
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    
 
   
kswaringen
post Mar 2 2018, 03:23 PM
Post#1



Posts: 75
Joined: 11-January 12



Hi,

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:

CODE
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
    fd.Properties.Refresh
    
    Set fd = td.Fields("Objectives")
    td.Fields("Objectives").Properties.Append fd.CreateProperty("TextFormat", dbByte, 1)
    fd.Properties.Refresh
    
    db.TableDefs.Refresh

    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?

Thanks,
Kristen



Go to the top of the page
 
zaxbat
post Mar 3 2018, 06:14 AM
Post#2



Posts: 952
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.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
kswaringen
post Mar 5 2018, 09:30 AM
Post#3



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).

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

and

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

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

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 02:19 AM