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.
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
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.
Figure 2 - The Form that is opened in readonly mode from the hyperlink on the report
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.
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.
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 & "'"
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
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
Figure 5 - the hyperlinks have disappeared in the Preview mode
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.
Figure 6 - Conditional formatting formula used in the report ( see RHS of figure 1)
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
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
all the Sample records for that batch
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