Extending Access Reports with Word and HTML

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Andrew Wrigley

Andrew Wrigley shows how to leverage Access reports to produce intricately formatted reports that "flow" as if typed into a word processor. The technique delivers the report in two eminently portable file formats–as a Web-ready HTML file and as a RichText Format document–so your happy clients get double value for their money. The only additional skill that you need is a basic understanding of HTML and Cascading Style Sheets (CSS).

Like any reporting software, Access reports display their data in controls such as textboxes, labels, and graphs. The unit of layout and formatting is the control on the design surface, be it a textbox or whatever, and you can only apply one style to the content of each control. For instance, you can't have part of the text in a textbox displayed in bold face and the rest in plain face: It's all either one thing or another. Also, you can't make the contents of one textbox seamlessly flow behind another like text in a word processor: Your controls are pretty much fixed in design space, and the result has a highly structured look.

	This is great for most purposes, such as invoices or displaying sales information, but what if you need a complex report that looks and flows like a word processor document? This is where even Access reports meet their horizon of competence. Take a look at Figure 1 and you'll see what I mean.

	Figure 1 shows a sample from a 300-page directory that the Diocese of York produces every year with details of all its churches and officials. The Church of England has an intricate hierarchy of appointments and formality that needs to be reflected in the formatting of this directory. The RTF document that was used by the print bureau took two secretaries two months of loving and painstaking care to produce. And yes, you guessed it, all of the data that went into the RTF document was already stored in an Access database.

	I was an Access novice when I was asked to automate the process. The job looked straightforward: The formatting was complex but logically consistent, so all I needed to use was an Access report that grouped the data in the right order. Right? Wrong! My report needed to mimic the exact formatting and layout shown in Figure 1, but with textboxes and labels that simply wasn't possible.

	When this dawned on me, I started looking at pumping the data into Microsoft Word and using Word Range objects to control formatting. After flailing around calculating the length of pieces of strings, all I developed was a headache. I was driving home in silent panic when it hit me: However you format a document, you inevitably need to mark where the style formatting begins and then mark where it ends. In other words, you need to mark your data up. You then need to add your styling instructions to that markup. And that, ladies and gentlemen, is an operational description of HTML and Cascading Style Sheets.

	It gets better: HTML documents, when displayed, "flow" just like a word processor document, which is just what I wanted. Also, more recent versions of Word will happily open an HTML document and display the contents almost as a browser would (unlike Notepad, where if you open an HTML document you'll see the source code).

	So my problem was reduced to getting an HTML document out of Access for the layout and generating a Cascading Style Sheet to control the formatting. As I'll show you, Access reports are just the tool for the job.

Overview

Instead of using an Access report to directly produce the output I want, I use a report to produce the HTML source code of a Web page with a built-in style sheet. This way, I get the best of both worlds: Formatting and layout can be as intricate as HTML and CSS can handle, plus I have the full power of Access reports at my disposal (grouping, calculation engine, sub-reports, and so on).

	As an HTML document is just a text file with an .html extension, I can save the report to a file using the OutputTo method of the DoCmd object (discussed later). I then use Word Automation to open this HTML file and save it with an .rtf extension. This way, my client gets two reports for the price of one–the report as an RTF file and a Web-ready HTML document.

	Into the bargain, if your client requires a Web solution that's searchable, you could store snippets of HTML code in a (denormalized) table, along with the data that you want to search on (as I'll show later on). The Access report is quite capable of stitching these snippets together, with only basic HTML skills required from you.

The report

The first step is to "trick" an Access report into delivering the source code for a well-formed HTML document with an embedded Cascading Style Sheet. The following code shows a crude example of what I want:

<html>
  <head>
    <!-- Embedded Style Sheet-->
     <style type='text/css'>
         .dummy {color: #ff0000; 
                 font-size: 9pt; 
                 font-family: Arial}
     </style>
   </head>
   <body>
     <!-- HTML Snippet starts here -->
     <p class = 'dummy'>
     Hello, the dummy style applies to me!
     </p>
     <!-- HTML Snippet ends here -->
   </body>
