Chapter 18

Creating Expert Reports


Putting data into an application that has no reports is like taking your daily newspaper from the front porch each day and locking it unread it into a closet. Similarly, putting data into an application with poorly designed or inaccurate reports is like reading selective paragraphs from newspaper stories without reading the headlines or understanding the context. Because you work hard to collect and protect data with your expert solution, you should work equally hard to provide its users with high-quality reporting output so that the data stored in the solution can feed business processes and decisions.

There are many facets to creating good reports and to using the Access reporting engine. Consequently, there is no shortage of techniques to put in reporting chapters. The problem is that most reporting techniques are already in print in other Que books, and I wanted to provide unique value here. For this chapter, I try to identify strategies that I know will provide developers of expert solutions with value but are not covered widely in print. Here is the mix of ideas that met my criteria for this chapter:

Reporting the Facts

Sometimes creating solutions can be a thankless job. One example that comes to mind occurred when we were creating reports for a client recently.

For many years, our client had been using simple, columnar reports produced off of the mainframe. The manager reviewed these reports at the end of each month. We convinced the manager that these reports would be more detailed and visually appealing if they were produced from Access instead, so he authorized us to create a system to download and report the mainframe data.

When our database and reports were completed, the system was installed and the first month's worth of data was imported into it from the mainframe and printed out. The manager immediately called us and quite gruffly pointed out that there was a significant math error in our main inventory report. I apologized and sent someone over to have a look.

What the manager had noticed was that our reports did not arrive at the same average value as the mainframe reports that were run in parallel. As it turns out, we discovered on closer examination (and proved to the manager) that our report was accurate and the calculations on the mainframe report were wrong.

Because the manager placed great faith in mainframes and his legions of Cobol programmers, he had never checked the math on the original reports, and they had been in error for years. Due to his distrust for personal computers and inexpensive tools like Access, his review of our Access reports constituted the first time he had ever checked the validity of the inventory report calculations!

The Access reporting engine is extremely powerful. With its ability to nest multiple reports in a parent report (or to nest a subreport inside a subreport inside a report), Access allows you to express relational data easily and to group and sort information for very effective data presentations.

Over the years, you will devise common approaches to your complex reporting problems. To help you master your reporting challenges, I wanted to spark your interest here in some of the newer or more interesting facets of reporting. This chapter contains information about assisting users with report selection and filtration; about dynamically configuring reports; and about utilizing HTML-based Web pages to report Access data.

Exploring New Reporting Features

Access 95 introduced several new reporting properties and features of interest. In case you didn't discover them or are moving to Access 97 directly from version 2, I'll describe those that make reports more powerful and programmable:

Reports!rptCust.Filter = "LastName = 'smith'"

Reports!rptCust.FilterOn = True

Me!subKids.Visible = Me!subKids.Report.HasData

Private Sub Report_NoData(Cancel As Integer)

Beep

MsgBox "No records match the report criteria." _

, vbOKOnly + vbCritical, Me.Name

Cancel = True

End Sub


The NoData event does not fire for unbound reports. You can check the HasData property for a value of 1 instead to tell if a report is unbound (has no recordset).

Access 97 dabbles very little with reporting-it introduces only one new report property. The IsVisible property determines whether a report control is going to be displayed for the current record on the current section or not. IsVisible is used when there is a dependency between controls and the driving control for the dependency has its HideDuplicates property set to True. You use IsVisible to determine if the control will be displayed for the current record or if it will be hidden, because it contains the value that duplicates the value in the prior record.

The IsVisible property is not the same as the Visible property. Assume that you have a Text Box control placed on a report's Detail section. The control's Visible property is set to True so that the control will attempt to print whenever its section prints. In addition, the control's HideDuplicates property has been set to True so that duplicate values repeated in the text box will not print as duplicate values on the report.

As each individual detail record is prepared for printing, the report evaluates whether or not to display the text box by checking to see if its value will be equal to its value from the previous record. If so, the report toggles the control to invisible to honor the HideDuplicates property for the control, and then temporarily sets the control's IsVisible property to False. The control's Visible property setting is left unchanged at True, and the control's IsVisible property setting is reset again when the report moves on to the next record.

To use IsVisible, your code can read the property as a record prints, and can set properties of related controls to dynamically display or hide them based on the specific circumstances. For example, a Label control related to a Text Box control whose IsVisible property is currently False can be hidden by your code at the same time:

Me!lblDivision.Visible = Me!txtTitle.IsVisible


You can only read and set the IsVisible property for a report control from the Print event procedure attached to the parent section that contains the control, as in this example:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If Me!txtTitle.IsVisible Then

' Do something

End If

End Sub

Creating Standardized Report Layouts

Report layout standards are very subjective, and will vary by company, user team, and sometimes even application. The Access report wizards actually do a fine job of satisfying many reporting needs and producing reports that have a consistent look.

When defining report layouts, the key concept to keep in mind is this: A form displayed or a report previewed within an application is "in context," meaning that the user can discern the purpose and content of the form or report from its place in the application. The hardcopy (printed) version of a form or report, on the other hand, is "out of context," because it may be circulated to people who do not know which application it was generated from or the context in which it was printed.

That fact that hardcopies are out-of-context means that special care must be taken when creating them to provide enough contextual information on the printout to clarify the document for its readers. The next section will help you improve context information on your reports.

Displaying Context on Reports

An expert report should carry with it all of the information required to identify its origin and data set. As a general rule, good reports circulate much more widely than the developers or users initially expected. Thus, it is almost certain that an application's reports will end up being read by people who are not users of the application itself. When these people have a question about the report, they need to know its source and other information about how it was produced.

I like to create a standard reporting template for each project, and use the template for each report in the application. The template should be designed to provide the same look and layout to each report in the application. This allows users to quickly identify the source of the report by its layout.


