18

Creating Powerful Reports

WHAT'S IN THIS CHAPTER?

• Customizing reports
• Using subreports
• Giving users greater control
• Adding professional polish to reports

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The code downloads for this chapter are found at www.wiley.com/go/proaccess2013prog.com on the Download Code tab. The chapter download file includes five database files:

• 2013Team.accdb
• ReportExamples.accdb
• ReportExamplesNW.accdb
• PDFDemo.accdb
• PDFDemo.mdb

Also included are the code snippets for this chapter, which are all located in the file named 850832_ch18_CodeSnippets.txt. You will also find PDFDemo.pdf to use with the PDF examples.

The built-in report writer in Access has always been one of the most powerful and popular features of Access. In addition to the powerful wizards that can create formatted reports in a matter of minutes, Access has an easy-to-learn interface used to create, revise, and customize reports. You can use built-in Macros and VBA, or a combination thereof to modify reports on the fly at run time, either from user input or from values within the database.

Whether you have been working with Access reports for years or are just venturing into reports, you can easily find an abundance of good tips and examples. So the focus of this chapter is to point out some built-in features and fresh ideas to demonstrate how you can quickly benefit by leveraging them in your reports. It provides some powerful tools and examples that you can incorporate into your solutions to give your users more flexibility and control over the content selection, organization and display of their reports.

In addition to the techniques, features, and tips that are covered in the chapter, you can delve into the code and get more ideas by working with the forms and reports in the database files that accompany the chapter. You can use these, in whole or in part, to quickly add features and functionality that will improve the user interface and give your Access solutions a more professional presentation.

As with any solution, the style and visual enhancements of the final design is based on the project and developer. With that in mind, the examples are kept relatively plain so that you can easily extract portions and apply your choice of formatting to them, include them in templates, or use them in other manners.

INTRODUCTION TO REPORTS

Storing information in a database is only part of the challenge. You must also make it easy to enter, retrieve, review, and report in ways that are usable and meaningful to the consumers of the data. Just because the data is contained in well-designed tables does not mean that anyone but the application developer can see the data. Queries, forms, and reports are the principal methods used to deliver and present meaningful information to the consumer. This chapter focuses on reports, and builds on information about queries, macros, and forms that you may already know or was covered in earlier chapters.

Queries are at the heart of most reports and forms. Queries are used to return specific sets of data that will be displayed in a report. Having good query building skills is a big plus when designing reports. Chapter 16, “Programming using VBA, APIs, and Macros,” includes discussions and examples that will help you enhance and leverage queries to retrieve precisely the data that you want and expect.

To give you a common foundation for working with subsequent examples, this chapter starts with a brief overview of some of the fundamentals for creating reports. The examples in the first part of the chapter leverage built-in features, such as the wizards and various properties of the report itself. You will see how to leverage these features to quickly give users the ability to create event badges and personalized session schedules that incorporate multiple subreports in one report.

Later in the chapter, the examples focus on leveraging events properties to give users control over the content and layout of the reports. You will again start with simple examples that become the foundation for creating a form that allows users to specify the way the data is grouped and summarized in their reports. The discussion wraps up with some tips on providing professional polish. But it doesn't stop there. As an added bonus, you will also find an example that demonstrates how to use Access to fill in PDF forms. You get the complete package, database, code, and demo PDF files.

CREATING REPORTS

This section is designed to establish a common foundation to support the rapid progress through the examples presented later in the chapter. Whether your expertise is working with other areas of Access or you are interested in a quick review, you can often find helpful nuggets by starting from a fresh perspective.

The discussion and examples are intended to build on each other and to give you a variety of options to use in your solutions. Some examples are demonstrated techniques that can save you time or add professional polish, other examples provide code and features that can be incorporated into your solutions to provide greater flexibility and control for your users.

You will start with examples using the Report Designer on a blank report. Then, you'll use the Report Wizard and the Label Wizard. Even if you typically don't use these wizards, it is good to see how they work as they tend to provide insights into approaches that you may not have thought about. After that, the examples will use the reports that you created to demonstrate how to modify the reports to fit your specific requirements. Some of the techniques will be familiar, but they serve multiple purposes here. In addition to providing alternative approaches for accomplishing a task, they may also help you troubleshoot or update a solution created by someone else.

Fundamentals

Although you may do some heavy lifting to ensure you're presenting the correct data, the majority of your time may easily be spent customizing the layout so that the end result achieves the desired look. You'll want to invest a little extra effort to ensure that your reports present the level of professionalism that adds to, rather than detracts from, the information being conveyed. The property sheet is a great resource for managing both the content and presentation of reports.

Property Sheet

Three of the most common approaches to customizing layouts include using a template (or copying objects from one), using the Property Sheet, and using the tools on the Design ribbon. Quite often, it will be a combination of all three. For now, the emphasis will be on the Property Sheet Format tab. You can open the Property Sheet from the Design ribbon or by right-clicking the report and selecting Properties at the bottom of the list. Figure 18-1 is a composite figure showing the Format tabs for both the Report and the Detail sections, as well as Group and Sort window. It is helpful to be familiar with the location of some of properties as you will use them extensively as you follow the examples and create your own reports.

As you are looking at the Format tab, you'll be familiar with most of the properties; but the focus here is to identify ones that you can leverage most effectively to create a professional presentation in your reports. With that in mind, there are a few properties that you'll most likely use, based on the part of the report or the control type.

FIGURE 18-1: Property sheet tabs and group and sortcontrol

Key Format Properties applicable to all objects include:

