[AccessD] Highlighting Select Words in a Large Text Field

Brad Marks BradM at blackforestltd.com
Sun Sep 30 18:45:02 CDT 2012


Rusty,

You are right.  The definition of the text field is in a purchased package and cannot be changed.

The data itself, can only be changed via the purchased manufacturing software package.  We can NEVER update the data via Access or any other software except the purchased package.

The purchased software has some canned reports, but our users want many more reports which are quite easy to produce with Access.

The report that I am currently working on has the large text field.  This field contains detailed manufacturing specifications.  Each product produced (gold rings for example) are highly customized.

The users want the ability to key in a word (or words) and then see a report of every Sales Order record that has this word (or words) in the large text field.  This is easy to provide.

The more difficult challenge is that the users want the word (or words) that they select (key in) to be shown on the report in the color red and in a bold font.

For example, a user might key in "AMPI" for their search criteria.  They would then want to see each Sales Order that has the Text field that contains the word AMPI  *AND* they want the word AMPI to be shown in bold font and the color red.  This is to make the word AMPI stand out, as it can appear any place in the large text field and it can appear multiple times.

I have this working via the HTML method.  I looked into using RTF, but I wasn't sure how to use VBA to change plain text into Rich Text with specific words in a bold font and in the color red.

There might be another way to do this, but I was pretty excited about getting the HTML method to work.


Brad    

PS. I sometimes get a little heat on the home front for working on the weekends, but I keep telling them that I am really not working, "I am playing".  I remember a quote that I once saw from an Oracle executive. Something along these lines... "A relational database is a computer game with unlimited levels"    :-)

  



-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Rusty Hammond
Sent: Sun 9/30/2012 3:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Highlighting Select Words in a Large Text Field
 

Arthur,
Sounds like he's pulling from a database for a package they bought.
Probably can't (or at least shouldn't) change the data for risk of
causing issues in the purchased application.

Rusty

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Sunday, September 30, 2012 3:44 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Highlighting Select Words in a Large Text Field

Brad,

How do you know which words to highlight? If you know them in advance,
why not store all the HTML directly in the memo field, rather than just
the text?

Arthur

-----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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.







More information about the AccessD mailing list