</html>

	To get this, I design an Access report that looks like this:

• &#9;In the Report Header section, I place a label that I'll call lblHeader. In the label caption I type the opening <HTML> and <HEAD> tags, followed by the style sheet that I want. The style sheet is followed by the closing </HEAD> tag and the opening <BODY> tag (see Figure 2).
• &#9;In the Report Footer, I place any HTML tags that I need to close my properly formed HTML document, such as the ending </BODY> and </HTML> tags.

&#9;So far, I've hardly needed to use an Access report, but now I'll add data to the report. For the York Diocese directory, I needed to group Churches in Benefices, Benefices in Deaneries, and Deaneries in Archdeaconries.

&#9;In Figure 2 you can see how I leveraged Access reports' grouping capabilities to achieve this grouping. Three Group sections wrap the Details section, with data inserted at each level using a textbox. So what data do I put into each of these textboxes? The answer is plain snippets of HTML. This HTML is only as complex as the required output. You do need to be careful that your snippets mesh into a well-formed HTML document. For example, you need to make sure that you match any opening <SPAN> tags with a closing </SPAN> tag and that all tags are properly nested in a hierarchy of parent tags. You can generate the HTML snippets in code (as I do in the next section), or hard-code your HTML directly into the ControlSource property of the textbox in question.

&#9;A quick look at some of my HTML snippets will clarify how this works.

HTML snippets

The HTML snippets are nothing more than data values appropriately concatenated with the required tags and class attributes. For example:

"<span class = 'Arch'>Archdeaconry of " & _
  [Archdeaconry].[Archname] & "</span>"

&#9;This snippet appears in Figure 2 in the Archname Header. The snippet inserts the name of each Archdeaconry ([Archdeaconry.Archname]) into a textbox, enclosed within HTML <SPAN> tags. The <SPAN> tags are used to apply the required formatting to the Archdeaconry name text.

&#9;The formatting is applied using the class attribute of the span tag to refer to the .Arch style class that's defined in the caption of lblHeader:

.Arch {color: #ff0000; 
   font-size: 11pt; 
   font-weight: bold;
   font-family: 'Times New Roman'}

&#9;The following code shows the HTML snippet that's stored in the ChurchDetails field (the DeaneryDetails and BeneficeDetails fields are very similar). All names and details have been changed:

<p>
   <span class = "Parish">All Saints, 
      Thorne Tree ER 64
   </span><br>
   <span class = "Church">Sunday Service Times: 
      09:00 am; 11:00 am
   </span><br>
   <span class = "Lay">Wardens</span>: 
   Mr A B Cxxxx, 17 South Road, Billington, 
   ZO17 8XX, 
   Tel: 05544 123456, Fax: N/A<br>
   Mr E F Gxxxxx, Salton 
   Tel: 05544 758255, Fax: N/A<br>
   <span class = "Lay">PCC Secretary</span>: 
   Mrs K L Mxxxxx, Rowgate, Thorne Tree, 
   ZO17  8XY, 
   Tel: 05544 09876543, Fax: N/A<br>
   <span class = "Lay">PCC Treasurers</span>:
   Mrs H I Jxxxxx, The Lowlands, Thorne Tree, 
   Salton ZO17 8ZA, 
   Tel: 05544 34567890, Fax: N/A<br>
</p>

&#9;Again, the contents of the <SPAN> tags have had a style applied to them, using their Class attribute.

&#9;Styles can also be set for all <p> (paragraph) tags like this:

p {color: #ff0000; 
   font-size: 9pt; 
   font-family: 'Times New Roman'}