• Caption should be used to display user friendly names.
• Font selection should focus on making the content easy to read, and on judicious use of script or fancy styles.
• Font Size and Weight selections should be used to enhance clarity, purpose and readability. Use bolder and/or larger sizes in headings and totals to make them stand out. And, perhaps most importantly, be consistent.
• Color choices should stay with a schema and be used to help convey the message or to provide intuitive clues, but it should not be distracting. Access makes this easy by offering several theme-related colors in the dropdown.
• Back Color can be useful to communicate purpose, status, or importance of a field, section, tab, or other object.
• Can Grow and Can Shrink are very helpful properties for text that varies in length. Using Can Grow will allow the text field to expand down the page or form.
• Hide Duplicates can improve readability and clarity by eliminating the repetitious display of data.
• Display When is a convenient way to show a control only on screen or in print. This technique can be ideal for obtaining and printing user input without storing it, such as for a one-time use to print a name tag.
• Border Style and other related properties can be used effectively to draw attention to textboxes and labels, whether it is to make them transparent, bold, or to change based on conditional formatting.

Key Properties of Report include:

• All of the Data tab properties can be effectively leveraged.
• Caption should be used to give the report a user friendly name.
• Border Style can be used to control user actions, such as using a dialog style to require a user response.
• Page Header typically displays the headings and other information one time per page.
• Page Footer should provide a consistent display of page number, print/data date, report identifier, and other such items.
• On No Data Event should be used to provide a polite message for the user to replace the system message.
• Pop-up and Modal should be used judiciously to help guide or control user actions.
• Cycle can be used to specify what records a user can/will see and is particularly useful in conjunction with on-screen viewing and interactive reports.

Header and Footer Properties (for various sections) include:

• Name should be changed and follow your established naming conventions so that you can easily identify and work with them in code and macros.
• Visible can be used to show or hide the header based on specific criteria. It can be used in conjunction with showing or hiding detail sections to quickly use one report and provide either a summary or detail display.
• {Group} Keep Together can be used to avoid displaying orphan records or headings on a page. You can quickly add a professional touch just by controlling the way data is split across page breaks and by starting major groups on a new page.
• Force New Page can be used to insert a page before or after a section. By choosing Before Section, you can start each heading on a new page. Used judiciously, this provides an excellent mechanism to organize and separate data to meet specific needs.

Layout View

It can save time to start with a wizard or report designer, but you'll almost always need to move some controls and customize the features. When you are moving controls, Layout View will quickly become either a help or a hindrance. It can be a handy tool for keeping controls neatly arranged as you work with several in a group, and it allows you to see the actual data in the controls. But it can quickly become a thorn if you are trying to move just one of the controls in a group.

The key is to recognize the benefits of using Layout View and learn to use the layout features to your advantage. The two layouts are Tabular or Stacked. So, when you want the controls neatly arranged and aligned in either pattern, you can select the controls and apply the layout. Conversely, if the controls are already in a layout grid, you can select the controls that you want to work with independently and remove the layout just from them. You can use the Ribbon control, but you can also use the right-click menu tool. Layout is just below the Insert and Merge options, and has a flyout that allows you to select Tabular, Stacked, or Remove Layout, as shown in Figure 18-2.

FIGURE 18-2: Right-click layout options

Calculated Fields

Before starting on the examples, there is a data type that is worth mentioning as it can be particularly beneficial when creating various types of reports. It is the Calculated Field data type, and it can be used with both text and numeric values. You will see it momentarily as the Display Name in the examples for creating badges and printing personalized conference session schedules.

The Calculated Field data type may seem somewhat of an exception to the rules about not storing calculated values or repeating data in tables. To reiterate, it is a built-in exception to the rule — which is not to say that just because it is built-in it is good. But, in this case, it does seem to be an exception worth keeping and using.

To demonstrate this, consider a common scenario that most developers can relate to: storing and working with names. You frequently need fields for first name, last name, and preferred name. But just because a table has all three fields, doesn't mean that a record will have data in each field.

On the name tag, you likely want to show a person's preferred name with their last name, but if the preferred name is blank, it needs to use the first name with the last name. But if neither the preferred or first name is available, it needs to provide the last name. That sounds straightforward. However, you also need to consider the space before and after the names. Meaning, if there is a first name but no last name, don't include the extra space that would otherwise follow the first name. Wow, your eyes may spin as you work to envision the IIf statement needed to implement that logic. Plus, if you do this on a report or query, you'd have to write it for every report that uses that name combination.

That's where the beauty of the calculated field starts to shine. You can incorporate that type of logic at the table level and then use the calculated field in forms and reports. When any of the underlying values are updated, the calculated field will automatically be updated. Figure 18-3 shows the calculated field, DisplayName, along with the expression.

FIGURE 18-3: Calculated field and expression builder

The Expression Builder will help you create the expression by providing the prompts and syntax framework based on the functions and fields that you select. This example uses Is Null to display the First Name only if there is no Preferred Name. Then it uses the plus sign (+) instead of the ampersand (&) to add a space after the name to separate it from the last name. If there is neither a first or preferred name, there won't be a space (see note about propagation of nulls). However, if it was left at that with no last name, it would still have a space after the first name. This could throw off the spacing in letters, greeting lines, or other such items. That is why Trim is added to the expression — it will remove all leading and trailing spaces from the final results:

Trim((IIf([PreferredName] Is Null,[NameFirst],[PreferredName])+" ")&[NameLast])

Null + "Teresa"      = Null
Null & "Teresa"  = "Teresa"

These are just a few of the fundamentals for creating and working with reports. The following examples will help solidify the concepts and open the door to more variations and possibilities.

Creating and Customizing Reports

