Making Your Access Reports Dynamic

<< Click to Display Table of Contents >>

Navigation:  Reports >

Making Your Access Reports Dynamic

Access 2010/2007

This article shows you why you should think about making your reports dynamic (interactive) and it discusses colour highlighting using conditional formatting.

 

What Is and Why would you might need to add Dynamic Reports to your database

One of the unheralded improvements to Access 2007 was a new way of viewing reports called the ReportView. This view allows you to make your report much more dynamic by allowing you make report text into hyperlinks and attach controls like button for rows of data. If you were upgrading an Access 2003 database and you used the preview mode for a report, it is unlikely that you noticed any improvement in the functionality of your database. Once you see how dynamic reporting works, you may have to think about how you use all your reports.  Its even possible that you may redesign your end user interface and give your users more reports and allow them to drill into data safely through the reports rather than jump around forms and queries to find information.

ad7468x60

An Example

Lets see an example of a dynamic report in action (Figure 1).  In this mining report, I am displaying a drillhole header record (black grey area), then I have a subreport that displays geology downhole observations (white area) and then a sub-sub report that shows many laboratory batch results for that sample number if they exist. Its a report that is a complicated as a human can comprehend but the users wanted more information about the results from the laboratory. To satisfy them, I have included a blue hyperlink like BR10122798 (circled in red).  If you were to click on this you would then see all the details for BR10122798 as shown in Figure 2  

201201_gr3_1

Figure 1 - Adding hyperlinks to reports

Once you click on the hyperlink in the report, a form or report is opened using a filter that is derived from the text that you clicked on. The form that I have opened in this case is a datasheet view with a subdatasheet showing the details.

201201_gr3_2

Figure 2 - The Form that is opened in readonly mode from the hyperlink on the report

 

More Examples

 

Download a Microsoft template to look for good samples of reportview dynamic reports.  In Figure 3 you will see the Active Tasks report in the 2007 Tasks template.  In this example I have clicked on the Task hyperlink which directly opens the Task form and allows you to edit the details. The Microsoft samples use embedded Macros and not vba but the important thing to glean from the Microsoft templates is the really neat user interface.  I have used techniques in the templates in many of my 2007+ databases.

 

201201_gr3_3

Figure 3 - Showing a dynamic report hyperlink working in the Tasks database template.

 

You do not need to stop at just internal Access forms and reports. In my companies billing database, I generate hyperlinks to pdf invoices with the same name as the invoice number in the record.

 

Technical Details

 

To open a report in dynamic form, use the acViewReport option in the OpenReport VBA command as I have done for the Report button in Figure 4.

 

 DoCmd.OpenReport "Fxr_DrillCollars", acViewReport, "", "HoleNo = '" & Me!HoleNo & "'"

 

 

201201_gr3_4_zoom25

Figure 4 - Click to see the report button that relates to the code above

 

To make the hyperlink work in the sub-sub-report, I write this simple line of code for the BatchNo field onclick event. You can see that I use the value in the BatchNo field to filter the form when I open it. I also open the form in readonly mode as I dont want record to be edited by a person reading this report.

 

 

Private Sub BatchNo_Click()

 

  DoCmd.OpenForm "fxBatches", acFormDS, , "([Batches].[BatchNo]='" & BatchNo & "') ", acFormReadOnly

 

End Sub

 

Coexisting With Preview and Printing Mode

 

It is still very likely that you will also want to open the report in Preview mode acPreview and Printing mode acNormal. If this is the case, you need to set the following property for hyperlinks so that the hyperlink becomes plain text in the Preview and Printing modes.  So go to the Formatting tab in properties and set

 

Display as Hyperlink = "Screen Only"

 

In figure 5 you can see how the hyperlink is now being displayed as text

 

201201_gr3_5

Figure 5 - the hyperlinks have disappeared in the Preview mode

 

We sell FMS Tools and bundle them with other discounted and free products

 

Conditional Formatting

 

If you look at Figure 1, you will see coloured writing. This displays interesting, good and great bauxite results. Colour coding is achieved using Conditional Formatting equations. There is a subtle technique that I am using here, rather than colouring the results, I repeat the field and make it one character wide. Then I colour code that narrow field using the conditional formating equations shown in Figure 6. As the field is only one character wide, it displays a hash sign. If the results is below the required cutoff, I display the text in the same colour as the background. Remenber, Colour coding text makes it hard to read, especially if you are colour blind.

 

201201_gr3_6

Figure 6 - Conditional formatting formula used in the report ( see RHS of figure 1)

 

 

 

Mining Details

 

The sample displayed in this article is a mining drilling report. If you are a mining person, the data you see is arranged as follows

 

Collar Table

Geology with sample numbers

  Samples - Multiple Sieve Sizes for each sample number

When you Click on the Batch number in the report you go to a form with

Batch Results

all the Sample records for that batch

 

 

Summary

 

I hope that has inspired you to get more out of your reports.  None of these tricks should take more than an hour or two of your time if you already have the reports and forms ready to hyperlink to.

 

Garry Robinson - Manager of vb123.com professionals

 

You may now wish to read Garry' Editorial entitled
Software stagnation - Doing nothing can cost you money