&#9;Defining a style like this causes the style to act as a default. This style will be applied to all content between <p> </p> tags except where the style is overridden by a nested tag (such as a <SPAN> tag). Here's a tip: If your HTML/CSS skills are a bit rusty, you can use any Web design software such as FrontPage or Adobe GoLive to visually build the basic HTML structure that you need to re-create in your code.

Generating the HTML snippets

My problem has now been reduced to generating the HTML snippets from the raw data and feeding the results into an Access report for assembly into a well-formed HTML doc. Everything else can be abstracted into a reusable framework. Customizing this framework for different reports is no more complex than stitching together a few snippets of HTML source code.

&#9;You can look at the samples in the download for this article for inspiration on how to write these snippets, and wire it all up into a user-friendly app. Because every report is different, I'll just give a few guidelines.

&#9;First, be cautious. The coding that you need to generate the snippets can get quite intensive, depending on the complexity of the formatting that you want to implement. Therefore, this technique is only commercially viable if the report is either too extensive to format manually or it's generated very frequently. In the case of the York Diocese directory, both factors applied: 300 pages of intricate and complex formatting that's regenerated on a regular basis. However, take a look at just a snippet of code required to produce just one line of the ChurchDetails HTML snippet shown earlier. I begin with some SQL that retrieves the church name with all its symbols, followed by the Sunday Service times. I then wrap the necessary HTML around the various fields:

r.Open "SELECT * FROM YBqryChurchName " & _
"WHERE Chcode = '" & nzChcode & "'", _
CurrentProject.Connection, adOpenStatic, _
       adLockReadOnly

CS = "<p><span class = ""Parish"">" & _
 IIf(Len(r.Fields("MotherDaughter")) > 0, _
 IIf(r.Fields("MotherDaughter") = "m", "§ ", "* ") & _
 " ", "") & r.Fields("AlternateName") & " " & _
 Nz(ChurchSymbols(r.Fields("ListedBuilding"), _
 r.Fields("LinkParishNumber"), _
 r.Fields("Accessibility"), _
 r.Fields("ConservationArea"), r.Fields("Elecroll"), _
 r.Fields("Loop System")), "") & "</span><br>" & _
 "<span class = ""Church"">Sunday Service Times: " & _
 "</span>" & r.Fields("Servtime") & "<br>"

r.Close

&#9;Second, you should use "structurally neutral" tags as much as possible. You should, for instance, use <SPAN> tags and <DIV> tags rather than the predefined HTML header tags (<h1>, <h2>, ..., <h6>). These tags are harder to control, as they can affect the overall layout of the document, especially when used "inline"–that is, on the same line as text that requires different formatting.

&#9;Third, in HTML design, giving "meaning" to your tags is a goal akin to normalization in database design. This "meaning" is applied to the <SPAN> tags by using class attributes as follows:

<span class = 'Clergy'>

&#9;When the page is displayed, the attributes reference the corresponding classes defined in the embedded style sheet. In the preceding example, this is the .Clergy class. Therefore, to change the formatting of all text marked up as "Clergy" in the HTML document I'll output, all I have to do is change the definition of the single .Clergy class. And that's good design in any technology.

&#9;Fourth, rather than hard-coding the style sheet into a label as I've shown here, you could assign it dynamically using a sub-report in the Report Header, allowing users to choose the styling that they want.

&#9;A final warning: Never ever use the Page Header and Page Footer sections, as the HTML tags will be inserted according to report page breaks that will bear no logical or structural relation to the HTML document you're constructing. Another tip: This technique allows you to use sub-reports (and sub-sub-reports) to insert the snippets into the main report. You just have to ensure at all times that the snippets mesh into a well-formed HTML document.

Storing the snippets

As this is a reporting solution, inputting data isn't really part of the problem. Therefore, you can safely denormalize the tables used to store the HTML snippets (for a discussion of denormalization for reporting solutions, see Peter Vogel's article "Iron Rules" in the June 2002 issue of Smart Access). Although contravening the venerable principles of table design, the reality is that denormalization generally only creates problems for you when you insert, update, or delete data in those tables. If you never input data into denormalized tables, the denormalizing won't bite you. The one exception is reporting data in a table that violates first normal (repeating the same data time multiple times in a single record). However, if you restrict the use of a table strictly enough (a purpose-built table), even violating first normal form may not be a problem.