There are several ways to start the process of creating a report. Among the easiest is to select the data source, typically a query or table, and then use the tools provided on the Create Ribbon. Report, Report Wizard, and Labels will start with a data source that you specify. The Report Design and Blank Report options essentially start with an empty sheet, except that Report Design has the grid and the page header and footer sections.

Regardless of how you get started, it is a good practice to save the new object immediately after creating it — before adding fields or making significant customizations. And, of course, you should follow good naming conventions — not just for the report itself, but for the controls within the reports. Most often, you will need to make at least a few modifications, such as changing the title and field captions, adjusting the placement and sizes of fields, and inserting a logo and image controls with custom graphics.

Using the Report Wizard

If you are building a report that requires a subreport, the Report Wizard can be an excellent place to start. This wizard allows you to select fields from multiple record sources, both tables and queries. If you select record sources that do not have an established relationship, the wizard will prompt you to create the relationship or select a different record source.

If you choose to create the relationship, it will open the relationship window for you. You can then add the appropriate tables and establish the join properties. Figure 18-4 shows the message and subsequent table relationships that will support creating the subreport to display an individual's conference sessions in an upcoming example.

FIGURE 18-4: Report Wizard message and the relationships window

Using the Label Wizard

To quickly review the basic steps and some good practices, you can start by creating a report to print event badges. This will use the built-in features for labels to get a preset size. You will also customize the look by formatting the company name, using the person's preferred name if it is provided, and formatting the fields to emphasize the person's name and company. You might also include conditional formatting, such as designating a color based on the person's role. You could also include a company or event logo, a barcode, or other event specific fields. You can use a similar approach for envelopes, address labels, and a myriad of other purposes.

Start by selecting query qryEventPerson as the record source. Then on the Create Ribbon, go to the Reports tab and click the Label button. This opens the Label Wizard. To quickly create a name badge that will fit in most badge holders, you might choose Avery 4168, continuous feed, to create name badges that are 2 1/6 inches tall by 4 inches wide. After clicking Next, you can customize the font. This example uses Calibri, size 10, Medium weight in the default black color. After clicking Next, you will begin selecting the fields to appear on the badge.

One of the handy features of the label wizard is the ability to move up and down the badge as you select fields and put them on the line that you want. This is just the initial layout and you will have the opportunity to rearrange fields, add formatting, and apply more complex criteria shortly. For the purpose of this demonstration, you can start adding fields by clicking at the top of the Prototype label window, which will create a gray area to indicate where your field selections will be placed.

Select the DisplayName for the first line, then click in the area for the second line and select the field Title. On the third line, select Company. Leave the next two lines blank, and select EventOrganization on the sixth line. On the next line, first select Role and then use the space bar to move the cursor to about the middle of the badge and then select EventDate.

At this point, the prototype will be similar to the image on the left in Figure 18-5. When you see it later in Design View it will be similar to the image on the right.

FIGURE 18-5: Label wizard and event badge prototype

After clicking, Next, you can select the field(s) to sort by, such as LastName and then FirstName. Notice that you can sort by fields that are included even if they do not appear on the badge. In this case, you may want to print badges in alphabetical order by last name, so that it is easy to include them with other conference material that is compiled by last name. Regardless of the print order, the badge will show the DisplayName because that uses the preferred name with the last name.

Next, you are prompted to save the report — this example is named rptEventBadge. You can then look at the report in Design View, (shown in Figure 18-5) and as you review the controls you should rename each TextBox to follow good naming standards, such as txtDisplayName. The practice of properly naming controls is important when you use code and macros to manipulate the controls or their content.

You'll also notice that the wizard does not allow you to use IIf statements to customize the name or to wrap text to another line. You will need to make those adjustments later. However, if you have a long line of text or otherwise exceed the width or height of the label, you will receive a nice little reminder about the text wrapping — by way of a messagebox that pops up to indicate that there may be blank pages. This can often be the result of putting multiple fields on one line or of a header or footer that was magically added. Fortunately, these types of issues are easily fixed in design view.

This quick demonstration gives you a good place to start on creating event badges, and it offers several opportunities to mention tips and features. That is a nice segue into customizing the reports, in this case badges, to your needs.

SubReports

Subreports are merely Access reports (or forms) that are embedded in another report. The subreport uses the properties Link Master Fields and Link Child Fields to link the master report and the child (subreport), as shown in Figure 18-6, which includes both the Design View and Property Sheet of the subreport.

You'll also see that the Source Object for the subreport is named srptPersonSideSession, but the container itself is named sRptSideSession. The container, like the report itself, has its own properties. You can change the Source Object to change what is displayed within the subreport. This is essentially the same look and functionality that you've used with subforms and tab controls. You can use subreports to display details, show charts, or to include a variety of other objects.

You can have multiple subreports on one report. Typically they are displayed sequentially down the page, even with a subreport within a subreport. But you can also have two subreports that are displayed side by side. This could be an effective way to display a chart on the left and the data in a grid on the right. You would also need to work with the properties to allow them to shrink or grow as needed.

FIGURE 18-6: Subreport links master and child fields

In a completely different scenario, you might include an unbound subreport so that the information does not change based on the content of other reports. If you think about conferences, there are typically plenary sessions as well as concurrent sessions that people select. To allow attendees to print their schedule for the day, they might have a printout that includes their name and company at the top along with the information about plenary sessions. Then the body of the schedule would be split into two subreports to show information about the side sessions.

On the left the report would show the schedule of the attendee's side sessions — this would use a master-child link. On the right you could use an unbound report to display a diagram showing the location of the conference rooms. Figure 18-7 shows the subreport with the meeting room diagram along with the Property Sheet to illustrate that this does not have a master-child relationship between the main report and the subreport containing rptRoomDiagram. You can again see that the container has a distinct name, sRptDiagram.