I use the term template here in an expanded context when contrasted to how Access uses the word. A report template in Access is an actual report whose format settings for the report and its controls are automatically applied to each new report. By default, the report template for your new reports is a report you create and save named Normal (although any report can be used as a template by identifying the report's name in the Report Template item in the Options dialog box).

When applying a saved report template, Access reads only the property settings from the named template; it does not copy its controls. Thus, my concept of a template goes past Access' to include adding specific controls to a report and using the report and its controls as a template. This is achieved by making a copy of the template itself as the starting point for each new report. Read on for clarification of this approach.

A report template for an application should provide a layout format that meets the needs of the specific users. Some of the items that we place on a standard report template include:


Locate either the report title, the report name, or both on each page of a report, as well as the date and time of printing. Together, these identifiers describe one unique printed instance of a report, and if the pages become separated each one is clearly identified by these key values so that they can easily be reunited.


You do not need to hard-code the sorting information shown on the bottom of a report. Your report can look at its own group-level information to learn how it's sorted and grouped.

For example, if a report has two sorting/grouping levels set by code and they are not static, placing the following expression in a text box on the report will show the current sorting information at the time of printing:

="Ordered by " & [Report].[GroupLevel](0).[ControlSource] _

& ", " & [Report].[GroupLevel](1).[ControlSource]


For more information on group level properties, see the section "Changing Report Properties Dynamically" later in this chapter.

="Page " & [Page] & " of " & [Pages]

Figure 18.1 shows a report that follows a standardized layout and contains the elements from the previous list.

Fig. 18.1

This report contains standard elements used by all reports in its source application.

In addition to the descriptive elements shown in figure 18.1, a more comprehensive reporting layout standard may include one or more of these elements:

=lci_gstrCurrentUser()

A saved report used as a template can have any or all of these listed attributes. In addition, you can save standard printer and margin information and a default code module with your report template.

As an alternative to creating a report template that you copy and paste to create new reports, you can utilize the new AutoFormat feature, which saves report format information for reuse. Access ships with six default formats, and you can also create your own.

A report AutoFormat is a saved definition of properties for the objects listed in Table 18.1. The listed settings are saved for each section and control displayed on the report used as the basis for the template. Control settings can be saved for each type of control-even the new TabControl object and the ActiveX container control.

Table 18.1 Property Values Saved With a Report AutoFormat

Object

Property

Comment

Report

Picture

Background picture

Report

PictureAlignment

Layout of picture

Report

PictureSizeMode

How picture is sized

Report

PictureTiling

Whether picture is tiled

Report

PictureType

Embedded or linked

Picture

ObjectPalette

Palette for background picture

Section

BackColor

Background color

Section

SpecialEffect

Outline effect

Control

BackColor

Background color

Control

BackStyle

Transparency

Control

BorderColor

Outline color

Control

BorderStyle

Outline style

Control

BorderWidth

Outline width

Control

ForeColor

Foreground color

Control

FontItalic

Italicization

Control

FontName

Font name

Control

FontSize

Font point size

Control

FontUnderline

Underlining

Control

FontWeight

Font emphasis

Control

LabelX

Placement of attached label

Control

LabelY

Placement of attached label

Control

SpecialEffect

Outline effect

Control

TextAlign

Positioning of text

A report AutoFormat is applied at the control, section, or report level. The saved format contains information for each of these elements, and when you select Format, AutoFormat... from the menu and pick a format, the format is applied to only the selected object in your report design window. For example, if only a text box is selected as opposed to the entire report, only the standard text box properties from the format are applied to the selected control.

To create a new AutoFormat, create a report with one control of each type and one section of each type and apply your desired property attributes to the objects listed previously in Table 18.1. Next, choose Format, AutoFormat... from the menu, click Customize..., and choose the option "Create a new AutoFormat...."

Note the difference between an AutoFormat and the saved report template I discussed earlier in this section. An AutoFormat applies the property formats listed in the previous table to existing sections and controls, but does not create new controls (such as your application's standard Report Footer labels). An AutoFormat also does not set all of the properties on a report that may be part of your standard template, such as the width and margin settings for printing.

Using both a standard report template and an AutoFormat template together can provide an effective means for creating standardized reports. Here is an example of how you would do so:

  1. Create a standard report template that has the default controls you want placed on all application reports, such as standard headers and footers. Save the template as a report.
  2. Create another report, one that has one of each type of control, and set the control defaults specified in Table 18.1 for each control to the attributes you want on your standard reports. Choose Format, AutoFormat... to save these property values together as your standard AutoFormat template.
  3. Set any additional border, color, effect, and font settings for each control on the report and choose Format, Set Control Defaults for each control to save the control's properties as the default values for the report. Save the report as Normal or whatever name you've chosen for the default report template in the Options dialog box.
  4. To create a new report, copy your company report template from step 1 to the Clipboard and paste it back in to the application, giving it the name of the new report you want to create. Change the informational text strings in the default controls inherited from the template to the appropriate values for the current report.
  5. Set the record source of the new report and populate it with the data-bound controls it needs. Each control, when created, will derive its properties from those you set on the control of the same type on the Normal report from step 3.
  6. Apply the AutoFormat saved in step 2 using the Format, AutoFormat... menu option. This sets the properties for sections and controls on the new report to values saved in the format template. Your report's attributes and layout should now match your defined standard for the application.

The AutoFormat information is saved in tables in the supporting database WZDAT80.MDT. If you want to preserve your auto formats in case of a crash, back up this file. You can also copy the file to the workstations of other users in order to distribute your formats to them, but be aware that your file will overwrite theirs, and if they have any saved formats of their own they will lose them. In a multi-developer environment, you can have one person be the owner of the master AutoFormat templates by changing them on his or her workstation, then distributing the master WZDAT80.MDT to all the other developers.


Saved AutoFormat templates can be created for forms in the same way as for reports.

Printing Forms as Reports

Access allows you to print forms. However, in keeping with the WYSIWYG (What You See is What You Get) tradition of Windows, when Access prints a form it looks like a printed form, which is often quite unappealing. For example, figure 18.2 shows the printed view of our standard Get form for filtering record lists, as described in Chapter 16, "Presenting Data to Users."

Fig. 18.2

The printout of an Access form usually looks too much like a form to make a useful hard copy report.

Note from figure 18.2 that forms printed as reports do not provide the sorting, grouping, and dynamic control and section sizing features found in reports. Thus, only infrequently will you be motivated to print forms to paper.

In order to be print-worthy, your form should meet these criteria:

The form shown in figure 18.3 provides an example of a form that qualifies as a better-behaved candidate for printing, because it has these attributes:

Fig. 18.3

This simple form makes a good candidate for a hard copy printout.

Refer to figure 18.7 in the next section to see this form in print preview mode.

Setting Properties for Printable Forms

In addition to providing a layout that prints well, your printable form must make proper use of available property settings that help create a report-like appearance from the form as it prints. The following property settings are important to help your form print well:


Access is not able to print preview a form that has the Border Style property set to Sizable and has been opened in dialog mode with the OpenForm method, like this:

DoCmd.OpenForm "frptProdOrderDet",,,,,acDialog


Attempting to preview a form opened this way produces run-time error number 2465. If your application needs to print preview a form that will be opened as a dialog with OpenForm, set the form's Border Style property to Dialog when originally designing the form.


Controls that are bound to data fields (that have a Control Source value) are always printed just as they are displayed on the screen, regardless of the form's Border Style, PopUp, and Modal properties.



The Display When property setting for a section overrides that of any controls on the section. Thus, for example, a control whose Display When setting is Always will not show on a printout if it's located on a Page Footer section with Display When set to Screen Only.


True Type fonts have both a display and a printer version of the font. Using these fonts for printable forms makes them independent of a specific printer driver and more useful across a large number of users with various printers. If you use only True Type fonts on a form that is printed, the value of the Layout for Print setting becomes unimportant.

Fig. 18.4

This toolbar combo box for font selection lists the fonts supported by the current printer.

When creating forms that may also be printed, Access allows you to tailor the form controls' properties to allow for two distinct views of the form: the on-screen view, and the printed view. The separate views are defined by the use of the Display When property on form sections and controls. The value for this property determines in which of the two form views the section or control will be displayed: Print Only, Screen Only, or Always.

When building forms that will also be printed, you can create two distinct form views (on-screen and hardcopy) by placing sections or controls on the form whose specific purpose is to add context when the form is printed. You may find it useful to provide additional information (the application name, for example) on the printed form versus the displayed form because the printed version may be distributed to users unfamiliar with its source application.


The Page Header and Page Footer sections do not have a Display When property setting. By design, these sections display during print only.

Figure 18.5 shows a diagram of some of the display property settings for the form controls in the current example form. The form includes controls and sections that are specifically designed for display or printing, but not both.

Fig. 18.5

Setting the display properties of form objects determines in which view the objects are printed.

Figure 18.6 shows the form as printed. Controls and sections that are set to display in the Screen Only view (see Table 18.2 below) are not displayed during printing. Contrast the printed version with the displayed version in figure 18.3 shown previously.

Fig. 18.6

The form from figures 18.3 and 18.5 is shown as it will print on the printer. Controls set to display on Screen Only are not shown.

In the example form from the previous figures, I've set the display properties of the form and its controls as shown in Table 18.2, in order to create a form that displays well and prints well.

Table 18.2 Properties for Elements of a Printable Form in Figure 18.5

Object

Property

Value

Comment

Form

Border Style

Sizable

Allows printing of unbound values

Form

Layout For Print

Yes

Use printer fonts

Form

Modal

No

Allows printing of unbound values

Form

Pop Up

No

Allows printing of unbound values

Form Header

Display When

Screen Only

Text for screen view only

Detail

Can Grow

Yes

Show all information

Detail

Can Shrink

Yes

Resize to best size

Detail

Display When

Always

Show on screen and report

Form Footer

Display When

Screen Only

Text for screen view only

Tuning the Display of Printable Forms

When you intend to allow users to print preview a form, it is likely that they will maximize the previewed form in order to see more of its data. Returning from preview to form view will leave the form in a maximized state. Add the following line of code to the Activate event of the form to restore the form's default size when preview mode ends:

DoCmd.Restore

Forms have no specific event that tells your code when a mode change between form view and preview takes place. Using Activate is a workaround that will usually prove adequate, because this event fires when a previewed form switches back to form view. However, be aware that this event also fires when the form is first opened and when focus moves to the form from another form, so any code you place in the event to solve reporting problems will fire in these instances.

When in print preview mode, you may not want the application's users to see Access' default preview menu, or toolbar, or custom menu, or the toolbar you've assigned for the form (see this chapter's sample database AES_RPTS.MDB on the CD-ROM for a sample custom print preview toolbar). You may instead want to provide your users with a custom menu bar or toolbar for the form while it's in preview mode. Your code can change the Toolbar property of the form as it enters preview mode, then change it back to its previous setting on returning to form view. In the event procedure that previews the form, use code like this:

Me.Tag = Me.Toolbar

Me.Toolbar = "lci_tbrPrvw"

Notice that the code preserves the value of the current Toolbar property setting (hides it in the Tag) before the change, so that the original setting is not lost. Reverse this process when the form changes back to form view. This approach is shown in the Form_Activate procedure in Listing 18.1. (Chapter 14, "Navigating in Forms and Applications" describes custom command bars in more detail.)

In addition to showing how to toggle custom command bars, the following navigation techniques for a printable form are demonstrated in Listing 18.1:

Listing 18.1 AES_Rpts.Mdb-Changing From the Default Toolbar to a
Custom Toolbar and Back

Private Sub Form_Activate()

' Purpose: Reset toolbars and form size

If Me.Tag <> "" Then

' When the toolbar is swapped during preview, its default

' value is stored in the Tag

Me.Toolbar = Me.Tag

End If

DoCmd.Restore

End Sub

Private Sub lstSale_DblClick(Cancel As Integer)

' Purpose: Drill-down to more detail

On Error Resume Next ' Allow printing to be canceled

If cbfValidate() Then ' Don't drill-down when criteria invalid

Me.Visible = False ' Hide the form

DoCmd.OpenReport "rptProdOrderDet", acPreview

End If

End Sub

Public Sub mtdPrntPrvw()

' Purpose: Change to preview mode, called by toolbar button

Me!lstSale = -1 ' Deselect list item for printing

Me.Tag = Me.Toolbar ' Preserve the default value

Me.Toolbar = "lci_tbrPrvw"

DoCmd.OpenForm Me.Name, acPreview

End Sub

The final element required for smooth navigation in this scenario is for the drill-down report (rptProdOrderDet) called by the form to redisplay the form as it closes. Recall that the form in the listing hides itself so that its drill-down report can be displayed, thus the drill-down report's Close event procedure must contain this line:

Forms!frptProdOrderDet.Visible = True

Filtering and Sorting Report Data

I see many users and developers create several-even dozens-of variations of a single report in order to present the same data to users in different layouts. For example, a telephone number report produced from a customer or contact database often must be printed two different sort orders: sorted by last name, and sorted by company name. A non-sophisticated approach to reporting these items would result in two different reports. A more sophisticated approach to the same problem would allow your application to use a single core telephone number report in a variety of different ways, and thus reduce the size of the database file and the number of objects in it.

In order to produce "reusable" reports, you must enable them with some sense of self-awareness and provide the code to detect and react to different states.

Selecting Reports and Report Data

As you noticed in Chapter 13, "Mastering Combo and List Boxes," and Chapter 16, "Presenting Data to Users," my applications rely heavily on List Box controls to present users with multiple options. A list box is a natural choice when allowing users to select reports or reporting options in an application. Figure 18.7 shows a simple menu form fmnuReport for selecting a report to print. The list of available reports is driven by a database table containing report names and configuration settings. When a report is added to the application, I simply add the report name and its attributes to the table, which in turn causes the report and its description to appear in the form's list.

Fig. 18.7

A simple menu form can be used to allow users to select a report to print.

The reporting menu in the figure is self-configuring, making it a truly smart object. The configuration information is passed to the form from its settings table zstblReport. The table has the structure shown in Table 18.3. All of the table fields are of type Text.

Table 18.3 Table Fields That Feed a Reporting Menu

Field

Contents

ReportName

Name of the report

ReportDesc

Short description of the report

ToOpen

A flag (F/R) that notes whether to open a criteria form or the report itself

OrderBy

Values for the RowSource of a combo box filled with sort order options

DatesReqd

Whether the form must collect From and To dates


Figure 18.8 shows the table zstblReport with some values.

Fig. 18.8

A configuration table can be used to provide values to a report selection menu form.

The menu form (refer back to fig. 18.7) that drives report selection has a List Box control (to list the available reports), a Combo Box control (to select the chosen report's sort order), and two Text Box controls (to select the starting and ending date range for the report). For each report, the field values in OrderBy and DatesReqd determine which of these controls will be displayed for the user.

When the menu form loads, it pulls values from the configuration table into the List Box control (the control's RowSource property is set to zstblReport). This list control serves two roles. First, it gives the user a list of reports to print. Secondly, it contains in its columns (hidden) the configuration information that the form needs for each report. Thus, each of the five fields shown in Table 18.3 is loaded into the list box, but only the first two (ReportName and ReportDesc) are shown to the user. To hide the last three columns, the list box's Column Widths property is set as follows:

1";1.5";0";0";0"

When the user clicks a report name in the list box, code runs to reconfigure the form for that report. Reconfiguration is simply a matter of displaying the combo box (and filling it), or enabling the text boxes so that the user can enter report criteria, or disabling all of these criteria controls, as determined by the OrderBy and DatesReqd (Column(3) and Column(4)) values in the list. The reconfiguration code is shown in Listing 18.2.

Listing 18.2 AES_Rpts.Mdb-Dynamically Enabling Criteria Selection Controls

Private Sub lstReport_Click()

' Purpose: Show the appropriate controls when a list selection is made

Call cbfCtlReset

With Me

If Not !lstReport.Column(3) = "" Then ' Show combo box

!cboOrder.Enabled = True

!cboOrder.RowSource = !lstReport.Column(3) ' Set the RowSource

' Select the first order as default

!cboOrder = !cboOrder.Column(0, 0)

End If

If !lstReport.Column(4) = True Then ' Show text boxes

!txtDateFrom.Enabled = True

!txtDateTo.Enabled = True

End If

End With

End Sub

Sub cbfCtlReset()

' Purpose: Re-hide the selection fields

With Me

!cboOrder = Null

!cboOrder.RowSource = ""

!cboOrder.Enabled = False

!txtDateFrom.Enabled = False

!txtDateTo.Enabled = False

End With

End Sub

When the user has clicked a report, entered the required selection criteria, and clicked the Print button on the form, the selected report or form is displayed, as enabled by the code in Listing 18.3.

Listing 18.3 AES_Rpts.Mdb-Checking the Criteria and Printing the
Selected Report

Private Sub cmdPrint_Click()

' Print the report or show a criteria form

If Me!lstReport.ListIndex = -1 Then ' No report selected

Beep

Exit Sub

End If

If cbfValidate() Then

If Me!lstReport.Column(2) = "F" Then ' Open a reporting form

DoCmd.OpenForm "f" & Me!lstReport

Else ' Open a report

On Error Resume Next ' Allow report to be canceled

DoCmd.OpenReport Me!lstReport, acPreview

End If

End If

End Sub

Private Function cbfValidate() As Boolean

' Purpose: Check for valid report criteria

Dim ctl As Control

Dim strMsg As String

With Me

' Validate combo box if showing

If !cboOrder.Enabled = True And IsNull(!cboOrder) Then

Set ctl = !cboOrder

strMsg = "Must select an order."

End If

' Validate text boxes if showing

If !txtDateFrom.Enabled = True Then

If IsNull(!txtDateFrom) Then

Set ctl = !txtDateFrom

strMsg = "Must select a start date."

ElseIf IsNull(!txtDateTo) Then

Set ctl = !txtDateTo

strMsg = "Must select an end date."

ElseIf !txtDateFrom > !txtDateTo Then

Set ctl = !txtDateFrom

strMsg = "Start date cannot come after end date."

End If

End If

If Len(strMsg) > 0 Then

Beep

MsgBox strMsg, vbOKOnly + vbCritical, "Validation Failed"

ctl.SetFocus

Else

cbfValidate = True

End If

End With

End Function

You can make your report selection forms as complex as your application requires. For example, the form shown in this topic does not allow the user to choose between preview mode and print mode. If you do not need to have the user automatically preview each report before printing, your reporting selection form could provide an option group to allow the user to specify Preview or Print.

The key concept here is that reports, unlike all other application objects, usually continue to be added to a system after deployment. Creating a table-driven, self-configuring approach to report selection that allows for the easy addition of reports to a system is the correct methodology for an expert solution.


Placing criteria selection fields on a reporting menu is only one of several ways to provide users with the ability to print subsets of data. Another example: if you allow users to create ad-hoc filters for a displayed form, it's now easy to print the same data on a report that is displayed on the form. When the user clicks the form button that prints the report related to the current form, the button can run code to copy the form's Filter property into the report's Filter property as the report opens. This causes the report to apply the pre-selected filtration from the form to its recordset.

Additionally, you can use the form's Filter property setting in WhereCondition argument of the OpenReport method in the form code that opens the related report, like this:

DoCmd.OpenReport "rptCust", , , Me.Filter

Reports can also be based on parameterized queries. A query with a criteria parameter displays a dialog box prompt for each criteria when the report is run. The downside to this technique is that the parameter information is captive to the query engine and cannot be easily validated by code in the calling form or called report.

Finally, most of the techniques for passing information into a form during its load process that are described in the section "Passing Information to a Form" in Chapter 14, "Navigating in Forms and Applications," are also applicable to reports. These techniques include setting the Tag property or passing information to a report via global variables.

Creating Drill-down Reports

When analyzing data, it can be very useful to view summary information and then to "drill-down" from that summary information to the related details. In Access, you must facilitate this behavior with a form, because reports and their controls do not provide events like Click and DblClick that a developer can use to make them interactive.

To create a drill-down environment, create a form that summarizes the desired information in a datasheet view or a List Box control. Provide the form with code that detects a user event and responds to the event by showing another form or a report with details for the selected record. A click or double-click action on a record, or a button click on a Details button, are two mechanisms for enabling this behavior.

For example, the form in Figure 18.9 shows a list box with aggregated sales order data by product. The query driving the list box control is a Top Values query that only shows the top ten products by sales volume. The product information in the query is limited by a criteria using the date ranges entered on the form. The SQL for the list box's Row Source property value looks like the statement in Listing 18.4.

Listing 18.4 AES_Rpts.Mdb-An SQL Statement That Selects the Top 10
Products by Order Volume

SELECT DISTINCTROW TOP 10

tblOrderDet.ProdID,

First(tblProd.ProdName) AS Product,

Sum(tblOrderDet.Quantity) AS Sales

FROM tblProd

INNER JOIN (tblOrder

INNER JOIN tblOrderDet ON tblOrder.OrderID = tblOrderDet.OrderID)

ON tblProd.ProdID = tblOrderDet.ProdID

WHERE tblOrder.OrderDate Between [Forms]![frptProdOrderDet]![txtDateFrom]

And [Forms]![frptProdOrderDet]![txtDateTo]

GROUP BY tblOrderDet.ProdID

ORDER BY Sum(tblOrderDet.Quantity) DESC

The first column of the list box (ProdID) is not displayed on the form, because it's required by code but not essential for the user.

Fig. 18.9

From this summary form, a user can double-click a product name to drill-down to its details.

The list box on the form shown contains a DblClick event procedure to open the related detail report for the ProdID value specified by the list box control, as shown in Listing 18.5.

Listing 18.5 AES_Rpts.Mdb-Drilling Down from a List Box to a Report

Private Sub lstSale_DblClick(Cancel As Integer)

On Error Resume Next ' Allow report to be canceled

DoCmd.OpenReport " rptProdOrderDet", acPreview _

, , "ProdID = Forms!frptProdOrderDet!lstSale" _

& " And (OrderDate Between Forms!frptProdOrderDet!txtDateFrom" _

& " And Forms!frptProdOrderDet!txtDateTo)"

End Sub

This code opens the detail report bound to the product information for the product and date range selected on the form.

In place of a list box, a continuous form or subform can be used to enable drilling-down in a similar fashion. Place a button or event on the continuous form that allows the user to print more detail for a selected record.


In a drill-down environment like the one described here, your application moves from forms to reports and back. Your users may appreciate the application more if each report displays itself maximized, then cleans up after itself (restores the calling form's size) as it closes:

Private Sub Report_Close()

DoCmd.Restore

End Sub

Private Sub Report_Open(Cancel As Integer)

DoCmd.Maximize

End Sub

The technique described here for drilling-down into reports from a controlling form is the easiest to enable, but is by no means the only way to implement drill-down. An alternative approach involves the use of command bars to allow the user to navigate from one record to another.

To drill-down using command bars, your application must provide users with a custom menu option on a menu bar, context menu, or toolbar button that allows them to indicate the desire to drill-down from the displayed report record. Your Drill-Down menu option will open a subordinate report with more information for the currently displayed record. The challenge in this technique is determining which record the user wants to see detailed. Reports do not provide an event model that allows the user to click or otherwise select a specific record. Thus, when the user selects your custom Drill-Down menu option, your code needs to be able to infer the context (in other words, deduce what the "current record" is). To detect the current report record, use this logic:

  1. Create a report that displays only a single record per page.
  2. When the user selects your drill-down option, determine which record is showing on the current report page (see code following).
  3. Display a drill-down report for the target record.

When your report displays a single record per page and the user makes the menu selection to drill-down, your code can grab the ID of the item on the currently displayed report page and proceed to drill-down. The code in Listing 18.6 shows how to determine which report record is currently showing.

Listing 18.6 How to Determine Which Record is Showing on a Report

' In the Declarations, establish a module-level recordset

Dim mdbs As Database

Dim mrst As Recordset

Private Sub Report_Open(Cancel As Integer)

' Purpose: On Open, establish a recordset matching the report's

Set mdbs = CurrentDb

Set mrst = mdbs.OpenRecordset(Me.RecordSource, dbOpenSnapshot)

mrst.MoveLast ' Cache all of the data

End Sub

Public Function mtdDrillDown()

' Purpose: Grab the displayed ID and drill-down

mrst.AbsolutePosition = Me.CurrentRecord - 1

DoCmd OpenReport "rptDrillDown", , , "CustID = " & mrst!CustID

End Function

Your custom command bar option for drilling-down would initiate the custom method mtdDrillDown shown in the listing.

The code in this listing highlights these Access characteristics:

If this seems like a convoluted approach, it is. The technique shown is required, however, because of the nature of the report layout engine. The engine is preparing the next record for presentation while you are viewing the current one, so you cannot simply ask the report for the value on the displayed page as you might presume. Assume that your customer report is ordered by customer number and that you are viewing the report page for customer 1. Surprisingly, entering the following string in the Debug window will show 2, not 1, because the report is busy laying-up the display of customer 2 while you are viewing customer 1:

Debug.Print Reports!rptCust.txtCustID

Thus, the code in the previous listing is needed to accurately grab the value of the displayed record by checking its CurrentRecord setting.


Information that will help you understand how to implement command bars on reports is contained in Chapter 14, "Navigating in Forms and Applications."


Also, in that chapter I provide a technique for loading a combo box on a toolbar with the values in a form, and navigating within the form based on the user's selection from the combo. This technique could also be used to list records on a currently displayed report and to facilitate drill-down to a specific record from the toolbar.


Changing Report Properties Dynamically

Report properties, like form properties, can be changed "on-the-fly" from code in order to create flexible and self-configuring reports. From the developer's standpoint, adequate properties and events exist in Access to detect the majority of status changes as a report is printing, and to modify the look of the report on demand.

The highest level report property is its RecordSource. Access reports provide an interesting opportunity with respect to this property. Reports do not look for the table or query defined in their RecordSource until the end of the Open event procedure (unlike a form, which looks for the record source before the Open event). This allows you to change a report's RecordSource, or set a filter or sort order, in the Open event procedure without incurring a performance penalty, because the report doesn't create an initial recordset from its saved record source prior to the property change. This behavior is a great asset for developers.

Frustratingly, Access reports do not have the OpenArgs property that's available on forms. If OpenArgs were enabled in reports, it would be simple to change the record source for a report by passing in the new value as an OpenReport method argument and running code in the Open event like this:

Me.RecordSource = Me.OpenArgs

Unfortunately, reports are missing this capability. The conundrum I mentioned earlier is now apparent: Your reports open awaiting record source information from your code, but provide no easy way to get it to them. To solve this situation, your code must pull any new RecordSource value from some external setting within the application.

Consider the drill-down form described in the prior topic and shown in Figure 18.9. This form is used to open a detail report, and the example in the previous topic showed how to pass filter criteria to the report as the WhereCondition argument on the OpenReport method. While useful, the technique demonstrated in Listing 18.5 earlier is limited because it can only change the report's WHERE clause, and not the entire record source or any other attributes of the source data.

A more advanced approach to the same problem would allow the report to look back to the drill-down form from its Open event and determine how to build its own record source. The following code shows an example of such an event procedure:

Me.RecordSource = "SELECT * FROM qrptProdOrderDet" _

& " WHERE ProdID = Forms!frptProdOrderDet!lstSale" _

& " And (OrderDate Between Forms!frptProdOrderDet!txtDateFrom" _

& " And Forms!frptProdOrderDet!txtDateTo)"


If the report in the current example used only one record source, it might run faster if the criteria shown were entered directly into the saved query qrptProdOrderDet and the query listed as the report's record source. The current example, however, assumes that the report's Open event has If...End If or Select Case...End Select logic to allow the creation of various different RecordSource properties during the report's launch.

In addition to the RecordSource, other report properties are equally good candidates for dynamic changes based on circumstances present at open or print time. Here are some other examples of property changes at print time that may prove useful:

Me.GroupLevel(0).ControlSource = "Category"

Creating Report Appendices

Some reports are more valuable when followed with supplemental information. Programmatically, it's simple to print two reports back-to-back. However, a few special challenges arise that may trip you up and are worthy of discussion. I'll approach the situations by explaining both what works and what doesn't work, with the hope of teaching you how to think creatively when debugging reporting challenges.

Consider the case where you have two reports that have a relationship to each other (rptChain_1 and rptChain_2 for this example). Your code needs to print both reports back-to-back. Because the reports are related, it would be nice to have the page numbering sequence continue from rptChain_1 onto rptChain_2. To facilitate this, you would be inclined to add your standard text box to the first report's footer to show the page number, using the Page property:

="Page " & [Page]

Next, you might locate a page number text box on the second report and add the total number of pages from the first report to the page counter to show the compound number, like this:

="Page " & ([Page]+[Reports]![rptChain_1].[Pages])

While your approach would be valid conceptually, it would stumble on two roadblocks:

DoCmd.OpenReport "rptChain_1", acViewNormal

DoCmd.OpenReport "rptChain_2", acViewNormal


Timing problems become more and more likely over time as applications like Access utilize the techniques for creating separate memory "threads" for different processes. In a multi-threading environment, you would not be able to reliably predict that the first report would open or complete before the second report, because their processing would run parallel.

To beat the second limitation, you must create a text box on rptChain_1 and set the ControlSource equal to =[Pages], then make the control invisible. Now, the Pages property will be initialized as the report formats and it will be available to code and external references, while still not printing on the report.

However, you still have not resolved the timing problem that may arise from chaining the reports in code. Solving the problem entails creating a more rigidly enforced flow of events. Knowing that the Pages property of rptChain_1 will certainly be available by the time the report is done printing gives you alternative approaches to chaining the reports. You can move the code that opens rptChain_2 from the original driving procedure into the module for rptChain_1, putting it in charge of the order of events, like this:

Private Sub Report_Close()

DoCmd.OpenReport "rptChain_2", acViewNormal

End Sub

This code structure means that the first report completes its print or preview processing before invoking the second report. The order of events appropriately meets your chaining objective. However, the fact that rptChain_1 is in the process of closing when it opens rptChain_2 means that the second report should not continue to make explicit reference to the first in its expressions (recall that you placed the following code in a control on the second report):

="Page " & ([Page]+[Reports]![rptChain_1].[Pages])

A wise strategy would be to gather the page counter information from the first report before it closes. (Ideally, if the OpenReport method had an OpenArgs argument, you could use that, but Access provides no such luxury.) To grab the page number from the first report, place code in the Open event of the second report, as shown in Listing 18.7.

Listing 18.7 AES_Rpts.Mdb-Grabbing the Page Count From the Previous
Report

Public pintPage As Integer ' Inherited starting number

Private Sub Report_Open(Cancel As Integer)

pintPage = Reports!rptChain_1.Pages ' Get starting number

DoCmd.Maximize

End Sub

The code in the previous listing ensures that whether the first report completes its closing process or not while the second report is running, the variable pintPage preserves the first report's page counter for reuse. The variable is created with Public scope so that it becomes a custom property of the second report and can be used in a text box to bump the printed page number by the first report's total page count:

="Page " & ([Page]+[Report].[pintPage])

You can also take a completely different approach to printing reports consecutively. By embedding one report inside another as a subreport, and locating the subreport on the footer of the parent report, the reports will print in sequence. The pagination problem is solved by this approach because the parent report's page footer provides the consecutive page numbering.

Consider the specter raised in the "Displaying Context on Reports" section earlier in this chapter, where I noted that it may be useful for some reports to include audit information. To print audit information, you can build a generic audit subreport and place the report on more than one parent report as required.

Figure 18.10 shows a subreport rsubAudit that is populated with unbound text fields. Each field derives its information from a property of the parent report.

Fig. 18.10

A subreport that does not refer to its parent report by name can be used as a component in multiple parents.

To use the generic auditing subreport shown, place it in the report footer of a parent report.

Getting the subreport to print on its own page at the end of the parent report is a little tricky; you must establish the following environment in order for the subreport to print correctly:

In the example report in figure 18.10, I created a group break on the expression =True, which serves as a bogus break expression. The value never changes, therefore it does not introduce multiple group breaks into the report, yet it does solve the current problem.


Set the Repeat Section property of the group header to Yes if the subform will span multiple pages. This setting instructs the group header to repeat on each new page, serving effectively as a page header for the subform.

The audit report's specific job is to display summary information about a report's properties at the end of a printout. The printed information in the example in figure 18.10 includes the record source of the report, sorting information, and so on. The audit information could be expanded to include other properties of the parent, such as group level information. Such audit pages on reports are useful when complex reports are printed and used for financial or legal purposes. The audit information clearly describes the exact content of the report as defined when it was printing.

You will be able to utilize the techniques in this topic to solve other situations that arise in your applications that are similar to the current audit report illustration. For example, you may need to print a detail report that is attached to a summary report, or an appendix to a report that lists references for the report.


In addition to their usefulness for subreports used as report appendages, the techniques described here will work when you need to create embedded subreports in any section of a report. The same tricks used here to cause the subreport to begin on a new page must be used when the subreport is located on the Detail or other section of a report.

Reporting Access Data to the Web

With the explosion of interest in the Internet and corporate intranets, it was only natural that Access 97 include support for publishing Access data in World Wide Web format.

HTML, the HyperText Markup Language, is the layout language of the Web. From Access 97, you can create static or dynamic Web-based versions of your Access data by using the menu option File, Save As HTML. This option launches the Publish to the Web Wizard and walks you through the export process.


You can start the Publish to the Web Wizard from your application code. First, add a reference to the file WZMAIN80.MDE that ships with Access. Then, call the function pub_modMain.pub_StartWiz from your VBA code.

The Publish to Web Wizard is a useful tool for creating one of three different types of data exports:


You can export tables, queries, forms, and reports to static HTML page format, and tables, queries, and forms to dynamic format. You cannot create dynamic Web-based reports from the wizard.



At the time of this writing, fully functional ASP technology was not available for my review in conjunction with Access 97. However, at first glance it appears to me that the ASP technology provides a significant improvement over the limited and frustrating IDC/HTX approach, so I encourage you to bypass IDC/HTX altogether if you have access to an ASP-compatible server by the time you read this.


One of the most notable benefits is the ability to include multiple SQL statements in a single ASP file, a capability not found in the IDC approach. Running more than one data request allows you to more easily create Web pages that truly resemble reports, with a report/subreport layout or aggregate computations such as totals.


When you run the publishing wizard (or alternately use File, Save As/Export... to save an object as HTML), the Web pages created by Access for viewing in a browser provide many of the same capabilities as their parent objects within Access. Internet Explorer provides background colors, various fonts and colors approximating those from the original presentation, a tabular data layout format, and so on. On the other hand, Web-published data does not incorporate the more advanced reporting features built in to Access such as pagination, snaking columns, and expressions, so exporting data with the File menu options does not provide a comprehensive solution.

The remainder of this chapter goes beyond the File menu options for exporting Web pages from Access and focuses on programmatic approaches to Web publishing. We'll explore how to enable your applications to create static Web reports using VBA code.

When you can create dynamic Web pages from tables or queries (as described above), and these pages can retrieve real-time Access data, why would you use static Web pages?

If your application must export Web-based reports to achieve one of the listed objectives (or a different one), you can write some fairly simple code to create attractive and accurate reports based on the original Access data. In general, you will write application code, as opposed to exposing the Publish to the Web Wizard to your users, if you are concerned about these two factors:

The following three topics introduce techniques for outputting Access data to HTML format from your applications.

Getting Started with HTML

Here it is-the dreaded HTML primer you were hoping you wouldn't find in this book. While everybody seems to have caught Internet Mania within the past year, I'm not writing this section because I'm Web-crazed. Actually, I believe that the Web will not replace the capabilities of Access to create user interfaces and to query and report data for a long time to come. This doesn't mean, however, that the Web cannot provide effective ways to disseminate information stored in Access databases-in fact, it can.

Given the limitations of Access 97, you can't export expert reports to expert Web pages without some knowledge of HTML syntax. In fact, you can't be a well-rounded solution developer in 1997 if you are unable to write program code to create Web documents from your applications. To do this, you must learn to output HTML pages from VBA. If you already speak HTML, skip onward to the next topic.


This section was devised to introduce you to HTML concepts that are specifically relevant to exporting Access data. It is not intended to cover this dynamic and complex language comprehensively.


Structuring an HTML Document

An HTML-based Web page is a file that is read into a browser application like Internet Explorer (IE) and displayed to the user. Formatting information in the file tells the browser how to lay out the text and other elements for display. Your HTML files can contain text, references to graphic files, and references (called hyperlinks) to other HTML files.

Within an HTML file, you create multiple elements. An element is a related pair of tags surrounding text, with the first tag (the start tag) toggling on a specific display attribute, and the trailing tag (the end tag) toggling the attribute off. Tags are always enclosed in the <> character pair, and an end tag is simply the same text as its start tag but preceded with /.

Here is an example of a tagged element from an HTML file. The start and end tag pair shown are pre-defined HTML syntax that turns on and off italics mode:

<I>This text is italicized!</I>

An attribute that is toggled on stays on until toggled off, thus your file can nest tags within other tags for compounded effects:

Normal, <B>bold, <I>bold italics</I>, bold</B> and normal.

You've deduced the tags <B> for bold and <I> for italics from the previous example; the tag pair for underlining is <U> and </U>. Other font attributes are set with the <FONT> </FONT> tags, which have three attributes that can be combined within the tag to define the font structure:

<FONT SIZE=n FACE="font-name" COLOR=color>

The SIZE= attribute allows you to specify a number from 1 to 7 to select one of the browser's built-in font sizes, with 1 as the smallest. The values for font color are found in Table 18.6 later in this section.

In addition to tag pairs, the HTML language has some directive statements that begin and end simply with a matched set of <> characters. Inline document comments are one example of such directives-a comment begins with <! and ends at the next >:

<!Purpose: This is a sample document.>

HTML documents should begin with a standard information block. The first line in the document should be the <!DOCTYPE> tag, which describes the document version to the browser. See your Web administrator to learn what the syntax and standards for this heading are in your company. The simplest variation of this tag is shown below; more complex variations include HTML version information:

<!DOCTYPE HTML>

The second element of a standard header block is denoted with the <HEAD> </HEAD> tag pair, which delimit the heading information for the document. In simple documents, the document heading is comprised of the document title, which displays in the browser's title bar and is delimited with <TITLE> and </TITLE>:

<HEAD>

<TITLE>Access Expert Solutions Sample Document</TITLE>

</HEAD>

More complex documents may contain reference information and browser directives in the heading. Consult a full HTML reference for more information.

The document body comes after the document heading, beginning with the <BODY> tag. The body element includes the main text and images for the page. Within the body element, carriage return/line feed information in the source document is ignored. Instead, you must physically tell the browser to insert a line break by using the <P> tag. When you place this in an HTML file, you see two lines of text:

<BODY>

<P>

This is the

first paragraph.

<P>

This is the

second paragraph.

</BODY>


<P> is the only tag noted here that does not require the use of its mate </P>, because it functions much like the vbCrLf constant does in VBA and inserts a line break immediately. The concept of pairing in this situation is irrelevant.

After the body section, a document should close with the end tag </HTML>.

Beyond the basic structural mechanics described so far, another important element of an HTML document is a hyperlink. When clicked, a hyperlink causes the browser to load the referenced page into the current window and move the current document down one level in its stack (browsers usually cache more than one page in a stack that can be navigated forward and backward).

Hyperlink tags begin with A (for anchor), then HREF= followed by a destination address, as in the following example. The text between the tag pair in the example would be underlined by the browser and would provide a clickable jump to my Internet site.

Visit the <A HREF="http://www.ldfinfo.com/">Access

Expert Solutions home site</A>.

Figure 18.11 shows a browser page demonstrating the tag examples shown so far in this topic.

Fig. 18.11

IE is showing a Web page with a title, paragraph breaks, text with attributes, and a hyperlink.

For showing outline-type structures within a document, HTML supports six levels of section heading tags-H1 through H6. These tags do not cause an outline view of the information, but they do allow you to define six different styles, one per heading, to give your information a nested look. The six styles are pre-defined by your browser but can be redefined in your style sheets (see the section "Creating HTML Reports Based on Styles" at the end of this chapter):

<H1>Regions</H1>

<H2>West</H2>

<P>Total sales: $140,125

<H2>East</H2>

<P>Total sales: $152,180

Table 18.4 lists a few more useful formatting tag pairs.

Table 18.4 Tag Pairs for Formatting Web Page Text

Tags

Meaning

<BLOCKQUOTE> </BLOCKQUOTE>

Indented text

<CENTER> </CENTER>

Centered

<LISTING> </LISTING>

Fixed (non-proportional) type

<PRE> </PRE>

Fixed (non-proportional) type

Figure 18.12 shows a browser page with examples generated by heading styles and the tags shown in Table 18.4.

Fig. 18.12

IE is showing a Web page with styles H1 through H3, plus block quote and fixed text.

Placing Tables in HTML Documents

With the basics of Web page layout in hand, we must move on to Web page tables. You cannot export tabular data from Access very attractively without some basic knowledge about how to layout a table in HTML. The <TABLE> tag begins a table, but there are several important attributes that you can combine with this tag to determine the table's characteristics, in this fashion:

<TABLE attribute1=value ... attributen=value>

Table 18.5 lists some important table definition attributes:

Table 18.5 Attributes for Defining an HTML Table Layout

Attribute Name

Values

Comment

ALIGN=

LEFT|RIGHT

Horizontal alignment of table

BACKGROUND=

url

Path to bitmap file

BGCOLOR=

color

Background color, see Table 18.6

BORDER=

n

Pixel width of border

BORDERCOLOR=

color

See Table 18.6

CELLPADDING=

n

Pixels between cell border and contents

CELLSPACING=

n

Pixels between table frame and interior cells

COLS=

n

Number of table columns

FRAME=

frame-type

See Table 18.7

RULES=

rule-type

See Table 18.8

WIDTH=

n | n%

Table width in pixels or percent of window

Table 18.6 lists the colors supported by Internet Explorer (other browsers may support different color values than those shown). These color values replace the placeholder color in the table syntax in Tables 18.5 and 18.9.

Table 18.6 Standard Color Values for IE

AQUA

BLACK

BLUE

FUCHSIA

GRAY

GREEN

LIME

MAROON

NAVY

OLIVE

PURPLE

RED

SILVER

TEAL

WHITE

YELLOW

Table 18.7 lists the values for the frame-type argument of the FRAME= tag shown in Table 18.5. This tag draws a border around all or part of the outside of the table:

Table 18.7 Types of Frames in the HTML <FRAME=> Tag

Value

Meaning

ABOVE

Border on top of table only

BELOW

Border on bottom of table only

BORDER

Border on all sides of table

BOX

Border on all sides of table

HSIDES

Border on top and bottom of table only

LHS

Border on left side of table only

RHS

Border on right side of table only

VOID

No borders

VSIDES

Border on left and right of table only

Table 18.8 lists the values for the rule-type argument of the RULES= tag shown in Table 18.5. This tag draws lines (inside borders) between cells within the table.

Table 18.8 Types of Cell Dividers in the <RULES=> Tag

Value

Meaning

ALL

All cells have lines

COLS

Vertical lines between columns

NONE

No cells have lines

ROWS

Horizontal lines between rows

To begin a table definition, establish a table format with the <TABLE> tag and specify its attributes. The prescribed formatting pervades the table(s) that follow until the next </TABLE> tag is reached, unless overridden by individual cell tags. The following HTML statement defines a table format that includes a thin blue frame and dividing lines between the columns:

<TABLE ALIGN=LEFT BORDER=1 BORDERCOLOR=BLUE CELLPADDING=2

CELLSPACING=0 FRAME=BORDER RULES=COLS

After establishing the table format, your Web page must add table cells with these tags:


You must prescribe attributes such as font information for each individual cell in a table. Each new cell definition (TD) resets to the default attributes.

Table row and cell attributes and values are listed in Table 18.9.

Table 18.9 Attributes for Table Tags TB, TD, and TH

Attribute Name

Values

Comment

ALIGN=

LEFT|CENTER|RIGHT

Horizontal alignment

BACKGROUND=

url

Path to bitmap file

BGCOLOR=

color

Background color, see Table 18.6

BORDERCOLOR=

color

See Table 18.6

COLSPAN=

n

Number of columns the cell spans

HEIGHT=

n

Height in pixels

ROWSPAN=

n

Number of rows the cell spans

VALIGN=

TOP|MIDDLE|BOTTOM

Vertical alignment within cell

WIDTH=

n

Width in pixels

Let's pull these table tags together into an example. Listing 18.8 shows HTML syntax to build a table; here's the process followed in the listing:

  1. Build a header for the table by creating a single-cell table the same width as the data table below it and centering a heading string in the cell.
  2. Using the <TABLE> tag, establish the general table attributes for the data table.
  3. Apply a <TR> tag to define the overall row attributes for each row.
  4. Finally, specify a <TD> tag element and corresponding string value for each table cell.

Listing 18.8 AES_HTML.Htm-Building a Simple Table With HTML

<!Begin a table>

<TABLE>

<!Top row is single heading cell>

<TR ALIGN=CENTER>

<TH ALIGN=CENTER WIDTH=120>

<FONT SIZE=3>

Table Heading

</FONT></TH></TR></TABLE>

<!Next come the column headers>

<TABLE ALIGN=LEFT BORDER=1 BORDERCOLOR=BLUE CELLPADDING=2

CELLSPACING=0 FRAME=BORDER RULES=COLS>

<TR>

<TH WIDTH=40><FONT SIZE=2>

JAN

</FONT></TH>

<TH WIDTH=40><FONT SIZE=2>

FEB

</FONT></TH>

<TH WIDTH=40><FONT SIZE=2>

MAR

</FONT></TH></TR>

<!Next come the data rows, 3 cells wide>

<TR>

<TD ALIGN=RIGHT><FONT SIZE=2>

100

</FONT></TD>

<TD ALIGN=RIGHT><FONT SIZE=2>

200

</FONT></TD>

<TD ALIGN=RIGHT><FONT SIZE=2>

300

</FONT></TD></TR>

<TR>

<TD ALIGN=RIGHT><FONT SIZE=2>

400

</FONT></TD>

<TD ALIGN=RIGHT><FONT SIZE=2>

500

</FONT></TD>

<TD ALIGN=RIGHT><FONT SIZE=2>

600

</FONT></TD></TR></TABLE>

Figure 18.13 shows the table generated by the script in the previous listing.

Fig. 18.13

Dozens of commands are required to create this rudimentary table using HTML.

The value of table formatting tags becomes apparent when you attempt to write code to publish higher quality Web pages from Access VBA than are available via the built-in export features. With a knowledge of table formatting and some simple code, your application can create Web-based reports that look outstanding.

Creating Simple Web Reports Using OutputTo

The OutputTo method has been expanded in Access 97 to include HTML export in dynamic and static formats. Within your code, you can create export routines that call this command and pass it a subset of data to publish.

The syntax for this command is as follows:

DoCmd.OutputTo ObjectType

[, ObjectName]

[, OutputFormat]

[, OutputFile]

[, AutoStart]

[, TemplateFile]

When exporting to HTML, you can specify an ObjectType argument of acOutputForm, acOutputQuery, acOutputReport, or acOutputTable. The values for the OutputFormat argument when creating Web pages are acFormatActiveXServer, acFormatHTML, and acFormatIIS. Because you cannot directly create dynamic Web pages from reports, these argument combinations are not allowed:

ObjectType:=acOutputReport, OutputFormat:=acFormatActiveXServer

ObjectType:=acOutputReport, OutputFormat:=acFormatIIS

For HTML-based output, use the AutoStart:=True argument with the OutputFormat:=acFormatHTML argument setting to launch IE immediately after export, loaded with the file specified by the OutputFile argument. The argument is ignored when the output format is IDC/HTX or ASP.

In the OutputTo command line, you can specify a template file that will be used to format the resulting Web page. If you specify a template file name but no path, Access looks in the HTML Template directory you've specified in the Options dialog box. If no path is specified, Access looks for the file in the default location \Program Files\Microsoft Office\Templates\Access.

HTML template files provide formatting information that is applied by the export routines to the exported data as it's moved to a Web page. Template files specific to Access contain custom commands (called tokens) defined for its export process. When you place a custom token in a template file, the export process replaces the token with data specific to the object being exported.

For example, place the following token in a template file:

<!--AccessTemplate_Title-->

During an export, Access replaces the token above with the name of the exported object, and wraps the object name in the standard HTML <TITLE> tag so that the string becomes the title for the Web page.

Table 18.10 shows the tokens that Access 97 understands in a template file. The tokens are shown in the order that they are usually located within a template file (the same order that a report flows in).

Table 18.10 Template File Tokens Applied to an Export

Token

Replaced With

<!--AccessTemplate_Title-->

Name of the object

<!--AcessTemplate_Body-->

Object's controls and data

<!--AccessTemplate_FirstPage-->

Hyperlink jump to first page of data

<!--AccessTemplate_PreviousPage-->

Hyperlink jump to previous page of data

<!--AccessTemplate_NextPage-->

Hyperlink jump to next page of data

<!--AccessTemplate_LastPage-->

Hyperlink jump to last page of data

<!--AccessTemplate_PageNumber-->

Page number


When Access exports a report, it creates one Web page (file) for each report page. The navigation tokens show in Table 18.10 for moving between pages are used in your template file to tell Access to provide page navigation hyperlinks between the pages. If you are exporting a table, query, or form, only one Web page is created and you do not need the navigation tokens.

Also, if you export a single-page report, the navigation tokens still cause the creation of page jump hyperlinks in the Web page, but they are inert. If you are exporting from code and can determine the number of pages on a report, you can create a template file that does not have navigation tokens and specify it when you export single-page reports.

In addition to the listed tokens, your template file can include any valid HTML tags. The tags will be copied into the output file as the Web page is built. If dynamic ASP or HTX file output formats are chosen, the commands in your template file are merged with the commands in the ASP or HTX output file.

To create reports for Web publication from your VBA code, you have two options. The first approach involves exporting a report directly to HTML using OutputTo and a template file, as discussed in this topic. The second approach, discussed in the next section, is to write Web pages completely from code.

When using OutputTo to handle your application's formatting and export work, your report may look adequate if you stay within the following guidelines:

As an example, assume you want to export the report rptOrderTop10_Web from code. The report is shown in Figure 18.14 and has been created using the guidelines from the preceding list. For example, the report summarizes all of the data in a table as opposed to needing criteria parameters.

Fig. 18.14

This report is optimized for export as a Web page.

Use the following command line to export the report in the figure to an HTML page:

DoCmd.OutputTo acOutputReport, "rptOrderTop10_Web" _

, acFormatHTML, "C:\Data\Top10Web.Htm"

The result of executing this command against the sample "Top 10" orders report is shown in figure 18.15.

Fig. 18.15

This Web page was created by exporting an Access report with OutputTo.

Notice that the Access export process did a decent job of creating a Web page providing the information from the source report. However, the formatting of the report was not fully carried into the exported Web page. For example, the graphic lines have been lost and the column headers are no longer bold. You are able to create Web pages from code that are more visually appealing than are produced by OutputTo. See the next section.

Outputting Formatted HTML Reports

A alternative to using OutputTo involves exporting database data as well as the Web page layout information from your code. Whereas OutputTo makes its "best guess" about the desired layout of the Web page, programmatic export gives you total control over the final look of the Web page and the information on it.

Consider the techniques you learned for creating Web pages and tables in the previous section "Getting Started with HTML." Armed only with the information in that section, you should be able to produce a Web report superior in appearance to one created by the OutputTo method (as shown in figure 18.15 in the previous section).

In addition to improving on the look of the exported report, your code can improve on OutputTo in these ways:

<!Jump to page 2>

<A HREF="#Page2">

...

<!Begin page 2>

<A NAME="Page2">

Let's create a set of routines to build a Web-based report similar to that produced by the bulk export of rptOrderTop10_Web in the previous section. Our objective for this code is to provide a more attractive report than the one shown in figure 18.15.

Here are the steps we'll take to create a Web export engine:

  1. Create a class object clsWebReport to contain all of the logic for Web reporting.
  2. Add custom properties to the class object that are set with values that will control the export.
  3. Add a custom method to the class to generate an HTML file based upon the property settings.

A modest amount of code is required to create the reporting engine. In describing the process, I assume that you are familiar with class objects and custom properties from Chapter 12, "Understanding Form Structures and Coding," and with the basics of VBA such as file input/output, arrays, and user-defined types.

The first step is to create the data structures used in the class object. Listing 18.9 shows the Declarations section of the module clsWebReport.

Listing 18.9 AES_Rpts.Mdb-Declarations for a Web Reporting Class Object

Option Base 1 ' 1-based arrays

Private Const mcstrQuote As String = """"

Private mdblSums() As Double ' Column sums

' Structures for custom properties

Private Type mtypCols ' Report columns

strHeading As String ' Column header display string

strField As String ' Bound field name

strFormat As String ' Column format

intWidth As Integer ' Display width

blnSum As Boolean ' Total the field

End Type

Private Type mtypWebReport ' Structure for one web file

intCols As Integer ' Number of columns: prpColumns

strFilename As String ' Output filename: prpFilename

strName As String ' Report name: prpName

strSQL As String ' Record source: prpSQL

strTitle As String ' Report title: prpTitle

tCols() As mtypCols ' Column information: prpCol

End Type

Private mtWebReport As mtypWebReport

The primary information storage device for the class is the variable mtWebReport based on the type structure mtypWebReport. It contains all of the settings required for a single Web export session: the number of columns for the report, the output filename to create, the SQL statement for the source data, and so forth. Each of the values for the structure is fed by a custom property procedure such as this:

Public Property Let prpFilename(rstrFilename As String)

' Purpose: Set the destination filename

mtWebReport.strFilename = rstrFilename

End Property

When you use the class object, you set the custom properties in the same way you set an intrinsic property:

clsWebReport.prpFilename = "C:\Data\Top10VBA.Htm"

Notice that the custom property tCols() is an array variable based on another custom type definition mtypCols. This allows for the storage of multiple columns for the report and multiple attributes for each column: the column heading, the bound field name, and so forth. The custom property setting for this structure is a little more complicated than prpFilename, and is shown in Listing 18.10.

Listing 18.10 AES_Rpts.Mdb-Complex Custom Property Procedure to Set
Values Into a Custom Type Array

Public Property Let prpCol(rstrAttrib As String, rintCol As Integer _

, rvarValue As Variant)

' Purpose: Set attribute of a report column

' Arguments: rstrAttrib:=Attribute name

' rintCol:= Report column number, 1-based

' rvarValue:= Attribute value

Select Case rstrAttrib

Case "Heading"

mtWebReport.tCols(rintCol).strHeading = rvarValue

Case "Field"

mtWebReport.tCols(rintCol).strField = rvarValue

Case "Format"

mtWebReport.tCols(rintCol).strFormat = rvarValue

Case "Width"

mtWebReport.tCols(rintCol).intWidth = rvarValue

Case "Sum"

mtWebReport.tCols(rintCol).blnSum = rvarValue

Case Else

Beep

MsgBox "Invalid property name: " & rstrAttrib, vbCritical, "prpCol"

End Select

End Property

For each report column, the calling code that populates the custom property in the listing must specify which element of the structure to populate. The calling convention to set the custom heading value for column 2 of the report using the custom property looks like this:

clsWebReport.prpCol("Heading", 2) = "Company"

The engine of the Web reporting class is, of course, the code that produces the HTML file. In the current example, the class object is generic enough that it could be used for a variety of purposes. However, the class is specifically tailored for reports with a few columns and no free-form leading or trailing text. Also, font and layout information is standardized in the code. However, any attributes that are in the code could just as easily be passed in as more custom properties on the class. Thus, the value of creating a class object is to make it self-contained enough that it can be reused in multiple applications, and also to make it generic enough that it can be used in a variety of ways.

Listing 18.11 shows the driving routine that generates the Web report. It is built as a custom method on the class. Notice how simple the process is:

Listing 18.11 AES_Rpts.Mdb-Driving Routine to Create a Web Report File

Public Sub mtdGenerate()

' Purpose: Generate a Web report

Dim dbs As Database

Dim intFile As Integer

Dim intLoc As Integer

Dim intPage As Integer

Dim rtbl As Recordset

Dim strOut As String ' Output string

intPage = 1 ' This routine does not deal with pagination yet

' Get standard strings

Set dbs = CurrentDb

Set rtbl = dbs.OpenRecordset("zstblWebReport", dbOpenTable)

rtbl.Index = "PrimaryKey"

' Create output file

intFile = FreeFile

Open mtWebReport.strFilename For Output As #intFile

' Create HTML Header

rtbl.Seek "=", "HTMLHead"

strOut = rtbl!StringValue

intLoc = InStr(strOut, "{webreporttitle}")

strOut = Left(strOut, intLoc - 1) & mtWebReport.strTitle _

& Mid(strOut, intLoc + 16)

Print #intFile, strOut

' Create report table

rtbl.Seek "=", "SystemName"

Print #intFile, cbfTableCreate(rtbl!StringValue _

, mtWebReport.strTitle)

' Create report footer

rtbl.Seek "=", "ReportFooter"

Print #intFile, cbfFooterCreate(rtbl!StringValue, intPage)

' Create HTML footer

rtbl.Seek "=", "HTMLFoot"

Print #intFile, rtbl!StringValue

MsgBox "File " & mtWebReport.strFilename & " created."_

, vbInformation, cstrProc

End Sub

In order to make the process of Web reporting truly generic, I've based the class on a table of standard strings, as demonstrated by the listing. Using a table means that each different application can customize the strings displayed by the Web browser or written into the HTML file's structure without modifying the code. Within the table strings are replaceable placeholders used by the code, such as {webreportdatetime} to tell the class to insert the date and time at the specified location.

Placing a string like the HTML file header tags in a database table provides three primary benefits. The first is that the class code is uncluttered by such mundane text. The second is that the programmer using the class does not need to pass the same information to the class over and over as a property. And the third benefit is that, when the HTML standard changes (as it does with regularity), reconfiguring the behavior of the class does not require opening the code, only the table. The configuration table is shown in Figure 18.16.

Fig. 18.16

The Web reporting class engine is customized by changing values in this configuration table.

Building the table of report data values in the HMTL file is a very straightforward process, controlled by the procedure cbfTableCreate; here is the flow of the code for the function:

Listing 18.12 AES_Rpts.Mdb-Setup Portion of a Routine to Create a Web
Report Table

Private Function cbfTableCreate(ParamArray avarHeadings() As Variant) _

As String

' Purpose: Create table of report data with headings

' Arguments: avarHeadings():=Heading lines

' Table cell constants

Const cintHeight As Integer = 15 ' Cell height

Const cintLeft As Integer = 30 ' Left-side offset for table

Const cstrCellFont As String _

= "<FONT SIZE=2 FACE=""Arial"" COLOR=BLACK>"

Const cstrCellFontSum As String _

= "<FONT SIZE=2 FACE=""Arial"" COLOR=BLUE>"

Dim blnSumRow As Boolean ' Make a row with column sums

Dim dbs As Database

Dim iint As Integer

Dim intWidth As Integer ' Total width

Dim rst As Recordset

Dim strAlign As String ' Cell alignment

Dim strValue As String ' Cell value

Dim strWork As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(mtWebReport.strSQL)

' Compute table width

For iint = 1 To mtWebReport.intCols

intWidth = intWidth + mtWebReport.tCols(iint).intWidth

Next iint

intWidth = intWidth ' + cintLeft

Listing 18.13 AES_Rpts.Mdb-Create the Caption Lines for a Web Report
Table

' Create table caption headings

For iint = 0 To UBound(avarHeadings)

strWork = strWork _

& "<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>" & vbCrLf _

& "<TR HEIGHT=26>" & vbCrLf _

& "<TD WIDTH=" & cintLeft & "></TD>" & vbCrLf _

& "<TH WIDTH=" & intWidth & " ALIGN=CENTER><B><I>" & vbCrLf _

& "<FONT SIZE=5 FACE=" & mcstrQuote & "Times New Roman" _

& mcstrQuote & " COLOR=BLUE>" & vbCrLf _

& avarHeadings(iint) & "</FONT></B></I></TH></TR>" & vbCrLf _

& "</TABLE>" & vbCrLf

Next iint

Listing 18.14 AES_Rpts.Mdb-Create the Column Headings for a Web Report
Table

' Create column heading row

strWork = strWork & "<P>" & vbCrLf _

& "<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>" & vbCrLf _

& "<TR HEIGHT=" & (cintHeight * 1.2) & ">" & vbCrLf _

& "<TH WIDTH=" & cintLeft & "></TH>" & vbCrLf

For iint = 1 To mtWebReport.intCols

' Columns that are summed are aligned right

strAlign = IIf(mtWebReport.tCols(iint).blnSum, " ALIGN=RIGHT" _

, " ALIGN=LEFT")

strWork = strWork _

& "<TH WIDTH=" & mtWebReport.tCols(iint).intWidth & strAlign _

& "><B><U>" & cstrCellFont & mtWebReport.tCols(iint).strHeading _

& "</FONT></B></U></TH>" & vbCrLf

Next iint

strWork = strWork & "</TR></TABLE>" & vbCrLf

Listing 18.15 AES_Rpts.Mdb-Create the Table Cells for a Web Report Table

' Create column data rows

rst.MoveLast ' Pull all rows to cache

rst.MoveFirst

Do While Not rst.EOF

strWork = strWork _

& "<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>" & vbCrLf _

& "<TR HEIGHT=" & cintHeight & ">" & vbCrLf _

& "<TD WIDTH=" & cintLeft & "></TD>" & vbCrLf

For iint = 1 To mtWebReport.intCols

' Columns that are summed are aligned right

If mtWebReport.tCols(iint).blnSum Then ' Running sum

blnSumRow = True

strAlign = " ALIGN=RIGHT"

mdblSums(iint) = mdblSums(iint) _

+ rst(mtWebReport.tCols(iint).strField)

strValue = Format(rst(mtWebReport.tCols(iint).strField) _

, mtWebReport.tCols(iint).strFormat)

Else

strAlign = " ALIGN=LEFT" ' Default alignment

strValue = rst(mtWebReport.tCols(iint).strField)

End If

' Apply underline to last row for summed columns

If rst.AbsolutePosition + 1 = rst.RecordCount _

And mtWebReport.tCols(iint).blnSum Then

strWork = strWork _

& "<TD WIDTH=" & mtWebReport.tCols(iint).intWidth _

& strAlign & ">" & cstrCellFont & "<U>" & strValue _

& "</FONT></U></TD>" & vbCrLf

Else ' No format

strWork = strWork _

& "<TD WIDTH=" & mtWebReport.tCols(iint).intWidth _

& strAlign & ">" & cstrCellFont & strValue _

& "</FONT></TD>" & vbCrLf

End If

Next iint

strWork = strWork & "</TR></TABLE>" & vbCrLf

rst.MoveNext

Loop

Listing 18.16 AES_Rpts.Mdb-Create a Row of Totals for a Web Report Table

' Create summary row

If blnSumRow Then

strWork = strWork _

& "<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>" & vbCrLf _

& "<TR HEIGHT=" & cintHeight & ">" & vbCrLf _

& "<TD WIDTH=" & cintLeft & "></TD>" & vbCrLf

For iint = 1 To mtWebReport.intCols

' Columns that are summed are aligned right

If mtWebReport.tCols(iint).blnSum Then ' Running sum

strAlign = " ALIGN=RIGHT"

strValue = Format(mdblSums(iint) _

, mtWebReport.tCols(iint).strFormat)

Else

strValue = ""

End If

strWork = strWork _

& "<TD WIDTH=" & mtWebReport.tCols(iint).intWidth _

& strAlign & ">" & cstrCellFontSum & strValue _

& "</FONT></TD>" & vbCrLf

Next iint

strWork = strWork & "</TR></TABLE>" & vbCrLf

End If

cbfTableCreate = strWork

End Function

The final piece of the puzzle is the code that sets the attributes and controls the process. While the class contains several powerful chunks of code, the ideal is that the investment in coding effort can be reused over and over, and can be reused very easily. Using custom properties that can be viewed in the Object Browser and good naming conventions that help the user understand the class' structure make reusing even a complex object a very uncomplicated task.

Listing 18.17 shows the code that creates an instance of the class and tells it how to create a Web report file.

Listing 18.17 AES_Rpts.Mdb-Routine That Drives the Web Reporting Class

Sub WebReport()

' Purpose: Print report to Web page

Dim clsWebReport As New clsWebReport

clsWebReport.prpColumns = 4

clsWebReport.prpFilename = "C:\Data\Top10VBA.Htm"

clsWebReport.prpName = "Top10VBA"

clsWebReport.prpSQL = "qrptOrderTop10_WebExport"

clsWebReport.prpTitle = "Ten Largest Orders in Our History"

clsWebReport.prpCol("Heading", 1) = "Order Date"

clsWebReport.prpCol("Heading", 2) = "Company"

clsWebReport.prpCol("Heading", 3) = "Order ID"

clsWebReport.prpCol("Heading", 4) = "Amount"

clsWebReport.prpCol("Field", 1) = "OrderDate"

clsWebReport.prpCol("Field", 2) = "CompName"

clsWebReport.prpCol("Field", 3) = "OrderID"

clsWebReport.prpCol("Field", 4) = "Extn"

clsWebReport.prpCol("Format", 4) = "Currency"

clsWebReport.prpCol("Width", 1) = 90

clsWebReport.prpCol("Width", 2) = 210

clsWebReport.prpCol("Width", 3) = 70

clsWebReport.prpCol("Width", 4) = 80

clsWebReport.prpCol("Sum", 4) = True

clsWebReport.mtdGenerate

End Sub

Figure 18.17 shows the end result of the machinations in this section. Notice that the Web report produced by our class object is more attractive than the one produced by Access and shown in Figure 18.15. Using our code, we were able to improve the spacing and layout, use bold font for column headings, underline summed columns, add color, and otherwise fully control the appearance of the output.

Fig. 18.17

This Web report was produced entirely by code in the Web Report class object.

The objective for the code in the previous listings was to produce an attractive report (more attractive than was available using built-in Access export features). With a few more additions, other objectives could have been achieved by the code instead. For example, once you have mastered writing static HTML reports from code, you can experiment with writing dynamic ones (HTML pages that extract their data from the source database using ASP files). Additionally, you can build drill-down reporting systems that place hyperlinks on Web reports to jump from a line of summary information to a related on-line detail report.


Paradoxically, it is actually easier to use Web-based reports to do data drill-down than it is to use Access reports. Compare the capabilities of HTML's hyperlinks described in this section to the information in the section "Creating Drill-down Reports" in this chapter and you'll understand why.

For example, the summary report built in this section could be easily be written with a hyperlink on each Order ID column value. Clicking on the hyperlink could jump to a detail Web page for that order, also written from a customized export routine. Alternately, the detail information could be included in the Web page with the summary information, and the hyperlink in the summary area could simply jump to the detail further down in the file.


From Here...


1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.