&#9;Generating the HTML snippets for the York Diocese directory on the fly was achingly slow, so I chose to build the snippets in advance and store them in denormalized tables along with the relevant keys I required for stitching the snippets together in my Access reports. Building the report this way gives me another benefit: I can search the tables using the keys to find the snippets that I wanted. This is useful for browser-based solutions, either online or in an Access Form using the Browser control (I show off this feature in the sample database in this month's download).

&#9;If you use this technique, you may not need to denormalize. First, many performance issues can be solved by optimizing the code used to produce the snippets. Second, the York Diocese directory is about as complex a problem as you'd ever encounter due to its extent and the intricacy of the Church of England's hierarchical formalities.

Building the files

Once I've generated my HTML snippets and stitched and grouped them together in my Access report, it's time to generate the two files that are the final output of this technique: the HTML file and the RTF file.

&#9;The HTML file is easy. I just use the DoCmd.OutputTo method with the report:

DoCmd.OutputTo acOutputReport, nameReport, _
   acFormatTXT, pathFile

&#9;In this code, nameReport is the name of my report and pathFile is the full path of the file I'll output. If the file doesn't exist, Access will create it. If I've designed the report and HTML snippets properly, I'll now have a well-formed HTML document that could be immediately uploaded and displayed on a Web site.

&#9;To generate the RTF file, I'll use late bound Word Automation from Access, as follows (please add your own error checking!). First, I store the paths and names of the RTF file and the HTML files in variables:

Dim fileHTML as String
Dim fileRTF as String
Dim pathApp as String

pathApp = CurrentProject.Path
fileHTML = pathApp & "\sample.html"
fileRTF = pathApp & "\sample.rtf"

&#9;Next, I create a Word Application object using late binding, and use the Open method of its Documents collection to open the HTML file (fileHTML) generated by my report:

Dim theWord as Object

Set theWord = CreateObject("Word.Application")
theWord.Documents.Open FileName:=fileHTML

&#9;I then I use the SaveAs method of the Documents collection to save the open document (that is, fileHTML) in RTF format (the FileFormat:=wdFormatRTF parameter set in the following code):

theWord.Documents(fileHtML).SaveAs _
  FileName:=fileRTF, FileFormat:=wdFormatRTF

&#9;I can now close the open document, saved as fileRTF, and quit Word:

theWord.Documents(fileRTF).Close
theWord.Quit
Set theWord = Nothing

&#9;That's all there is to it! You could also pass fileHTML and fileRTF into the sub as parameters, rather than hard-coding them as shown here. You can now open the RTF file and take a look. You'll see that Microsoft Word is clever enough that it's included the styles that you set in the embedded HTML style sheets as part of the RTF document, without needing a matching template.

&#9;This powerful technique leverages the great capabilities of Access reports to deliver the kind of layout and formatting that Access alone can't, especially where the content must "flow" as in a word processor document. The coding effort involved is offset by the fact that you get the report in two portable formats (RTF and HTML). You can also store the HTML snippets in tables that can then be used to deliver a fast and searchable browser-based solution.

&#9;This technique is best used for producing very extensive documents with intricate formatting or for reports that are produced on a frequent basis.

&#9;The sample download for this article demonstrates how to use the technique to mail merge an illustrative but rather silly report. The sample also includes some reports and modules prefixed with "NotWorking." These aren't wired up to live data, but you can look at them in Design view to get a feel for how the technique can be applied and the coding overhead that's involved.

Download 501WRIGLEY.ZIP

To find out more about Smart Access and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the January 2005 issue of Smart Access. Copyright 2005, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.