Editing and Formatting Options for Rich Text Fields in Microsoft Access

You can store rich, formatted text in an Access database by using a Long Text (a.k.a. Memo) field and setting the field’s TextFormat property to RichText. This also works when the data source is a linked SQL Server table/field.

You can make the text bold or underlined, apply different fonts to individual words or characters, and change text colors. You can apply formatting to all or part of the contents of the field when the field is displayed in a datasheet and by editing the field through a bound control in a form or report.

Rich text is text that is formatted with common formatting options, such as bold and italics, that are unavailable with plain text. You format your data by using common formatting tools, such as the Ribbon and the Mini Toolbar in Access. Behind the scenes, Access applies HTML formatting code to the data.

The main challenge is that the built-in formatting tools in Access are limited (although they are often sufficient). Certain formatting options such as indent handling, bullet options and more are not available so if you have formatting requirements beyond what Access includes, there are third party add-in options such as Total Access Memo by FMS or CKEditor. These typically have more than enough formatting power for many scenarios.

Recently however, a client needed more formatting options than even these powerful add-ins contained. They had a ton of data stored in Microsoft Word documents with extensive formatting in use so settling for less than that was not an option. The feature request was to add a rich text field to each of their customer records so they could do some merged reporting and have easy/direct links between customer records and the related Word documents that contained extended customer data.

After evaluating the various third party options, the decision was to leave the MS Word documents in place so they could continue to enjoy Word’s vast array of formatting options and to rename each document with a prepended customer_ID from the related customer record in the database so they have a link/relationship. Now from each customer record screen in Access, they are one click away from bringing up the related MS Word document to view/edit and using MS Word bookmarks and uniquely ID’d textboxes, some content is accessible to merge into certain reports combining both customer record data and MS Word content for customers. All of the Word interaction of course uses Word automation in MS Access VBA which is very easy to implement.

Nice to know there is ultimately always a way to meet a client’s objectives and the tightly integrated MS Office/VBA universe makes it possible.