Power Tip: Take advantage of Rich Text textboxes in Access

Today’s Power Tip comes from Philippe Riondel of text2texte. Philippe is an English to French certified translator based in Québec.

I am currently developing a terminology managing database for my main occupation, which is translation. The interface is mainly made of 2 parts, one for the search and one for the display of the found information. That second pane can typically include one or several of entries, plus one or several topics, links to Web pages, etc., like in the image below :

The question is: how many controls do you see in the right-hand pane above? The answer is: one.

My challenge was the following: I needed to show all the information relating to a term/concept with different formatting, according to the kind of information (English entry in dark red, French entry in dark blue, domain, source, definition, comment and context incorporating some bold and in different colors and sizes, etc.), and without setting in advance the number of entries or topics that would populate each record nor their respective lengths, in order to avoid to show blank controls or areas in between. In other words, I would like a somewhat linear presentation, like a Web page, as in the picture above. By the way, I also wished to be able to highlight the terms in the result that would match the words searched in a full text search, like in the picture below, and if possible, to have working hyperlinks.

In HTML, it would be a trivial problem, but how to manage it in Access? Obviously, most of you have an idea of the answer, which resides in the rich text formatted textbox, that was introduced in Access 2003, if I am not wrong. If the feature is not that new, I do not remember to have found much literature about taking full advantage of it on the Internet or elsewhere. My intent here is not to speak of the century’s discovery or of a revolution, but I found interesting to share my experience here, to give to some of you some ideas about using Rich text textboxes and to show how powerful they are. I stayed long without using them, but, when I started, it was a revolution for me. As I wrote it above, even the Web hyperlinks work!

Typically, I feed that single control with my mass of information just as I would code an HTML page, since the control is based on like-HTML tags. After, it’s just about using some queries to extract my data and loops to process them, in order to format them in the intended way. To do that, I use a single WriteContent() function, that is hosted in a module and returns a string incorporating the formatted content aimed to become the RTF textbox’s value. That content looks like this (looks really like HTML, doesn’t it?):

That code, when setting the textbox.TextFormat property to “Rich Text”, gives the result below. As one can see, the same function can be used to populate the same formatted text box in a report only made of the same control, called from the icon at the top right of the screen:

A document containing the code for the WriteContent() function, as well as an image of the database relationships, can be found here.

Have fun!

Philippe

(updated 4/4/2011 to fix images)