[AccessD] Highlighting Select Words in a Large Text Field

I have used HTML pages to manage data displays that the current Access
version was unable to handle easily or features like Google chart tools or
maps. These features seem to integrate quite nicely within Access especially
if you are using a MS SQL BE. MS Access is an excellent controller.

OT: It may be too late to educate you wife, in geek technology but it is
never too early to start educating your children. ;-)

Good work Brad.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Sunday, September 30, 2012 11:40 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Highlighting Select Words in a Large Text Field

All,

Ever since I became a member of the AccessD forum a couple years ago, I have
asked many questions and received a ton of great ideas and solutions.  I am
grateful for this help.  However, as a relative newcomer, I have not been
able to contribute very much that others might find useful.  I share the
following example with the idea that others may benefit from some work that
I did over the weekend.

The small firm where I work has a purchased manufacturing system.  I have
built an inquiry system using Access 2007 with ODBC access to the
manufacturing system database. This works very nicely.  Recently, our users
requested a new inquiry that would allow them to search for records that
contain select words that are found in a large text field.

This was easy to accomplish with a little VBA code that changes the SQL
based on the word or words that the users specify.  This works fine, except
that the resulting data on an Access report is simply displayed as one big
chuck (over 1,000 bytes).  I needed to find a way to highlight select words.


In the purchased manufacturing system database, the field in question is
defined as a memo field.  I did some research to see if it was possible to
highlight select words when displaying a memo field, but could not find a
quick solution.

Here is what I came up with over the weekend.

I use VBA code to change the SQL based on the string that the users request.
The select records are available via a Record Set. 

I then wrote a little VBA code that adds HTML tags as needed to create an
HTML file and to highlight the requested words in bold red.

When all of the records in the Record Set are processed, the VBA code
initiates Internet Explorer to display the generated HTML file.

This approach did not take a lot of time to build and it seems to work
nicely.

I showed what I had built to my wife and she did not seem to be impressed.
I showed it to one of our three children and he was not impressed either.  I
showed it to the family dog and he also was not impressed.  I am starting to
feel like the Rodney Dangerfield of the database realm.  I plan to show the
final results to the users tomorrow (Monday).

Anyway, I thought that I would share this concept and perhaps someone else
would benefit.  
 
Brad