FIGURE 18-7: Using an unbound subreport

Finally, you can see the end result: a personalized schedule for the “Access 2013 Conference” complete with the attendee's name, general sessions, side session schedule, and room diagram, as shown in Figure 18-8. This will print one page per attendee. You can use the forms and reports in the sample database (2013Team.accdb) provided on the website to experiment with the settings and adopt them to use in your applications.

FIGURE 18-8: Personalized conference schedule with three subreports

Drill Down Reports

Taking the conference scenario one step further, you may want to help personalize the experience for both the attendees and the presenters. Many of the side sessions will have a lot of discussion, so it can be helpful for the presenters to recognize and know a little bit about the participants. With that in mind, you can easily provide them with an attendee list that is both a nicely formatted report for printing and a convenient tool for drilling down (looking up) information about the attendees.

You can do that by adding an event to the record or field on a report. When displayed in Report View (rather than Print Preview), the user can click (or double click) to open the related report or form. To demonstrate this, you can open the report rptAttendees and have it filtered to events. That will show the people registered for the Access 2013 Conference. If you click a person's name, it will open the About Form to show their picture, bio and notes, as shown in Figure 18-9. You can see how helpful this would be for event organizers, instructors, presenters, et al. This technique can be incorporated to benefit countless other situations. For example, in sales, users could look up products, or in a production scenario, users could look up parts.

FIGURE 18-9: Attendee list with drill down to the About form

It is very easy to add this feature to your applications. You can use a macro or event procedure to open a form or report. Typically, you will want to open the item to a specific record. In this case, the field PersonID was used to link the records so that the form opened to show only the record for the person selected. The following code is behind the Click event for the DisplayName field:

Private Sub DisplayName_Click()
DoCmd.OpenForm "frmAbout", acNormal, , "[PersonID] = " & Me.PersonID, _
  acFormReadOnly, acDialog
End Sub

The field that is used to link the detailed display to the report needs to be on both objects, but it does not have to be visible on either one. As you can see on this report, the PersonID field is included in the report Detail section, however it does not appear to the user because the Visible property is set to No. The form opens in dialog mode so that the user will need to close it in order to move elsewhere in the report or application. You will find more information about working with forms, including the images, in Chapter 17.

These examples have focused on leveraging built-in features and functionality to meet the needs of real-world scenarios. Of course, you will want to fine-tune the formatting and layout to fit your needs. And, as with most aspects of database design, there are several other approaches that you might use to achieve comparable results. Soon you will have even more options to choose from. The next set of examples will help you leverage criteria forms and record source options to give users more flexibility and control with their reports.

REPORT EXAMPLES

By now, you have significant experience with Access both from your own projects and from some of the techniques in this book. That gives you a strong foundation for creating more complex solutions. The next few examples are aimed at giving you some powerful report techniques that you can incorporate into your own solutions to give your users greater control over the granularity and layout of their reports.

Unless otherwise noted, examples in this section are based on the file ReportExamples.accdb found in the chapter download. This file uses the tables and data from Microsoft's Northwind sample database as the foundation; it is a freely distributed demo file that many developers are familiar with. In this case, it provides the tables with a sufficient amount of data to demonstrate report techniques and results. For your convenience, all of the Northwind objects are included in the chapter download file ReportExamplesNW.accdb.

To make it easy to distinguish the forms, queries, and reports that were added to support the examples in this chapter, their names have a prefix and start with an underscore ( _), such as _rptOrders. This also makes them easy to find since they sort to the top of an alphabetic list, and you can type “_” in the Search field of the Navigation pane to see all new objects.

Grouping Data

It is good to know how to use a variety of techniques for grouping and reporting data. That way, you can select and tailor the techniques to your current needs. With that in mind, the first example uses the Northwind application which includes a form, Sales Reports Dialog, found in the chapter download file ReportExamplesNW.accdb to provide users with a grouping dialog, as shown in Figure 18-10. Although this allows the user to select the way they want the data displayed in the reports, it requires a separate report for each selection option. Creating that many reports can be quite time-consuming and costly to develop and test, and it can become a maintenance nightmare.

FIGURE 18-10: Criteria form for user determined grouping

However, this dialog invokes different reports based on the selected option, and there is no real grouping level. Instead, it uses the WhereCondition to filter the data, as shown in the following code:

' Determine report filtering
If Nz(Me.lstReportFilter) <> "" Then
    strReportFilter = "([SalesGroupingField] = """ & Me.lstReportFilter & """)"
End If

' Determine reporting time frame
Select Case Me.lstSalesPeriod
Case ByYear
    strReportName = "Yearly Sales Report"
Case ByQuarter
    strReportName = "Quarterly Sales Report"
Case ByMonth
    strReportName = "Monthly Sales Report"
End Select

DoCmd.OpenReport strReportName, ReportView, , strReportFilter, acWindowNormal

When you have a finite number of variables, this type of approach can let you quickly create the reports and provide a solution for your users. However, it becomes very labor and maintenance intensive if the needs or complexities grow or change. This brings you to the next series of examples. As you go through the following examples, you will look at an assortment of report options and learn how to create a single report that will group and display data in a variety of ways based on the criteria selected by the users.

Reports with Simple Criteria

You have probably seen Access applications with many reports. Some of them are very similar. There may be several for a date range, such as:

• SalesReport_2011
• SalesReport_2012
• SalesReport_Q1_2013

Each selection is bound to its own query. So for the three reports listed, you might use the following three queries, respectively:

• qrySalesReport_2011
• qrySalesReport_2012
• qrySalesReport_Q1_2013

You already know this is not a good idea from a maintenance perspective. Copying a query just to change the date range is very wasteful, and invariably over time the queries and reports will be just a little bit different. For example, one year is using only Active customers, while the other years are using all customers, causing confusion for the users of the reports.

You also already know how to fix this problem. You can create a single report with a simple date criteria form, such as the one as shown in Figure 18-11.

FIGURE 18-11: Simple date criteria form

The two text boxes have the Format property set to Short Date and the Show Date Picker property set to For Dates, so calendar buttons automatically appear when the fields have focus. In the preview's Click event you write:

DoCmd.OpenReport "_rptOrders", acViewPreview, _
  WhereCondition:= "[Order Date] between #" & Me.txtStartDate & _
    "# And #" & Me.txtEndDate & "#"

Reports with Simple Groupings

Another way reports can be similar is by the way they group the data. Even if you are only working with two types of groups, there can be an extensive list of variations based on the criteria used in each group and the way the data needs to be displayed within the groups. A few of the examples for reporting sales by rep and status include:

SalesReport_BySalesrep
SalesReport_ByStatus
SalesReport_BySalesRep_ByStatus
SalesReport_ByStatus_BySalesRep

Combine this with the date range reports you just worked with, and you can see that creating separate reports for each variation can quickly become unmanageable. This is clearly a time to create a single query, a single criteria form, and a single report. The upfront investment in writing some VBA will pay significant returns for the life of the project. In the next section, you will learn how to create this type of functionality.

One Flexible Report

If you need to provide multiple ways of displaying the same data, it is often more efficient to use an approach that builds flexibility into the process, than to use an ever increasing number of similar reports and queries. In this section, you will create a single query and report and learn how to use them to create a variety of report configurations.

The example uses a list of orders that can be grouped in different ways. You can easily apply the same techniques to an infinite number of scenarios, such as people in organizations, manufacturing processes, and more.

There is only a single query that simply selects all orders and the names for the various ID values, as well as the total amount per order. The criteria form shown in Figure 18-12 builds on the simple date criteria form used previously, and adds several options for filtering and grouping the data.

The Scope group allows the user to specify whether the report will show or hide the Report Detail section. By selecting Summary, the report will hide the details so only the group headers and footers are displayed, as shown in Figure 18-13.

Per the requirements for the Orders report, it should always be grouped by Order Date, and optionally by another grouping level like Salesrep, Shipper, or Status. Even with that, users may want to see the data grouped by the first criterion and then by the second, or vice-versa, depending on how they intend to use the data. Users control the order by selecting from the Group options. With these few controls, you have given the users the ability to create 48 distinct reports and filter them for any given date range. Not bad for about 50 lines of code.

FIGURE 18-12: Flexible grouping criteria form

FIGURE 18-13: Summary report shows only group headers and footers

The next examples show the same grouping along with the data. Figure 18-14 shows sales grouped by Date and Salesrep and then Figure 18-15 shows the data by SalesRep and by Date. You should also note that the report header is used to display the date range that was selected by the user. This technique of showing the criteria in the report helps people have a better understanding of exactly what they are reviewing so they can make informed decisions.

FIGURE 18-14: Orders grouped by date then sales rep

FIGURE 18-15: Orders grouped by sales rep then by date

Before delving into the explanation of how to create the report, it might be helpful to take a look at it in design view, as shown in Figure 18-16.

You can create the report by using the report wizard and selecting the query _qryRptOrders as the record source. You will need to add two arbitrary grouping levels which will become level 0 and 1. In this example we used ShipCompany and OrderDate. And, of course, you will need to apply appropriate formatting to tailor the look to your project.

In this example, the grouping labels and text fields have control names that are easy to use programmatically, such as lblGroupLevel0 and lblGroupLevel1. It also uses background color to indicate the outer (purple) and the inner (green) grouping level. These are, of course, among the items that you will want to adjust according to your project needs.

It only takes one line of code to launch the report: DoCmd.OpenReport.

The heavy lifting occurs in the Report_Open event. This is where the report retrieves the criteria from the criteria form and formats the report accordingly. The following four tasks are accomplished based on the criteria:

FIGURE 18-16: _rptOrders_Grouping in design view

1. Group by Date and another field, or vice versa.
2. Set the grouping level captions in the page header.
3. Bind the level0 and level1 header controls to the fields they are grouped by.
4. Bind the grouping levels to the fields based on the grouping.

You can apply this technique in numerous scenarios to quickly give users significant flexibility and control for both the selection and layout, such as reporting on production by plant, division and model, or perhaps it is restaurants by region, state or city.

To implement Step 1, you use the variables nDateLevel and nGroupLevel. Depending on which Group option is chosen, they can be level 0 and 1, or if the other option is chosen, level 1 and 0. The following line of code is used to bind the grouping level to the correct field:

Me.GroupLevel(nDateLevel).ControlSource = "Order Date"

To implement Step 2, you can take advantage of the specifically designed naming convention for labels by using the following line of code to set Me.lblGroupLevel0 or Me.lblGroupLevel1 to Month. The Date options “by Quarter” and “by Year” are handled very similarly:

Me("lblGroupLevel" & nDateLevel).Caption = "Month"

Step 3 is implemented in a similar way to Step 2, only this time by setting the text boxes' ControlSource property, as shown in the following code:

Me("txtGroupLevel" & nDateLevel).ControlSource = _
  "=Format$([Order Date],'mmmm yyyy')"

If you are using the second grouping level, and the user chose “by Salesrep” it would create this:

Me("txtGroupLevel" & nGroupLevel).ControlSource = "=[Employee Name]"

If the user chose “by Shipper” this would be created:

Me("txtGroupLevel" & nGroupLevel).ControlSource = "=[ShipCompany]"

Finally, if the user chose “by Status” it would create this:

Me("txtGroupLevel" & nGroupLevel).ControlSource = "=[Status Name]"

The last Step, #4, sets the ControlSource of the grouping level itself. This actually includes several actions to accommodate the options that are covered in the example. To follow the example, you can start with this code:

Me.GroupLevel(nDateLevel).ControlSource = "Order Date"

Then, if the user chose “by Salesrep” it would create this:

Me.GroupLevel(nGroupLevel).ControlSource = "Employee Name"

The Group options “by Shipper” and “by Status” are handled very similarly.

At this point, the Order Date needs to be set to group by Month, Quarter, or Year. Access has built-in support for Date grouping by Month, Quarter, and Year. So if the user chose “by Month” it would create this:

Me.GroupLevel(nDateLevel).GroupOn = 4    ' 4=Mon

If the user chose “by Quarter” this would be created:

Me.GroupLevel(nDateLevel).GroupOn = 3    ' 3=Qtr

Finally, if the user chose “by Year” it would create this:

Me.GroupLevel(nDateLevel).GroupOn = 2    ' 2=Year

Finally, you set the Order Date filter for the report equal to the values in the Start Date and End Date. That completes the process for creating the flexible report.

An alternative approach is to specify the date range in the WhereCondition property of DoCmd.OpenReport, as you did for the simple report with date range criteria. However, since the flexible selection criteria required additional code, you might as well write it all in the Report_Open event. It is often preferable to keep your code in one place so that it is easier to maintain and for someone else to work on the project.

Calling the Criteria Form from the Report

In many scenarios, there are benefits in calling the criteria form from the report, instead of using the criteria form to select and open a report, such as:

• It makes the report more self-contained.
• It is a more object-oriented approach.
• The report can't be run without criteria being set.

The Northwind sample app, which is the foundation for the ReportExamples.accdb, already shows how to implement this approach. So you can investigate and experiment with the reports in the chapter database. If you look at the Monthly Sales Report, you'll see the following code in the Report_Open event:

   If IsNull(TempVars![Display]) Or IsNull(TempVars![Year]) Or
IsNull(TempVars![Month]) Or IsNull(TempVars![Group By]) Then
       DoCmd.OpenForm "Sales Reports Dialog"
       Cancel = True
       Exit Sub
   End If

This code tests several elements of the TempVars collection. These elements are only set by getting values from the criteria form. If any of elements are null, then the form has not been completed, so the criteria form is opened and the report opening is canceled by setting Cancel equal to True.

Reports that Compare Values

Many reports need to compare actual values with expected or estimated values. For example, you may want to compare actual factory production figures with their goals or projections, or actual employee production with their quota. This technique can address needs across a wide spectrum of industries and departments, from auditing to sales, and from appointments booked to those kept on time. But, as with all reports, if you want to report the statistics you need to ensure that you have collected and stored the underlying data.

In this example each salesperson has a monthly quota. As in many real-world scenarios, tracking the quota adds a new requirement to the database. So the original specifications do not incorporate all of the requisite data in the table and fields. Therefore, you need to add a MonthlySalesQuota field to the Employees table in order to store this data. As all salespersons have a quota, you make it a required field.

The next step is to add the new field to the existing _qryOrders query. While this query is selected in the Navigation Pane, you select the Report Wizard from the ribbon and create a report with grouping levels by Employee Name and Order Date. For this example, save the report with the name _rptOrders_SalesQuota.

To compare the Actual value (the monthly sales) to the Expected value (the sales quota), you use Conditional Formatting. If Actual >= Expected, the Monthly Sales field will be green, otherwise it will be red. To apply the conditional formatting you select the Monthly Sales field in the Order Date Footer, and select Conditional Formatting from the Format tab on the ribbon. You enter two rules — one for green and one for red — comparing the value of this field to the MonthlySalesQuota field, as shown in Figure 18-17.

FIGURE 18-17: Applying conditional formatting

As expected, the report now indicates for each month if the salesperson met the quota, as shown in Figure 18-18.

FIGURE 18-18: _rptOrders_SalesQuota with conditional formatting

This report also shows another often-used technique: cumulative amounts. You might think this would require a complicated SQL query, but fortunately the Access report engine is doing all the work. You simply add a second Order Total field and set the Running Sum property to Over Group. Access will calculate the cumulative values and restart the count for the next month. If you like, you can add conditional formatting to this field comparing the value to the monthly quota. You will see that initially the field is red, to indicate that it has not met the quota. Once the cumulative value exceeds the quota, it will be green.

Another very interesting use of Running Sum is to provide line numbering. If the new requirement is to number each order line starting at 1 each month, you can add a text field and set its Control Source to “=1” and set its Running Sum to Over Group. You can then view the results and adjust the layout to suit your preferences, as shown in Figure 18-19. This is all done with features in the report design, so the query remains unchanged.

FIGURE 18-19: _rptOrders_SalesQuota

PROFESSIONAL POLISH

When you write reports, you are mostly focused on presenting accurate data. However, a pleasing and consistent style is important as well. You can setup a Normal template with the basics. This section covers some of the other aspects that you can leverage to give your reports a professional polish.

Report Criteria

Reports are basically intended to be standalone documents, often saved or printed and viewed separately from the database. At that point, the criteria form is a thing of the past, so you — and more importantly the people viewing the report — will not have the criteria form to indicate the selections used for a given report. That's why in many cases it is remarkably helpful to repeat the criteria in the report header.

When you make it easy for readers to see which criteria were used to produce the report, you should be as complete as possible, so the report is not ambiguous. For example, you may have a criteria form that prompts for a date range, so the criteria field should display similar information, as shown in Figure 18-20. As you can see, this report shows the selected beginning and ending dates in the report header.

FIGURE 18-20: Including report criteria in the report header

In this case, the expression in the criteria field includes the subject (order date) as well as the start and end dates. You can apply this technique of providing informative headings and labels to other areas of your reports — and forms, too, for that matter. The following expression was used to create the information line in the header of this report. In the declaration section at the top of the report, include this code:

Public m_strCriteria As String

In Form_Open event, include the following code, using m to indicate that this is a public variable and used across multiple procedures:

m_strCriteria = "Order Dates between " & frm.txtStartDate & _
  " and " & frm.txtEndDate

In Form_Load event, include the following code:

Me.txtCriteria = m_strCriteria

The reason you want to set m_strCriteria in Form_Open is because that's where all formatting takes place. It is best practice to keep all of the code together in one place.

You may also be wondering why to use Form_Load instead of Form_Open. The reason you use Form_Load to set the text is because Form_Open is too early in the creation of the report and the field value cannot be set at that time.

However, you may have other criteria to consider. For example, if your query also restricts the orders to only those for active customers, or any other relevant criteria, you could include those in the report header as well.

You can further customize the string to provide proper syntax. For example, if your date criteria form allows the start date or end date to be empty indicating “all dates,” then you should write the criteria text accordingly. You can enhance the professional presentation of the report by using proper grammar and matching the wording for singular and plural, and other such items.

The following code uses the order dates. You can use this example and modify the code to work with your scenarios:

If IsNull(Forms!frmCriteria!txtStartDate) _
  And Not IsNull(Forms!frmCriteria!txtEndDate) Then
    Me.txtCriteria = "Order Dates up to " & frm.txtEndDate
ElseIf Not IsNull(Forms!frmCriteria!txtStartDate) _
  And IsNull(Forms!frmCriteria!txtEndDate) Then
    Me.txtCriteria = "Order Dates from " & frm.txtStartDate
Else
  'Both dates exist
  Me.txtCriteria = "Order Dates between " & frm.txtStartDate & _
    " and " & frm.txtEndDate
End If

There are many other factors that might improve the delivery of the information being reported if they were pointed out to the reader. For example, if it is not obvious and relevant to the type of report you are creating, you should also state how the report is sorted.

These are just a few of the factors that you can leverage to create a professional report tailored to the specific criteria that it is displaying. In addition to the data, there may be other corporate, legal, or routine matters that need to be conveyed with the reports. These can be addressed in the report header, footer, or both.

Confidentiality Statement

Your reports often contain sensitive information, and many organizations have a confidentiality statement that should appear on each report. You could use a label for that in the footer, but what if the statement changes? Then you have to fix all your reports.

It's much better to make this a data-driven element. One solution is to have a SystemSettings table with a ConfidentialityStatement field. You can then use a textbox in the footer of each report and set the ControlSource to:

= DLookup("ConfidentialityStatement", "SystemSettings")

Be sure to set the CanGrow property of this field to True so that the entire statement will be displayed, extending down multiple lines if necessary.

Page Numbers and Report Date

The Access report wizard adds page numbers and the report date to all reports. If you design a report from scratch, you should always add these. Page numbers should appear in the format “Page X of Y” so the user knows not just the current page number, but also the total number of pages.

Report Name

In some organizations the report name is a required field in the footer of any report. Often the name will be assigned by the department requesting the report. Sometimes it is followed by a sequential number for each revision, which you will have to maintain.

If you simply want to state the name of the Access report, you can use a textbox and set the ControlSource to =Me.Name. Of course, it would be more professional to use a nicely formatted name, such as you might store in a table so that it can easily be used for the report title, footer, and other places. You've seen this technique mentioned and used in both this chapter and Chapter 17.

Using Work Tables

Creating a temporary or work table can provide benefits to performance and a significant relief to development challenges in cases that involve a series of calculations or work with very large amounts of data. It particularly makes sense when you are working with historic data rather than data that is subject to ongoing changes.

There are differing schools of thought and approaches to working with temporary tables. Some developers prefer to create the tables when and as needed, and others prefer to have a permanent structure and then delete the records just before or after using the table. The benefit of keeping the records until the next use is that you can rerun the report or research anomalies. However, deleting the records and compacting the file after use takes a smaller footprint. Either way, it is preferable to use linked rather than local work tables to avoid undue database bloat.

And that brings this to a good point to wrap up the polish and share one final bonus example, the PDF tool.

FILLING OUT PDF FORMS USING ACCESS

Portable Document Format (PDF) files seem to be nearly ubiquitous. More and more organizations use PDF forms for requisitions, routing slips, tax forms, invoices, receipts, registration, and legal forms. They are easily distributed online and via e-mail, and they provide a structured format for collecting and presenting data. Given that PDF files are such an effective way to request data, it is important to have an efficient way to fill them out, which you'll soon learn how to automate.

As PDF files have become more prevalent, their specifications have also changed. Although there are a variety of formats available, developers recognize Adobe's PDF specification as an industry standard for producing and distributing official documents in electronic format. So that is the format used for this example.

Fortunately, as techniques for completing PDF files continue to evolve, they have become easier to automate and incorporate into Access solutions. Whether the PDF is produced internally or provided by an outside entity, you can use an Access data file as the source data for filling out the forms.

A PDF file is essentially a report as much as it is a form. So the focus here is to provide some approaches that you can use to print a PDF file. The methods range from emulating the original form to actually inserting data into specific fields. The following techniques are just a few of the options that are currently available. You can select and modify the techniques to correspond with the specifics of your scenario.

You will find a working example of this in the database named PDFDemo.accdb, which is available on the book's website. As an extra bonus, you'll see that an .mdb version is also provided so that you can easily incorporate the techniques into legacy solutions.

Using Reports

The traditional method of filling out a PDF form using Access is by duplicating the PDF form's structure and format in an Access Report. In some scenarios, this can include shading, numerous font sizes, and even inserting images of text that use special fonts or sizes that are not available due to network or printer limitations. As you can imagine, replicating a PDF form in this manner can be very tedious.

It can also become problematic when you have to modify the report in response to changes in the form, or require multiple versions to correspond to specific date periods, such as with tax forms. In many cases, it might be better to start fresh and create a new report from scratch. However, a notable benefit of this approach is it produces a high quality replica of the PDF form.

One other method that could save time when using a report as a PDF form is to scan the original PDF form and then use the scanned image as a background for the report. In this case, all the developer has to add to the report are the data fields from the Access table. This is not as simple as it might sound. This technique requires that each textbox control be precisely aligned with the background image on the report. In addition, the final outcome of the report is heavily dependent upon the quality of the image as well as the capabilities and settings of the printer itself.

These techniques work, but fortunately for developers, Adobe provides tools that can spare developers all the pain of duplicating a PDF form in an Access report.

Using an XFDF file

XML Forms Data Format (XFDF) is an XML-based specification for working directly with the data used in PDF forms. Although you don't need Adobe Acrobat to create XFDF files, you do need a PDF reader application to view the final result.

The Essentials

The XFDF file is an XML-based document, so it requires both a header and a body section that follow strict formatting rules. Specific tags have to be used to describe the path to the PDF form being populated with the data, to specify the names of the fields in the PDF form, and to designate the values of the data coming from your Access database.

The following is an example of an XFDF file format. In this example, MyPDF.pdf is the name of the PDF form to be filled out and FirstName and LastName are the names of the fields in the PDF form:

<?xml version="1.0" encoding="UTF-8"?>
<xfdf xmlns=http://ns.adobe.com/xfdf/ xml:space="preserve">
<f href="MyPDF.pdf" />
<fields>
    <field name="FirstName">
        <value>DB</value>
    </field>
    <field name="LastName">
        <value>Guy</value>
    </field>
</fields>
</xfdf>

It is important to remember that XFDF is an XML-based file, so certain characters have special meaning when the file is parsed by the PDF reader. If your data contains any of these characters, you must convert them into XML entity references. The following table shows the five predefined entity references in XML.

Sample Code

The following example shows how you could create an XFDF file that the PDF reader will use to fill out the PDF form. You can also find this code in the download file for this chapter.

Option Compare Database
Option Explicit

Private Sub cmdPDF_Click()
'[email protected]
'www.accessmvp.com/thedbguy
'04/10/2012 - Original version (v1)
'06/13/2012 - Update (v1.1)
'04/14/2013 - Update (vW1)

' - XML parsing will fail if the field contains either an ampersand (&)
'   or a less than symbol (<)
' - One approach is to replace "&" with "&amp;" and "<" with "&lt;"

'This demo creates a XFDF file to merge with a fillable PDF form.
'Tthis method avoids the need to use an Acrobat DLL to manipulate the PDF file.
'This method relies on the capabilities of the installed PDF reader.

'Declare the PDF file to be filled and assume it's in the
'same directory as the database.
Const strPDF As String = "theDBguy.pdf"

'Declare the XFDF file to use
Const strXFDF As String = "theDBguy.xfdf"

Dim strPath As String
Dim intFile As Integer

strPath = CurrentProject.Path
intFile = FreeFile

'Create XFDF file
Open strPath & "" & strXFDF For Output As #intFile

Print #intFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #intFile, "<xfdf xmlns=""http://ns.adobe.com/xfdf/"" xml:space=""preserve"">"
Print #intFile, "<f href=""" & strPDF & """/>"
Print #intFile, "<fields>"
Print #intFile, "<field name=""fname"">"
Print #intFile, "<value>" & Me.txtFName & "</value>"
Print #intFile, "</field>"
Print #intFile, "<field name=""lname"">"
Print #intFile, "<value>" & Me.txtLName & "</value>"
Print #intFile, "</field>"
Print #intFile, "<field name=""notes"">"
Print #intFile, "<value>" & Replace(Replace(Me.txtNotes,_
   "&", "&amp;"), "<", "&lt;") & "</value>"
Print #intFile, "</field>"
Print #intFile, "</fields>"

Print #intFile, "</xfdf>"

Close #intFile

'Open the PDF file
ShellEx strPath & "" & strXFDF

End Sub

SUMMARY

Although reports are primarily used to create customized layouts for printing, they can also be used for on-screen viewing. One of the greatest benefits in that area is the opportunity to leverage interactive features — such as allowing users to do their own sorting and filtering. Combining this with the increased mobility and portability of devices, you can use reports to preview, select, and print specific items, such as the example with printing event badges, personalized schedules, or PDF files.

Access 2013, as well as previous versions of Access, allows developers with skills ranging from novice to expert to quickly create customized professional reports. It is arguably the most versatile, robust, and easy-to-use reporting tool available, particularly if you need to combine data from multiple sources.

In Chapter 17 and this chapter you worked with forms and reports based on data stored in Access tables. You now have several new database files to contain the forms, reports, and queries that you've been working with, along with several other examples that demonstrate techniques that will allow you to quickly add features and style to improve the user interface. You can incorporate these into your projects to give your Access solutions a more professional presentation.

In the next chapter, you will learn how to extend the reach of Access by integrating with and automating other Office programs.