17

Creating Intuitive Forms

WHAT'S IN THIS CHAPTER?

• Creating intuitive and professional forms
• Built-in functionality you can leverage
• Managing images, attachments and MVFs
• Report Runner tool

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The code and file downloads for this chapter are found at www.wiley.com/go/proaccess2013prog.com on the Download Code tab. The code is in the Chapter 17 download folder in the files named:

• 850832_ch17_Team_CodeSnippets.txt
• 850832_ch17_ReportRunner_CodeSnippets.txt
• 850832_ch17_MVF_CodeSnippets.txt

The files include the code snippets from their respective database examples, sequentially from throughout this chapter. The download folder also contains the associated databases 2013Team.accdb and MVFDemo.accdb and their support files so that you can easily follow the discussion and expand on the examples.

Forms will make or break your user's experience with the database and it's important they are not only functional but that they also look great. You can have the best program in the world but if it looks like it was written by an amateur, that's the level of respect that it will get. Although an in-depth discussion on UI design (user interface) is beyond the scope of this chapter, it is a critical aspect of both the functionality and the acceptance of a database solution.

Whether you are a UI expert or expanding your focus by venturing into designing forms, you will find some new ways to incorporate, leverage, and apply the features that Access provides for creating powerful forms. As you design the forms, you need to focus on people who will be using them and ensure that the information, layout, and flow through the form(s) will be as intuitive and efficient as possible for each user or user group. Consider the user background, the work environment, and the flow of the data. You also need to minimize the opportunity for errors and to provide whatever verifications are necessary to avoid reporting data incorrectly.

From a functional standpoint, forms are the operation center of the database. They provide the path for entering, retrieving, editing, and reporting data. The success or failure of your solution can be contingent on how quickly and easily users adapt to using the forms that you create. So, the focus of this chapter is to demonstrate some techniques that you can use to create powerful, intuitive forms. And, true to real-world form (no pun intended), the effort has been invested in providing clear examples to demonstrate functionality rather than on customizing the look and flow of the forms. As you incorporate the objects into your applications, you can apply the theme and other design properties as appropriate.

The examples are based on real-world scenarios to show you how to design and incorporate the techniques and tools into your solutions. Using the scenario of tracking people at a conference, you will see how to work with images, create attendee lists, print badges, and look up speaker profiles. You will also see how to leverage Datasheet view to create powerful search and filter forms, and how to organize data so that it is easy for users to navigate and manage. The examples and techniques can easily be adapted to support a myriad of other industries and needs, from parts or product lists, to restaurant menus or construction projects.

You'll also work with a tool for designing reports. This approach allows users to customize their reports by not only selecting the report and record source, but also to select which fields should be filtered and to specify the filter criteria. In addition to creating the forms, the discussion will explain the tables that are required and the programming involved. As you go through the process, you will learn about the associated objects, events, and properties. And, you will find out how you can incorporate this amazing tool in your Access solutions.

CREATING INTUITIVE FORMS

Forms simplify the navigation through the database by guiding users through entering, validating, and retrieving data. Without a properly designed user interface (UI), most users will be quickly overwhelmed, particularly if they are required to use the Navigation Pane to find what they need in order to add data, run queries, and view reports. It is much better to guide the user by providing navigation through menus, the ribbon, the Navigation window, and controls and events on the forms and reports themselves. You will see a variety of techniques as you work with the examples and other forms and reports in the database files. By creating an intuitive UI, you can make it easy for users to quickly and efficiently find, enter, and report data. And you can also control when and how users can view and manipulate various parts of the data.

As in the past, you can choose from a variety of approaches for creating forms, ranging from using a blank form to deploying a wizard to provide a functional result that you can tailor to meet your needs. By working with real-world examples, the discussions will cover some of the fundamentals, suggest alternative approaches, and demonstrate how you can leverage the features and techniques to provide powerful tools for your users.

The ultimate goal is for you to provide robust, reliable, effective and intuitive solutions. One approach will not fit all, but by having a variety of options, you can combine them to match the scenario and environment. With that in mind, our discussion will start with the general and work through some commonly used controls and end up with a very complex solution. So, the first step is to review some tips shared by several Access MVPs.

Clean Layout

Appearances make lasting impressions, and this also applies to the look and actions of the forms and reports in your database. If forms are too busy, the user may get confused, make mistakes, get unduly frustrated, or become alienated from the program. Some of the key aspects of having a clean look include:

• Start and end with timed screens — Most people agree that an attractive, informative splash screen provides a professional introduction as a program opens. Similarly, you can have a closing screen that assures the user that all files are properly saved and closed.
• Color and font — You can keep the tones appropriate for the environment and consider the size of the screen, the work environment, and the activities involved. Most important, you can focus on users, and particularly their eyes. Use color and font, type, size, and weight to convey information. Unless the application is for entertainment purposes, bright colors and lots of action will be a distraction.

Use various types of forms and controls to organize data and provide it when needed. Find the right combination for the situation, such as combining cascading combo boxes with subforms and a tab or treeview control, or to expand on the functionality of Split Form and Datasheet views.

Guiding the User Through the Process

There are several features and properties that you can combine to guide a user through the process. As you are placing the textboxes and other controls on the forms, consider the user's hand movements as well as how and where they are getting the raw data. In some cases, you may need to minimize use of the mouse; at other times mouse clicks may be nearly all that is required.

Leverage the tab order in conjunction with show or hide, and enable controls based on the user, the current process, how the form was opened, and other criteria. At points where the user must be led in a certain direction, you can use a dialog form, or a pop-up style modal form to prevent any other processing until the form is closed, as explained in the upcoming section about these types of forms. You can expand on this by incorporating validation processes, events, and the Tag property to enable, hide, and otherwise change the appearance of a control. You'll read more about these techniques later in this chapter.

Showing and Verifying Data in a Timely Manner

It isn't enough to collect data; you need to ensure that users are able to efficiently enter, retrieve, and edit records. In many instances, it may not be clear which record the user really needs. For example, the user may be looking for Sam Jones but there are 10 records with that name. There are several ways to help the user make informed selections. Depending on the specific scenario, you might use an enhanced combo box, cascading combo boxes, a Split Form, or Datasheet view, or some combination of these and other controls. You will find several variations mentioned and demonstrated in the chapter.

Data entry can encompass a wide spectrum of tasks and environments, and it is incumbent upon you to create a solution that manages the processes as efficiently, effectively, and effortlessly as possible. That includes ensuring that the data is valid, complete, and not duplicated. Fortunately, there seems to be an endless variety of techniques to help both you and the user. This discussion will focus on what you can do at the form level. This complements and builds on techniques you learned in other chapters, such as working with data macros which is covered in Chapters 9 and 16.

There are several properties and resources you can leverage when creating your forms. Some of the key ways to validate or manage data include using Validation Rules and setting the Required property. You can also use the Tag property to designate required fields, stipulate criteria, and take specific actions. For example, you can use code with the Tag property to check for values and, if something is amiss, intervene before the record is updated to highlight the errant fields and put the cursor in the first field to be corrected.

There are seemingly endless combinations of techniques to guide the user through their tasks. You can also use prompts, default values, Smart Tags, control tips, conditional formatting, show/hide/enable controls, the tab order, and numerous other clues to help users efficiently and accurately work with the data. Several of these techniques are discussed and demonstrated in the examples for this chapter.

User-Friendly Messages and Tips

Courtesy is one of edicts of professionalism. The messages and instructions that the user sees should follow that guidance.

Let the user know where they are by using clear captions and labels on forms, reports, and controls. It's important to remember to change the control name as well; this makes it much easier to identify and work with the desired item using VBA or macros.

Trap error messages and provide your own words that are easy to understand — give the user the necessary information and instructions. This might include the form name, instructions to contact tech support (you), what needs to be fixed, which field is missing data, and so on. And remember, it is not polite to shout, so exclamation marks and bold should be used very judiciously.

On forms that may be complicated or unfamiliar to the user, you can include information labels that provide the appropriate background and instructions. There are more and more options for how to convey the information. You might include it on form itself, in tips, or in pop-ups, or via a video or PowerPoint demo that is opened from a command button.

Keep these types of ideas in mind as you read through the chapters. This will help you to build correlations between the examples and your projects and make it easier for you to modify and incorporate the techniques into your solutions.

LEVERAGING BUILT-IN FUNCTIONALITY

The form controls that are supplied with Access 2013 are robust and are relatively easy to implement. These controls are much more than textboxes and labels typically used to display, add, or edit data. There are formatting controls such as the Line and the Rectangle controls for drawing, the Image and the Unbound Object Frame to display pictures or objects, and Hyperlinks and Web Browser controls. You'll work with these in several of the examples in this chapter.

When designing forms, there are always multiple ways to implement the same functionality, having a rich toolbox of controls, and understanding what they do and how they work, makes it easier for a developer to design a form the way the customer, be it either a commercial client or an internal user within your organization, wants the data presented.

Textbox

The Textbox is by far the most prevalent control. It is so heavily used that you might think everyone has mastered all of its properties. But reality is far different. There are so many properties and ways to combine properties that most barely scratch the surface of the possibilities. Although textboxes are typically bound to a record source, you have probably also used them as unbound controls and perhaps changed the record source at runtime.

As you work through the examples in this chapter, you'll see all three approaches and much more. You will see how to use events and properties of forms and controls to not only customize the visual effects, but also provide guidance and direct user actions, such as using the Tag property to change the appearance of a control to help collect data in required fields.

In addition to the features demonstrated in the chapter and examples, there are countless controls and properties that you can find and explore. For example, you can use conditional formatting to change the background color of a control based on the data. This could draw attention to preferred customers or past due or overdrawn accounts, or could even indicate required fields that are empty. You can quickly create the conditional formatting rules using the features in the Control Formatting group on the ribbon's Format tab, which is available when a form is in Design view or Layout view.

If the textbox is bound to a Long Text (Memo) field, you can set the Text Format property to Rich Text to allow users to apply additional formatting directly to the text itself.

Label

Labels are usually static and used to identify the content or purpose of another control. When you add a control to a form, it typically includes an associated label that will follow the control wherever it is moved, and it will also be deleted if the associated control is deleted. An associated label can be deleted separately from the primary control and conversely, a new label can be associated with a control that does not already have an associated label.

When a label is not associated with a control, it will have a small green triangle in the upper-right corner label, indicating that it is an unassociated label. This indicator may seem like a nuisance, but sometimes such indicators are helpful warnings about significant issues that need to be addressed.

The captions and formatting of labels can also be set at runtime by changing the label's properties. Using this in conjunction with changing the control source of a textbox allows the user to identify what values are being displayed in the control. Although a label does not have any Data properties, it has a large selection of Format, Event, and other properties, most of which can be manipulated at runtime thru VBA. This means that you can use the label's colors, font weight, visibility and other features, to provide scenario-based guidance to the user. You can also use the label's Event properties to run code or macros. However, labels for Option Button and Check Box controls do not have events because their event is used to change the value of the control itself. You might be tempted to use an invisible label to initiate an event. And although an invisible control is not enabled, you can use a combination of property settings to make a label appear to be invisible, and the event will still fire, as illustrated in Figure 17-1.

With this technique, you can strategically locate areas that will trigger events without relying on the traditional command button. For example, you could put several labels on a logo or image so that different events are triggered based on where the user clicks. You can also use this technique to hide a control so that only select people will know how to open a set of forms or reports. Obviously, this is not intended to replace true security, but it is a relatively easy way to provide secret doorways to additional forms.

FIGURE 17-1: Click event for a transparent label

Command Buttons

Using a Command Button is the most common way to have a user initiate an action, be it to open a form, preview a report, exit a form, or exit the application. The On Click event is probably the most used event for this control. Most computer users are familiar with clicking objects to initiate tasks, so your focus should be to make your command buttons as intuitive as possible. That means you need to consider the look, the size, and the location, and you should be as consistent as possible.

You can use a combination of properties to help guide users through a form or process in the order that you want things to occur. For example, by setting a command button's Enabled property to False, it will be visible but grayed out and its events will not fire. Users will interpret this to indicate that some actions need to be completed before they can use the command button. Of course, you will want to provide guidance about what actions need to be completed. How you do that should be based on the scenario and users. Once the necessary actions are completed, you can use code or a macro to enable the command button.

You can also hide the command button by setting the Visible property to No. Hiding controls is often done to prevent users from seeing commands and/or implementing actions based on their permissions or because certain conditions have been met. For example, you can hide a command button until acceptable values have been entered into all of the required fields.

Some of the most common uses of command buttons are to open and close forms, save records, or go to a specified record or value. However, they can also include a series of processes that allow for different outcomes depending on the results. For example, the events behind a save record button could be used to confirm that all required fields have acceptable values, that it is not creating a duplicate record, and that any number of other requirements are satisfied before the record is actually saved.

Split Forms

Split Forms were introduced in Access version 2007, but they were not widely accepted right away. Many developers hesitate to work with the 2007 Split Form due to some inconsistencies with its object model. However, it is time to recognize the benefits and functionality that the Split Form has to offer.

Creating a Split Form

The Split Form allows you to create a form that displays the data in both an overall and a detailed datasheet format at the same time. This is easily accomplished with the help of a wizard. And, as you can see in Figure 17-2, the Split Form created by a wizard looks a lot like a form with a subform.

FIGURE 17-2: The Split Form with the 2013 Team

As you can see, the Split Form simultaneously displays data in both form and grid-style views. Although Figure 17-2 shows the single view on top, there are actually four configuration options:

1. Datasheet on top
2. Datasheet on bottom
3. Datasheet on left
4. Datasheet on right

As the user navigates through the records in either the form or datasheet section, the data remains synchronized and both views are based on the same record. One aspect that may not be obvious is that you can selectively hide fields in either view. This means that you can limit the fields on the form view and also organize the datasheet columns into a logical tab order or to have the most frequently searched fields on the far left.

Split forms have the advantage of having only a single object in your database as compared to having to maintain both a main form and one or more associated subforms, each with their own record source and master-child link.

Splitter Bar

The area of the single form and the datasheet can be adjusted by moving the splitter bar, shown in Figure 17-3.

FIGURE 17-3: The Split Form splitter bar

By dragging the splitter bar up or down (or left or right for side-by-side configurations), you can control the amount of data or space displayed on each section. Once you are satisfied with the placement of the splitter bar and to prevent the user from moving it, you can set the Split Form Splitter Bar property to No to hide the splitter bar. Alternatively, you can allow users to move the splitter bar; they may want to maximize the search grid when they need it and then minimize the grid to work with the selected records in form view.

Some developers prefer to actually hide the traditional form portion and opt instead to provide users with a pop-up form to display the details about the data. Using this technique, the developer is able to display more records in the datasheet portion, as shown in Figure 17-4, and still having the form header to provide some functions that you cannot usually do with a datasheet form.

FIGURE 17-4: Header for a Split Form using only a datasheet view

You will also find several more tips for working with Datasheet views as you read through Appendix A. The examples can be applied to the datasheet sections of the Split Form as well as to the Datasheet view of a form or subform.

Pop-up, Modal, or Dialog Forms

Access forms can be opened within your application in three different window modes: Popup, Modal, or Dialog. These can be set manually using the property sheet or programmatically using VBA or macros. Each mode behaves differently and can be used for different purposes. You can also combine the modes to have even greater control over the way the user can interact with the form and the entire application when the form is open.

Pop-up

A pop-up form opens and stays on top of all other open forms within your application (actually, the pop-up form opens outside of the Access application window). Although it is on top of other objects, the pop-up form does not necessarily have to have the focus. This can be quite helpful when you want to present the user with some reference information that needs to be available while they are using or entering data on another form. A pop-up form also allows the user to access the ribbon buttons and menus.

You can create a pop-up form by setting its PopUp property to Yes. This property is often used in conjunction with setting other properties such as Modal, Cycle (of records) and Border Style.

Modal

A modal form opens and keeps the focus until it is closed (or hidden). When a modal form opens, the user will not be able to use other forms or even go to the ribbon (it won't even let you click the Exit button to quit Access). This mode is useful when you want to control the user's workflow by opening each modal form in sequence based on the task they're doing.

You can create a modal form by setting the form's Modal property to Yes. If you use both the PopUp and Modal properties, you can make the form behave both as a pop-up (stays on top) and a modal (keeps the focus) form by setting them both to Yes.

Dialog

A dialog form opens on top of all the other forms and also keeps the focus until it is closed (or hidden). There is one major difference with a dialog form's behavior as compared to a form with its PopUp and Modal properties set to Yes. When a form is opened in dialog mode, all code execution is also “suspended” until the dialog form is closed. This type of behavior is very useful when you need user input before proceeding with the next logic in your code. Some examples of dialog forms are the “warning” messages from Access or the MsgBox() and InputBox() functions.

You can only open a form in dialog mode using code (VBA or macro). The form can be opened in dialog mode regardless of the PopUp and Modal property settings. You can use either of the following lines of code to open a form in dialog mode,

DoCmd.OpenForm "FormName", , , , , acDialog

or:

DoCmd.OpenForm "FormName", WindowMode:=acDialog

There are several other properties that you can use to help guide users through the application. The next example introduces you to the Tag property.

THE DEMO FORMS

To provide clear examples of some of the features, properties, and controls, the 2013 Team database includes a few forms that have the single purpose of demonstrating the specific functionality that is being discussed. These forms start with the prefix frmDemo and aren't intended to be integrated with other processes of the database. The data source may be specifically structured to support features being discussed and should be considered as a slice of a solution or a temporary work table rather than representing fully developed, normalized tables, forms, or reports. As you go through the following examples, you can supplement the discussion by exploring the forms that are referenced.

Tag Property

Most of the controls on a form have a Tag property. It is a general purpose property that you can take advantage of to enhance the functionality of a specific form or apply it globally throughout the application. The Tag property accepts any text value that you assign, so you can adjust the designation depending on the purpose. For example, you can use the Tag property to ensure that certain fields are “read-only” by using the text value “readonly,” as you will see in this demonstration. Another common use of the Tag property is to show or hide controls based on the user, the data, or some other condition.

The form frmDemoTagReadOnly is specifically designed to provide a demonstration that you can easily expand on and incorporate into your solutions. You can see how it works as you follow the discussion. The database also contains the form frmDemoTagRequiredData as an example of how you can use the Tag property to direct user actions when specific data is required based on a scenario. On this form, the Tag property is used to provide an easy-to-understand message to the user; it also changes the background color of the applicable controls and places the cursor in the field needing data. The technique can also be used to provide user-friendly messages for fields that are required at the table level. You will see additional ways to use the Tag property later in this chapter when you review the code to reset column widths in Datasheet view as well as when you are working with the Report Runner.

The current example could likely apply to a lot of your projects, as many solutions require a method to have users log in, and then use that information to identify their permission level or user role, which then dictates what they can do within the database. The form frmDemoTagReadOnly, shown in Figure 17-5, demonstrates how to use the Tag property in conjunction with a user role to allow users to see or edit fields depending on having either an Admin or User role. In this case, Admins are allowed to make any changes to the data, while Users can only change certain fields in the database.

FIGURE 17-5: Using the Tag property to enforce user permissions

Where you would normally use a login form or some other means to determine the current user's role, frmDemoTagReadOnly uses a combo box to simulate this process. It then uses the following code in the form's Open event to set the read-only fields based on the role selected:

Private Sub Form_Open(Cancel As Integer)
'Initiate the user's role in memory based on the combobox value
TempVars.Add "UserRole", Me.cboUserRole.Value

'Set read-only fields based on current user's role
Dim ctl As Control
Dim blnAdmin As Boolean

'Check user's role
If Nz(TempVars("UserRole"), "User") = "Admin" Then
  blnAdmin = True
Else
  blnAdmin = False
End If

For Each ctl In Me.Controls
  If ctl.Tag = "readonly" Then
    Select Case ctl.ControlType
      Case acTextBox
        Ctl.Enabled = blnAdmin
    End Select
  End If
Next

End Sub

Looking back at the code, you can quickly review what it does. At the beginning of the code, an object variable is declared to represent the controls on the form. Then, after establishing the current user's role using a TempVar, the code loops through all the controls on the form checking the Tag property.

If any control has “readonly” in its Tag property, the code sets that control's Enabled property according to the role.

You may also want to include code to confirm values or take other actions. And of course, your forms will have the full suite of controls and navigation appropriate for the situation.

Displaying Images

When designing forms, you can use images, such as logos, to enhance a form's appeal to the user and to convey critical information. Proper use and placement of images can make the form look more interesting and professional. There are several ways you can include images on a form, including bound and unbound objects, the image control, and attachment fields. FrmDemoImage, shown in Figure 17-6, demonstrates the effects of some of the settings. It also demonstrates the benefits of including an image within the instructions to provide helpful guidance to the users.

FIGURE 17-6: Ways to display images on a form

Bound Object Frame

This control is used to display Object Linking and Embedding (OLE) objects stored in your database table. OLE objects can be an image or any other file on your computer. If the content of the object cannot be rendered by Access in the control, an icon is displayed instead.

When storing OLE objects in your table, you have the option of linking the record to the file or embedding the file into the record. Linking the record will cause Access to check the original file for any changes and automatically update the record when a change is detected.

When displaying images using the Bound Object Frame, three display size modes are available: Clip, Stretch, and Zoom. Clip will display the object at actual size and clip the image if the control is smaller than the object. Stretch will size the image to fill the entire control that could result in distorted proportions. And Zoom will display the entire image within the control while preserving its aspect ratio.

Bound Object Frames are extremely powerful. For example, they allow you to edit a Word object in place. As a consequence, it is also a very “heavy” object, taking up a lot of memory and processing power, so use it judiciously.

Unbound Object Frame

The Unbound Object Frame control is similar to the Bound Object Frame, except that the unbound control is primarily used to display objects that are not stored in the database. When using this control, you have the option to use an existing file or to create a new file.

Image Control

The Image control is primarily used to display image files. For example, if you want to display a company logo on the form, you would typically use the Image control. It has similar properties to the bound and unbound object frame controls, such as the ability to link or embed the image and adjust the size displayed in the control.

One very useful property of the Image control is the Control Source property. This property allows you to “bind” the control to a field in your table that stores the full path to an image file. For example, you could create a profile form in your employee database to display a photo of each employee by using image files stored on the company's server. This approach can also be used to include logos on labels, envelopes, invoices, etc. The image control can also be an ideal approach for displaying a QR (Quick Response) code.

Attachment Control

The Attachment control is used primarily to display and manage an Attachment field on your form. It is similar to a Bound Object Frame in that it can contain images and other files. However, the main advantage of an Attachment field over an OLE Object field is that an Attachment field can contain more than one object in one record. As a result, the Attachment control includes a small navigation toolbar that pops into view when the Attachment control has the focus.

Attachment and multi-value fields require special handling, particularly when you need to copy or move records or tables that contain them. You'll learn how to do that a little later in this chapter.

List and Combo Boxes

List and combo boxes are typically used to provide users with lists of values to select from. Whether the purpose is to fill in a value or to find an existing record, it is typically easier and more accurate to allow users to select from a list rather than to type freeform. There are numerous ways to customize the controls to fit the specific needs. For example, when possible you can minimize the potential for entry errors by setting the Limit To List property to Yes. If you do that, you can also specify what list to use, such as selecting a Field or setting the Row Source property to display the unique values of the underlying field. Additional features make it easy for users to add values to the list. For example, you can use the NotInList event to accept new values from the user or you can select an appropriate data entry form in the List Items Edit Form property.

Combo boxes and cascading combo boxes are often used to allow users to search for a record or records that meet the criteria. With very little effort, you can greatly enhance the user's experience by adding additional fields to the record source or dropdown list. Some fields are displayed to help make informed selections, and other fields can be hidden but still used to organize or limit the records or used to populate fields after the selection is made.

For example, if you have 5,000 people registered for an event, you will likely need more than a first and last name to distinguish each person. So in designing the combo box, you would use industry knowledge to add additional fields to help the user make the correct selection, as demonstrated on form frmDemoComboBox. In this case, it seems reasonable to include their company and their position or title, as shown in the first combo box of Figure 17-7. In the lower part of the figure, you can also see that the record source includes additional fields that are used to filter and organize the records.

FIGURE 17-7: Enhanced combo box techniques

If you look at the query grid, you'll notice that the first sort field is the Status field, followed by LastName and other fields that have been designated for sorting. As the records are displayed, they are first grouped by the Status, and then sorted by the last name. The record source also includes a specifically designated sort order field. That can be tremendously helpful when you are dealing with categories or items that you might include in a list but that don't lend themselves to alpha/numeric sorting.

These are the types of UI enhancements that were discussed earlier in this chapter. You can provide similar benefits to those using your applications by including standard sort and status fields in your tables. You may not always need them, but it is easier to have them included but unused than it is to add fields to tables, forms, and control sources after a solution has been deployed.

As you work with combo boxes, you will likely become adept at setting the properties to best suit the scenario. But as you're experimenting and learning which combinations work best for specific situations, you may find it helpful to create and preserve some examples with write-ups, similar to the demo combo box provided here. You might include notes about the importance of the column order and widths, the order in which columns are filtered and grouped, and other comments that may be useful when you are incorporating the example into a future project.

Datasheet View Search Forms

Earlier, you looked briefly at a Split Form in a discussion of a few of the options and benefits associated with having a single source that simultaneously provides both a form and grid layout. Although not a Split Form, the datasheet search example demonstrates some of the techniques that you can use with the form's Datasheet view to give users powerful, informative search capabilities. You can follow the example by using frmSearchPersonEvent. This has the form as a main form, but you can also apply a similar approach to use this as a subform and have additional controls in the main form.

The main purpose of this demonstration is to show you how quickly and easily you can give users a powerful search tool that displays valuable information to help them quickly and accurately filter to the desired record or records. Once they have identified the records, you can offer any number of custom or built-in options, such as sending them to Excel, transmitting them by e-mail, or using them as the record source to open a form or report. You can also put specific actions behind each textbox and even vary the actions based on the user. As you work with this example, you'll recognize the vast potential for uses and benefits.

To get started, you can create a complex query that includes all of the desired information. You will want to ensure that you've used the correct join properties to ensure that records are not eliminated as is often the case if you use an inner join instead of a right or left join. Figure 17-8 shows the design view of qrySearchPersonEvent, the record source for the search form.

FIGURE 17-8: Record source query for the datasheet search form

You'll notice that it has a field called All Names. This was created to facilitate searches across the first, last, and nickname or preferred name fields. Many times, the user may not know how the name was entered, so rather than have them conduct three searches, you can create an expression in your query and search that. The All Names field uses the following expression: All Names: [tblPerson]![NameFirst] & " " & [tblPerson]![NameLast] & " " & [tblPerson]![PreferredName], as shown in the preceding query.

After you create the query, the next step is to create the form. The easiest way is to select the query and then use the ribbon's Create tab and click the More Forms button; then click Datasheet. This will automatically create the datasheet form with all of the fields in the order that they appear in the query, as shown in Figure 17-9.

FIGURE 17-9: Datasheet view as created

You can adjust the fields and make other changes. One of the first tasks should be to give the form a correct name. It is also helpful to use the properties to only allow Datasheet view and to specify whether or not users can update or add data. If this is to be strictly a search form, you use the property settings to prevent users from making any changes but still allow them to open other forms so that they can add and edit records. You can also open pop-up forms from either the record selector or a control (field) on the form to provide users with additional details and options.

One of the benefits of using the Datasheet view is that a lot of people are used to working with Excel so they feel comfortable and confident working with this view in Access. Users may also be familiar with searching and filtering within spreadsheets, so a nice tip to share with them is the built-in feature to perform complex searches in a single column. The most intuitive approach is to use the search and filter drop-down at the top of the column. If you need multiple criteria across several fields, you can use the Filter by Form feature that is on the ribbon's Home tab, in the Sort and Filter group. You can also include this control in custom ribbons. Note that Filter by Form is not available in Access Runtime solutions.

For the purpose of this example, consider that you want to search the All Names column. Using the search tool at the top of the column, you can quickly select items from the list of unique values, or choose one of the text filters. If you are searching for a single value, you merely select the appropriate filter, such as Equals, and type the value into the custom filter form. You can also search for multiple values, such as all names that start with de or te. You would need to use Text Filters and Contains because the name can be anywhere in the field. In the Custom Filter window you need to type: *de* or *te* — with no quotes, as shown in Figure 17-10.

FIGURE 17-10: Steps for creating a complex custom text filter

As mentioned earlier, after you have located the desired records, you can take any number of actions, such as opening a form or report by using the form's double-click event. In the next example, you will see how to take that one step further, and open multiple instances of a form. Plus, you will see how to use the Collection property to limit it to one instance per person. But first, let's look at an approach that might help you resize or hide columns after a user has dragged them out of place:

Called in the Current event of the form =ResetDataSheetColumns([Form])
 You will need to rename and modify to your application.
Public Function ResetDataSheetColumns(frm As Form) As Boolean
'---------------------------------------------------------------------------------
' Procedure    : ResetDataSheetColumns
' Author       : GPG - ' with modifications
' Purpose      : Reset column widths
'---------------------------------------------------------------------------------
On Error GoTo errHandler
Dim ctl As Control
For Each ctl In frm.Controls
  If ctl.Tag = "Hidden" Then

    If ctl.ColumnHidden = False Then ctl.ColumnHidden = True
  ElseIf ctl.Tag = "Fixed" Then
    ctl.ColumnWidth = -2 ' -2 sets column width to fit displayed text exactly
  End If
Next ctl

exitProc:
  Exit Function
errHandler:
  Call GlobalErrorMessage(iNum:=Err, iLn:=Erl, sCtl:= _
"procedure ResetDataSheetColumns of Module modUtils")
   Resume exitProc
   Resume
End Function

Multiple Instances of a Form

Access usually allows only one instance of a given form to be open at one time, regardless of how many times the form is called. For example, this code opens the form only once, despite it being executed twice:

DoCmd.OpenForm "frmMyForm"
DoCmd.OpenForm "frmMyForm"

However, you can use VBA to open multiple instances of the same form. One of the common scenarios is to allow users to compare details or select between items, such as parts or products, menu items, or as in this scenario, event speakers.

In order for the form to be opened multiple times, the Has Module property must be set to Yes. With that set, you can open multiple versions of the same form by simply creating a new instance of the form using a form object. You could start with the following code:

Dim frm As frm

Set frm = New Form_frmMyForm

frm.SetFocus

In the preceding code, a form object is declared and assigned an instance of the original form by using the New keyword. However, the usefulness of that code (and life of the form) depends on where the object variable was declared. As soon as the variable goes out of scope, the form will automatically close. To prevent that, you need to use a more persistent object variable. For the purpose of this demonstration, a global Collection object will be used to contain each instance of the form to keep it available until it is no longer needed.

The download file included in this chapter supports the premise that the user would like to see the profile of each event speaker (or attendee) in a separate form. This allows users to compare speakers as a factor in choosing which sessions to attend. By making the profile (frmAbout) available from the search form, frmSearchPersonEvent, the user can look up available information about anyone that is registered. This example uses the form's DoubleClick event, but you can also place the code behind specific fields. Regardless of the placement, you will also need to inform the users how and why to open this or any other forms that you include.

To open the speaker's profile, the following code can be used in the search form's DoubleClick event:

DoCmd.OpenForm "frmAbout", , , "PersonID=" & Me.PersonID, , acDialog

That code would open the profile form in dialog mode filtered to the speaker info on the current record. However, using dialog mode will not allow any further user interaction on the main form until the profile form is closed. If you don't use dialog mode, which sets the Modal and PopUp properties to No, the user can double-click on another record to view the speaker's profile. When they do, Access will replace the data on the already opened copy of frmAbout, so the user will still only be able to see one profile at a time.

To allow users to simultaneously view multiple instances of frmAbout, you can use the following code in a standard module:

Option Compare Database
Option Explicit

Public collAbout As New Collection 'Instances of frmAbout

Public Sub OpenAbout(ByVal PersonID As Long)
'Open an independent instance of frmAbout

Dim frm As Form
Dim var As Variant
Dim blnAlreadyOpen As Boolean

'Check if same person is already in collection
For Each var In collAbout
  If var.PersonID = PersonID Then
    Set frm = var
    frm.SetFocus
    blnAlreadyOpen = True
    Exit For
  End If

Next

'Open new person form
If Not blnAlreadyOpen Then
  Set frm = New Form_frmAbout
  frm.Visible = True
  frm.Filter = "PersonID=" & PersonID
  frm.FilterOn = True

  'Add to collection
  collAbout.Add frm, CStr(PersonID)
End If

Set frm = Nothing
Set obj = Nothing

End Function

As you review the code, you can see that it starts by creating a Collection object that will store each instance of the form. Because each speaker could appear on the search form multiple times, the code also checks to see if a profile form is already open for the selected person. If not, a new instance of the profile form is opened, as shown in Figure 17-11. When a new instance of the form is created, it is added to the collection to keep it available.

FIGURE 17-11: Multiple instances of a form to compare details

Using this technique allows you to open and preserve a form, but you are responsible for managing this and tracking when forms are closed. When the profile form is closed, it needs to be removed from the collection. To do that, you can use the following code in a standard module:

Public Sub CloseAbout (ByVal ID As Long)
'Remove form instance from collPerson collection

Dim var As Variant
Dim bool As Boolean

'Check if form instance is in collection

For Each var In collAbout
  If var.PersonID = ID Then
    bool = True
    Exit For
  End If
Next

'Remove instance from collection
If bool Then collAbout.Remove CStr(ID)

End Sub

You would then use the following code in the Close event of frmAbout to remove the current instance of that form from the collection:

Private Sub Form_Close()

'Remove form instance from public collection
Call CloseAbout(Me.PersonID)

End Sub

With that, there is one step remaining for you to manage multiple instances of the profile form rather than one. You need to replace the original DoubleClick event code with the following code snippet. So the following code goes behind frmSearchPersonEvent, in the form's DoubleClick event procedure:

Private Sub Form_DblClick(Cancel As Integer)

'Open profile form for this person
Call OpenAbout(Me.PersonID)

End Sub

When you use attachments, such as the image files, you will also encounter new challenges if you need to copy records or files. So the next example provides a process that you can use to copy, insert, or add records and tables that have MVFs or attachments.

MULTI-VALUE FIELDS

Every seasoned developer is familiar with the “evils of lookup fields at the table level.” So when Microsoft added the multi-value field (MVF) feature to easily display child records in one field, it sparked some interesting discussions among Access developers. The primary concern was that storing multiple values in one field is considered against established normalization rules, and therefore, might be a likely source of programming challenges and data discrepancies.

However, the way the MVF was implemented in Access doesn't necessarily violate the rules on normalization — at least there is a valid argument that it doesn't. The MVF uses an internal table to store the child records separate from the parent table, and it maintains the one-to-many relationship between the records. As the use of MVFs has become more prevalent, you may need to know how to manage the data, particularly how to transfer records from one database to another.

That being said, this demonstration provides both the background and a tool to help you manage and manipulate records that contain MVFs. You will learn about their benefits, limitations, and special handling requirements. You will see that using MVFs has several similarities to handling other special controls, such as a multi-select listbox. This example uses the chapter download database file named MVFDemo.accdb, and the code is available in the file named 850832_ch17_MVF_CodeSnippets.txt.

Appending MVFs

As you may already know, you can't use a simple make-table or append query to move or copy multi-value fields from one table into another. This is one of its limitations. The more important limitation is that MVFs are not supported by most relational database management systems (RDBMS). So, if you are planning to upscale your database into an enterprise system, then you will need to use a different approach or make specific arrangements to accommodate the MVF data.

To append the data from a multi-value field into another table, you must loop through its recordset. The following VBA example demonstrates one way to accomplish that. You can find the code and example in the database file MVFDemo.accdb:

Private Sub cmdAddMVFs_Click()

On Error GoTo errHandler

Dim rs As DAO.Recordset
Dim strSQL As String

'Check for records first
If DCount("*", "tblNewTable") > 0 Then
  'Open table with MVF
  strSQL = "SELECT RecordID, MVFs.Value As NewMVF " _
    & " FROM tblOldTable WHERE MVFs.Value Is Not Null ORDER BY RecordID"
  Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

  'Loop through all records and insert MVF into new table
  With rs
    Do While Not .EOF
      strSQL = "INSERT INTO tblNewTable (MVFs.Value) VALUES ('" _
        & !NewMVF & "') WHERE RecordID=" & !recordid
      db.Execute strSQL, dbFailOnError
      .MoveNext
    Loop
    .Close
  End With

  'Refresh new table subform
  Me.frmNewMVF.Requery

Else
  MsgBox "Please add records first.", vbInformation, "No Records"

End If

errExit:
  'Cleanup

Set rs = Nothing
  Exit Sub

errHandler:
  Select Case Err.Number
    Case 3022
      MsgBox "MVFs were already added.", vbInformation, "Done!"
    Case Else
      MsgBox Err.Number & ": " & Err.Description
  End Select
  Resume errExit

End Sub

As you read the code, you see that the MVF values are being copied from tblOldTable into tblNewTable. The first step is to create a recordset based on tblOldTable. Then the code loops through the records and inserts each record into the multi-value field in tblNewTable. It uses the RecordID field to maintain the correct associations.

The recordset object in the code contains all the child records in the multi-value field. You get that by using the Value property, which is rather unique to MVF. Using the Value property, you can refer to the child records in the MVF from the recordset produced by the SQL statement.

Appending Attachments

It may not be obvious but Attachment fields are also considered a special type of MVF. As such, make-table and append queries will not work for moving or copying records with attachments. You might think that because it is an MVF, you can use the same technique shown previously to manipulate its records.

However, that is not the case. Attachment fields have additional properties that also require special handling. Instead of using a simple SQL statement, you will need to create a separate recordset for each Attachment field and loop through its content. The following VBA example demonstrates one way to accomplish that:

Private Sub cmdAddAttachments_Click()

On Error GoTo errHandler

Dim rs1 As DAO.Recordset 'Table with attachments to be imported
Dim rs2 As DAO.Recordset 'Table to import attachments into
Dim rs3 As DAO.Recordset2 'Attachments to be imported
Dim rs4 As DAO.Recordset2 'Recordset to accept the new attachments
Dim strSQL As String

'Open table with attachments
strSQL = "SELECT RecordID, Attachments FROM tblOldTable " _
  & " WHERE Attachments.FileName Is Not Null ORDER BY RecordID"
Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Loop through all the records to be imported
Do While Not rs1.EOF
  'Open table to be appended

strSQL = "SELECT RecordID, Attachments FROM tblNewTable " _
    & " WHERE RecordID=" & rs1!RecordID
  Set rs2 = db.OpenRecordset(strSQL, dbOpenDynaset)

  'Recordsets for the attachment fields
  Set rs3 = rs1!Attachments.Value
  Set rs4 = rs2!Attachments.Value

  'Table to be appended must be in edit mode
  rs2.Edit

  'Add all new attachments (Note: Access automatically adds the file type)
  Do While Not rs3.EOF
    rs4.AddNew
      rs4!FileData = rs3!FileData
      rs4!FileName = rs3!FileName
    rs4.Update
    rs3.MoveNext
  Loop

  'Update parent record
  rs2.Update

  'Go to next record with attachment to import
  rs1.MoveNext

Loop

'Refresh new table subform
Me.frmNewAttachment.Requery

errExit:
  'Cleanup
  rs2.Close
  rs1.Close
  Set rs4 = Nothing
  Set rs3 = Nothing
  Set rs2 = Nothing
  Set rs1 = Nothing
  Exit Sub

errHandler:
  Select Case Err.Number
    Case 3021
      MsgBox "Please add records first.", vbInformation, "No Records"
    Case 3820
      MsgBox "Attachments were already added.", vbInformation, "Done!"
    Case Else
      MsgBox Err.Number & ": " & Err.Description
  End Select
  Resume errExit

End Sub

As in the previous example, the code uses tblOldTable as the table with the attachments to copy or move. And it copies the attachments to tblNewTable.

As you can see, this requires four recordset variables to deal with the Attachment field. To avoid any confusion and to ensure that this clearly shows which recordset is being manipulated, the code purposely does not use any With/End With code block.

As you follow through the code, you will discern that rs1 represents the table with the attachments, and rs2 represents the table where the attachments are being appended. rs3 represents the records in the Attachment field of tblOldTable, and rs4 represents the records that will be inserted into the Attachment field in tblNewTable. As with the MVF code in the earlier example, this code uses the Value property of the Attachment field to instantiate rs3 and rs4.

There are two more properties that you need to handle for Attachment fields: the FileData and FileName properties. A third property, FileType, will be handled automatically by Access, so it is not included in the code.

In a nutshell, the code loops through all the records that have an attachment and then loops through each attachment and copies it to the new table by adding a new record.

Add Attachment from File

You can use the preceding method to copy an Attachment field from another table, but you can also add individual attachments. The DAO method called LoadFromFile allows you to add singular attachments from a file. It uses the following syntax:

Recordset.Fields("FileData").LoadFromFile(<filename>)

So, continuing with the previous example, if you wanted to add a new attachment from a file, you could use code such as this:

rs4.AddNew
  rs4!FileData.LoadFromFile "c:foldernamefilename.ext"
rs4.Update

Save Attachments to File

You may be thinking that what comes in, must go out. So, if you can create an attachment from a file, you should also be able to save an attachment to a file. And, you would be correct. There is also a DAO method to save an attachment to a file. It is called SaveToFile and uses the following syntax:

Recordset.Fields("FileData").SaveToFile(<filename>)

You can take that a step further with the following code that will check to make sure the correct attachment is being saved to a file:

If rs4!FileName = "filename.ext" Then
'Save current attachment to a file
rs4!FileData.SaveToFile "c:foldernamefilename.ext"
End If

And applying it to the current scenario, the code might look something like this:

'Save current attachment to a file
rs4!FileData.SaveToFile "c:foldernamefilename.ext"

As you work with MVFDemo.accdb, you can see how the code is structured and how you can use it to manage attachments.

That wraps up the individual examples. In the next example, you will learn about and leverage several controls and properties of forms as you create a power tool that allows users tremendous flexibility and control for creating customized reports.

REPORT RUNNER

Most users think of forms primarily as a means of entering and viewing data or navigating between forms. While this is certainly true, forms can also provide users with a powerful mechanism for launching reports. Rather than opening the report to show all records, you can use a form to allow users to filter and select the exact records that fit their current needs. And that's what you're about to learn how to do.

This section will cover a host of tools and techniques that you can incorporate into your solutions so that your users will have the ability to not only filter their reports but to predefine which fields they will use as filters.

In the process, you will review how to iterate through a form's controls collection and how to use the hidden system tables. You will also see how to use a report's recordsource to determine which fields the user might wish to include in his filter sets, and use a form control's Tag property to clear the control based on the type of control it is. The code snippets for this section are found in the chapter download file named 850832_ch17_ReportRunner_CodeSnippets.txt. The code is listed sequentially by heading and task.

Creating the Foundation

The first step is to build the tables to hold the information about your reports. For this example, you will use two tables, tblReportCriteria and tblReportCriteriaDetail, shown in Figures 17-12 and 17-13 respectively.

FIGURE 17-12: tblReportCriteria in design view

FIGURE 17-13: tblReportCriteriaDetail in design view

These two tables, together, play a critical role in allowing users to establish the criteria selection fields for their reports. So when you incorporate this functionality into your applications, you will need to use the same table structure. As you can see, tblReportCriteria has four fields: an autonumber primary key, a field that is a foreign key linked to the system ID of the report, a field to give the user a friendlier display name for the report (other than its actual name, which is often not very descriptive), and notes the user may wish to give others about how to use the report.

Table tblReportCriteriaDetail also has four fields: an autonumber primary key, a foreign key to tblReportCriteria, the field name, and notes specific to the criteria fields the user is given. It is a good idea to create a relationship between these two tables and enforce referential integrity with Cascade Delete. This will prevent orphaned detail records should the parent record be deleted. You can also add MSysObjects to the Relationships window by selecting “Show System Objects” in Navigation Options. You can then create the relation with the ReportID field, but since this is a system table you cannot enforce referential integrity.

Now that you have your tables, you're ready to build your forms. You will use three forms. Two of the forms will be bound to the tables. One will be an unbound form that will use the information from the tables to let the user choose which report they want to run and apply any criteria they want (within the constraints defined by the criteria records) to the report.

Setting Up the Report and Its Criteria Fields

The report criteria setup form and subform provide a user-friendly and easy-to-apply method of building the criteria fields for your user's reports. By allowing the user to define which field's criteria may be applied for each report, you remove the burden from you, the developer, of trying to anticipate how each report would be filtered. Often, you would use parameter queries or specialized individual forms to allow the user to choose which records to report on. As the user's needs change, you are usually forced to create new reports or to change or add parameters to the report's query. The same holds true if users start building reports on their own and then find that these new reports need to be filtered to specific records as well.

The tables that mange the reports utilize information from system tables, so any new reports that are added will automatically be available for criteria field selection by the user.

There are a few caveats that need pointing out:

• The tables for the reports will be maintained in the front-end files.
• To provide user-friendly data for filtering, many of the lookup fields store the actual data rather than the PK value.
• To avoid having to deal with raw SQL statements, all reports must be based on a named query or table — the filtering cannot be done within the report's record source.
• A report only has one set of report criteria; your users can change the criteria as needed.
• Fields can be used as criteria selection only once.
• The current process is not designed to use attachment, multi-value, or memo (Long Text) fields as criteria fields. If desired, you can modify the code to prevent fields with those data types from being available as report filtering criteria. In that case, you may benefit from moving some of the code into modules.

It is also helpful to understand the benefits related to some of the deviations from normal practices. Although some developers may argue that it isn't necessary, these tables use an Autonumber for their primary key. The ReportID has a default value to support a process for determining if an empty record has been created. You will also see that each variable in the modules is dimensioned as a Variant rather than an Integer. This allows you to leverage the ability for a Variant to be null and use that to pinpoint failure modes.

The next step is to examine the process for setting up a report to accept criteria selections. You begin with frmReportCriteriaSetup, shown in Figure 17-14. This form is bound to tblReportCriteria.

FIGURE 17-14: The report criteria setup form

The main body of this form has one ComboBox control, two TextBox controls, and a SubForm control. They are for the ReportID, DisplayName, Notes, and the SubForm for the criteria field names, respectively.

You will notice that when you open the form, a flash screen (similar to a splash screen) pops up to warn you that opening the form will close any open reports without saving. As shown in Figure 17-15, this form gives you the option of canceling the action so that you can close any open reports in a more controlled manner.

FIGURE 17-15: The flash screen for the report criteria setup form

The reason that all of the reports need to be closed is so that you can open them again later. As the user selects a report, or does a search for a report that has already been set up, the code will open a hidden instance of the report in design view. This is done so that you can extract the report's RecordSource and get the field list for the Report Runner's subform. You use the following code in the form's On Open event to display a flash screen (aka splash screen) and to close any open reports:

Private Sub Form_Open(Cancel As Integer)
  'Display message and close any open reports then
  On Error GoTo TrapError:

  Dim obj As AccessObject
  Dim db As Object
  Dim varResponse As Variant

  varResponse = MsgBox("Caution: Opening this form will close all open " _
  & "Reports without saving" & vbCrLf & "Are you sure you wish " _
  & "to continue?", vbYesNo, "Report Criteria Setup Form")

  If varResponse = vbNo Then
    Cancel = True
    GoTo ExitSub
  End If

  Set db = Application.CurrentProject

  For Each obj In db.AllReports
    If obj.IsLoaded Then
      DoCmd.Close obj.Type, obj.Name, acSaveNo
    End If
  Next

ExitSub:
  'clean up objects
  Set obj = Nothing
  Set db = Nothing
  Exit Sub

TrapError:

  MsgBox "Error number: " & Err.Number & vbCrLf & "Error Description: " _
& Err.Description & vbCrLf, vbCritical, "Unkown Error"
Resume ExitSub
End Sub

Once the criteria setup form is open, you use code to control various aspects of what is displayed in certain controls as well as the opening and closing of the hidden reports as the user navigates through existing records or creates new ones. You also control the choices in some of the combo boxes to prevent error messages if the user tries to create records that already exist, and to prevent unauthorized choices (reports with record sources not based on a saved query or table).

You can review all of the code behind this form at your leisure, but for now it is worth discussing the form's On Current event. This event contains some useful code for setting the RowSource property of the cboReportIDComboBox. The code for the event is listed here:

Private Sub Form_Current()

  On Error GoTo TrapError
  Dim strReportIDSQL As String

  'Set rowsource for cboReportID
If Me.NewRecord Then
    strReportIDSQL = "SELECT MSysObjects.Id, MSysObjects.Name, " _
  & "MSysObjects.Type " _
  & "FROM MSysObjects LEFT JOIN tblReportCriteria " _
  & "ON MSysObjects.Id = tblReportCriteria.ReportID " _
  & "WHERE (((MSysObjects.Type)=-32764) AND " _
  & "((tblReportCriteria.ReportID) Is Null));"
  Else
    strReportIDSQL = "SELECT MSysObjects.Id, MSysObjects.Name, " _
  & "MSysObjects.Type " _
  & "FROM MSysObjects LEFT JOIN tblReportCriteria " _
  & "ON MSysObjects.Id = tblReportCriteria.ReportID " _
  & "WHERE (((MSysObjects.Type)=-32764));"
  End If
  Me.cboReportID.RowSource = strReportIDSQL
  Me.cboReportID.Requery

  'Hide search if in data entry mode
  If Me.DataEntry Then
    Me.cboFindReport.Visible = False
  Else
    Me.cboFindReport.Visible = True
  End If

  Dim obj As AccessObject
  Dim db As Object
  Dim strReportName As Variant

  'Close any hidden reports that may have been opened by this form

'This closes reports that may be in memory and prevents multiple instances
  Set db = Application.CurrentProject

  For Each obj In db.AllReports
    If obj.IsLoaded Then
      DoCmd.Close obj.Type, obj.Name, acSaveNo
    End If
  Next

  'open current report as hidden to get current recordsource
  strReportName = Me.cboReportID.Column(1)
  strSQL = ""
  Me.txtSQL = ""

  If Not strReportName = "" Then
    DoCmd.OpenReport strReportName, acViewDesign, , , acHidden
    strSQL = Reports(strReportName).RecordSource
    Me.txtSQL = strSQL
'Next: display the fields in this query using combobox RowSourceType = FieldList _
'The control is hidden, but can be visible to show the SQL for troubleshooting.
    Me.sfrmReportCriteriaDetail.Form.cbofieldname.RowSource = strSQL
  End If

ExitSub:
  'clean up objects
  Set obj = Nothing
  Set db = Nothing
  Exit Sub

TrapError:

  MsgBox "Error number: " & Err.Number & vbCrLf & "Error Description: " _
& Err.Description & vbCrLf, vbCritical, "Unkown Error"
  Resume ExitSub
End Sub

There are several factors being addressed by the section of code under the comment ‘Set rowsource for cboReportID’ that is used to set the RowSource property of the cboReportID combo box. One of the challenges is to ensure that a report is only in the list one time — a user can change the criteria, but they can only have one set of criteria per report. You might consider enforcing this at the table level using a unique index (such as a primary key), but that still allows the user to select the report and then presents an error if it already has a record. It is more considerate to not show reports that have already been selected, such as accomplished with this approach.

Selecting the Criteria and Running the Report

Now that you've created your records that give you a description of the report and the fields that can have criteria applied to them, you're ready to select the criteria and run your report. To do this, you will create an unbound form to facilitate the report and criteria selection process. Your form will allow up to three criteria fields to be chosen from those that have been set up for that report. This can be expanded by adding more controls and code as needed.

In this example, you allow the user to choose how subsequent criteria fields are used. As in a query, you can use either And or Or to join the criteria. Using And indicates that each record must contain all of the criteria chosen for all fields. In contrast, using Or between two or more fields' criteria will return all records that meet at least one of the criteria. Your Report Runner form is intrinsically simple yet very powerful. In the header of the form, you have four usable controls: a ComboBox for selecting the report to be run, a CheckBox for selecting if the report is to be previewed or printed directly to the default printer, a CommandButton for launching the report, and a CommandButton for clearing all of the criteria selections.

In the detail section of the form, you have the controls for first selecting the criteria field and then selecting the value(s) from those fields to filter the report. These controls work as a group and use a combo box to select the field and a list box to select the criteria. Between criteria fields 1 and 2 and between 2 and 3 you have an OptionGroup with two Radio Buttons. These allow the user to choose whether to combine the criteria (using And) or to get records that meet both criteria (using Or).

You'll also find three command buttons. These allow users to clear the selection criteria, one list at a time. You can get a better understanding of the form by looking at the Design view in Figure 17-16, or by working with the form itself.

FIGURE 17-16: Design view of the Report Runner form

You will notice that the form itself and the controls are unbound — they do not have a RecordSource. To display the data, you use the RowSource property of each ComboBox control. The combo boxes for the three criteria fields are cascading, so each RowSource is filtered based on the report selected as well as on the choices made in each of the previous criteria fields. This prevents the user from choosing the same criteria field more than once. You can see the rowsource of the three combo boxes in Figures 17-17, 17-18, and 17-19.

FIGURE 17-17: Criteria 1 Row Source

FIGURE 17-18: Criteria 2 Row Source

FIGURE 17-19: Criteria 3 Row Source

Note that for the first criteria field, the only filter used is the report selected. This is also used for the other criteria fields. The second criteria field also now uses the Not Like criteria stipulation with a pointer to the first criteria field to prevent the choice from that field from being available for the second criteria. The third criteria field uses the same method but includes pointers to both the first and second criteria fields. If you wanted to allow the user even more fields to choose from, then duplicate this method for as many controls as needed.

The next step for allowing your user to actually choose criteria values is to populate the ListBox control under each criteria field with values from the report's RecordSource. To make this process easier, most of the work is done behind the scenes and it uses a hidden TextBox to store the SQL statement in the event that it is needed for troubleshooting.

When the user chooses the report to run, several activities take place behind the scenes, including:

• The report is opened in Design view and hidden.
• The report's RecordSource is extracted and stored in a hidden TextBox.
• All of the criteria ComboBoxes are cleared.
• All of the criteria for the ListBox RowSource are cleared.
• All open reports are closed and all code objects are released.

The code described in the preceding text is triggered by the report selection's After Update event, as shown in the following code. It could be placed in a module, but it is kept behind the form so that the forms are self-sufficient and easier to import into other databases:

Private Sub cboSelectReport_AfterUpdate()
  On Error GoTo TrapError

  'Open current record's report in hidden mode to get current SQL source
  On Error GoTo TrapError
  Dim obj As AccessObject
  Dim db As Object
  Dim strReportName As Variant
  Dim strSQL As String
  Dim ctl As Control

  'Close any hidden reports that may have been opened by this form
  Set db = Application.CurrentProject

  For Each obj In db.AllReports
    If obj.IsLoaded Then
      DoCmd.Close obj.Type, obj.Name, acSaveNo
    End If
  Next

  'Open current report as hidden to get current recordsource
  strReportName = Me.cboSelectREport.Column(3)

strSQL = "" 'initialize variable
  Me.txtSQL = "" 'initialize hidden text box

  If Not strReportName = "" Then
    DoCmd.OpenReport strReportName, acViewDesign, , , acHidden
    strSQL = Reports(strReportName).RecordSource
    Me.txtSQL = strSQL 'put report record source in hidden text box
  End If

ExitSub:
  'Clean up objects, clear selections, and close reports
  For Each obj In db.AllReports
    If obj.IsLoaded Then
      DoCmd.Close obj.Type, obj.Name, acSaveNo
    End If
  Next
  For Each ctl In Me.Controls
    Select Case ctl.Tag
      Case "Clear"
        ctl.Value = Null
      Case "ClearSQL"
        ctl.RowSource = ""
        ctl.Value = Null
        ctl.Requery
    End Select
  Next
  strCriteria = ""
  Set obj = Nothing
  Set db = Nothing
  Exit Sub

TrapError:

  MsgBox "Error number: " & Err.Number & vbCrLf & "Error Description: " _
& Err.Description & vbCrLf, vbCritical, "Unkown Error"

  Resume ExitSub

End Sub

You now have at your disposal the actual values for each of the criteria fields that are available in the report. You use the After Update event of each criteria control to change the RowSource property of its corresponding ListBox. Although the delimiters are strings, this code uses a Variant for the Public Variables; which avoids issues in the event that a Null is used. It is worth it in order to smoothly handle unexpected values — and we all know that can happen.

The following code is used for the AfterUpdate event:

Private Sub cboCriteria1_AfterUpdate()

  'Determine and set SQL for Criteria list box
  Dim strSQL As String
  Dim strWHERE As String
  Dim strSQLTemp As String
  Dim strFieldName As String
  Dim strSourceName As String
  Dim intType As Integer

  strSourceName = Me.txtSQL
  strFieldName = Me.ActiveControl

  'Get the Query Field's DataTypeEnum Integer value to use to get
  'the correct Delimiters.
  intType = GetFieldDataType(strFieldName, strSourceName)

  'Get the correct Delimiter for the Field Data Type and store it
  'in a hidden text box.
  varDelimiter1 = GetDelimiter(intType)

'The following line could use SELECT DISTINCT instead of SELECT with GROUP BY:
strSQL = "SELECT " & Me.ActiveControl & " FROM " & Me.txtSQL & " GROUP BY " _
       & Me.ActiveControl & ";"
  Me.lstCriteria1.RowSource = strSQL

End Sub

To insure there are no errors when applying criteria, you must determine the DataType of the criteria field selected. Once you determine the DataType, you need to use the correct delimiter for that DataType. For example, text must be enclosed in single quotes while dates must be enclosed with ‘#’. You use two functions to determine and use the correct delimiters; Function GetFieldDataType and Function GetDelimiter. (Special thanks to Patrick Wood for these functions.)

Function GetFieldDataType accepts two variables: strFieldName and strSourceName. These are the field name and table or query name linked to the criteria field selected. The function returns the numeric value of the data type associated with the field. This value is passed to the function GetDelimiter, where the delimiter associated with that data type is determined. This delimiter is stored in a publicly declared variable for use by the subSetCriteria sub routine for building the criteria used when opening the report:

Public Function GetFieldDataType(ByVal strFieldName As String, _
        ByVal strSourceName As String) As DataTypeEnum
'Purpose: Get the DataTypeEnum value of the specified Field.

On Error GoTo ErrHandle

  Dim db As DAO.Database
  Dim fld As DAO.Field

  'Cannot just use CurrentDb to set the fld Object
  'because it causes an error when trying to get the fld.Type.

Set db = CurrentDb

  On Error Resume Next
  Set fld = db.QueryDefs(strSourceName).Fields(strFieldName)
  If Err.Number = 3265 Then
    'This is not a Query field.
    Err.Clear
    On Error GoTo ErrHandle
    Set fld = db.TableDefs(strSourceName).Fields(strFieldName)
  ElseIf Err.Number <> 0 Then
    'Unknown Error. Trap it.
    GoTo ErrHandle
  End If

  If Not fld Is Nothing Then
    GetFieldDataType = fld.Type
  Else
    GetFieldDataType = 0
  End If

ExitHere:
  On Error Resume Next
  Set db = Nothing
  Set fld = Nothing
  Exit Function

ErrHandle:
  GetFieldDataType = 0
  MsgBox "Error #" & Err.Number & " " & Err.Description _
  & vbCrLf & "In Procedure GetFieldDataType"
  Resume ExitHere

End Function

Public Function GetDelimiter(ByVal intType As DataTypeEnum) As String
'Purpose: Get the delimiter needed to build the Criteria.

On Error GoTo ErrHandle

  Dim strDelimiter As String

  Select Case intType
    Case dbBoolean
      strDelimiter = vbNullString 'Boolean (True/False or Yes/No) Data Type
    Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbBigInt
      strDelimiter = vbNullString 'Numeric Data Type.
    Case dbDate
      strDelimiter = "#" 'Date and/or Time Data Type.
    Case dbText, dbMemo, dbChar
      strDelimiter = "'" 'Text Data Type.
    Case Else
      strDelimiter = "'" 'Assume text Data Type.

End Select

  GetDelimiter = strDelimiter

ExitHere:
  Exit Function

ErrHandle:
  'Don't bother the user with an error in this Procedure.
  GetDelimiter = "'" 'Assume a text data type.
  Debug.Print "Error #" & Err.Number & " " & Err.Description _
  & vbCrLf & "In Procedure GetDelimiter"
  Resume ExitHere

End Function

Now the user is ready for the criteria value selection process. To give your users the greatest flexibility in choosing values, you will want to set each Listbox's Multi Select property to Extended. This will give them the functionality most of them have come to expect when selecting items in a ListBox. That is the use of Ctrl+Click and Shift+Click for selecting multiple items in the list.

Notice the Tag property at the bottom of the property list displayed in Figure 17-20. As you've seen in other examples, the Tag property can be remarkably helpful when you need to execute the same code across multiple controls on a form or report. Here, you use two Tag values: Clear and ClearSQL. You may have noticed that they are used in the cboSelectReport_AfterUpdate event to clear the criteria ComboBoxes and ListBoxes respectively.

FIGURE 17-20: Property sheet showing MultiSelect and Tag Properties

To tie all of the choices together and open a filtered report, you will use a named SubRoutine and a command button. The SubRoutine is triggered by the AfterUpdate event of each ListBox control.

The following code builds a SQL criteria statement that is used when opening the report. Although the GetDelimiter function returns a string and the delimiters are all strings, this code also uses a Variant for the Public Variables in the event that a Null is used, as previously explained:

Option Compare Database
Option Explicit
Public strReportSource As String
Public strCriteria As String
Public varDelimiter1 As Variant '
Public varDelimiter2 As Variant ' } Dimension delimiter variables
Public varDelimiter3 As Variant '/

Public Sub subSetCriteria()
  On Error GoTo ErrorHandler

  Dim ctlListBox As Listbox
  Dim varItem As Variant
  Dim strCriteria1 As String
  Dim strCriteria2 As String
  Dim strCriteria3 As String
  Dim strDelimiter As String

  strCriteria = "" 'initialize criteria variable

  If IsNull(Me.cboSelectReport) Then Exit Sub

  Set ctlListBox = Me.lstCriteria1 'Get primary category selections if any

  'Get the Delimiter for the lstCriteria1 List Box Field Data Type.
  strDelimiter = Nz(varDelimiter1, "'")

  For Each varItem In ctlListBox.ItemsSelected
    strCriteria1 = strCriteria1 & strDelimiter _
    & ctlListBox.ItemData(varItem) & strDelimiter & ", "
  Next

  If Len(strCriteria1) < 2 Then

'if no criteria selection force criteria to empty string
  strCriteria1 = ""
Else
  'remove trailing comma
  strCriteria1 = Left(strCriteria1, Len(strCriteria1) - 2)
End If

Set ctlListBox = Me.lstCriteria2 'Get secondary category selections if any

'Get the Delimiter for the lstCriteria2 List Box Field Data Type.
strDelimiter = Nz(varDelimiter2, "'")

For Each varItem In ctlListBox.ItemsSelected
  strCriteria2 = strCriteria2 & strDelimiter _
  & ctlListBox.ItemData(varItem) & strDelimiter & ", "
Next

If Len(strCriteria2) < 2 Then
  'if no criteria selection force criteria to empty string
  strCriteria2 = ""
Else
  'remove trailing comma
  strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 2)
End If

Set ctlListBox = Me.lstCriteria3 'Get secondary category selections if any

'Get the Delimiter for the lstCriteria3 List Box Field Data Type.
strDelimiter = Nz(varDelimiter3, "'")

For Each varItem In ctlListBox.ItemsSelected
  strCriteria3 = strCriteria3 & strDelimiter _
  & ctlListBox.ItemData(varItem) & strDelimiter & ", "
Next

If Len(strCriteria3) < 2 Then
  'if no criteria selection force criteria to empty string
  strCriteria3 = ""
Else
  'remove trailing comma
  strCriteria3 = Left(strCriteria3, Len(strCriteria3) - 2)
End If

'Build criteria string if any
If Not Len(strCriteria1) = 0 Then
  strCriteria = strCriteria & "[" & Me.cboCriteria1 _
                & "] In (" & strCriteria1 & ") " _
                & IIf(Me.optCriteria2 = 1, " AND ", " OR ")
End If
If Not Len(strCriteria2) = 0 Then
  strCriteria = strCriteria & "[" & Me.cboCriteria2 _
                & "] In (" & strCriteria2 & ") " _
                & IIf(Me.optCriteria3 = 1, " AND ", " OR ")
End If
If Not Len(strCriteria3) = 0 Then
  strCriteria = strCriteria & "[" & Me.cboCriteria3 _

& "] In (" & strCriteria3 & ") "
  End If

  If Len(strCriteria) > 5 Then
    If Right(strCriteria, 5) = " AND " _
    Or Right(strCriteria, 5) = " OR " Then
        strCriteria = Left(strCriteria, Len(strCriteria) - 5) 'remove ending
    End If
  Else
    strCriteria = "" 'set length to 0
  End If

  Exit Sub

ErrorHandler:

  MsgBox "There was an unexpected error" & vbCrLf _
         & "Please report the following to the database developer" _
         & vbCrLf & "Error: " & Err.Number & vbCrLf & "Description: " _
         & Err.Description & vbCrLf & "In Procedure subSetCriteria " _
         & "of Form_frmReportRunner", vbCritical

  Exit Sub
  Resume
End Sub

The preceding code builds a SQL criteria statement that will be used when opening the report. The code iterates through each ListBox's ItemSelectedCollection and concatenates the selected items together inside a SQL IN() clause. An example looks like this: [FieldName] IN (Value1, Value2,...,ValueN). The different fields are then linked together with either an AND or OR depending on the choice of the user. Once the full criteria has been built, any extraneous AND or OR is removed. The completed criteria statement is stored in the public variable strCriteria for use later when opening the report.

Because the criteria selection is based solely on the ListBox controls and Radio Buttons, you only call the subSetCriteriaSub Routine from the AfterUpdate event for each of these controls:

Private Sub lstCriteria1_AfterUpdate()
  Call subSetCriteria
End Sub

Private Sub lstCriteria2_AfterUpdate()
  Call subSetCriteria
End Sub

Private Sub lstCriteria3_AfterUpdate()
  Call subSetCriteria
End Sub

Private Sub optCriteria2_AfterUpdate()
  Call subSetCriteria
End Sub

The final piece of the puzzle is running the report. This is controlled via the cmdOpenReport command button, by using the following code:

Private Sub cmdOpenReport_Click()

  On Error GoTo ErrorHandler

  Dim strReportCriteria As String
  Dim strReportName As String
  strReportCriteria = "" 'initialize criteria

  If IsNull(Me.cboSelectREport) Then
    MsgBox "Please select a report to print", vbCritical
    Exit Sub
  Else
    strReportName = Me.cboSelectREport.Column(3)
  End If

    strReportCriteria = strCriteria

  If Me.chkPreviewReport Then
    If Len(strReportCriteria) Then
      DoCmd.OpenReport strReportName, acViewPreview, , strReportCriteria
    Else
      DoCmd.OpenReport strReportName, acViewPreview
    End If
  Else
    If Len(strReportCriteria) Then
      DoCmd.OpenReport strReportName, acViewNormal, , strReportCriteria
    Else
      DoCmd.OpenReport strReportName, acViewNormal
    End If
  End If

  Exit Sub

ErrorHandler:
  If Err.Number = 2501 Then Exit Sub

  MsgBox "There was an unexpected error" & vbCrLf _
& "Please report the following to the database developer" _
& vbCrLf & "Error: " & Err.Number & vbCrLf & "Description: " _
& Err.Description, vbCritical

  Exit Sub

End Sub

As you can see there are other Command Buttons on the form for clearing selections made by the user. The code behind these has not been covered, but it should be easy to figure out.

And that wraps up the discussion of the Report Runner. It provides an impressive foundation for a custom report designer. Now that you have the functionality and concepts, you can tailor it to work with your projects.

SUMMARY

Forms are the primary way that users work with data and navigate the database. And as the database designer, it is incumbent upon you to make it as efficient, intuitive, and easy as possible for users to manipulate, manage, and report their data. You also need to ensure that the data is accurate, valid, and correctly reflects the user's criteria and purpose.

This chapter covered a lot of tips and techniques to help you create a user-friendly interface. It also included several tools that you can incorporate into your projects, such as the Report Runner, using datasheet searches to open detail records, and managing attachments.

The examples were designed to represent a slice of real-world scenarios to provide you with multiple approaches to find and display data. You can combine and adapt the techniques to best fit whatever project you are working on.

Many of the techniques, such as working with images, are also applicable to reports. And of course, the Report Runner tool, itself, will certainly prove invaluable when you want to allow users to customize their selection criteria. And that is an excellent segue to Chapter 18, “Creating Powerful Reports.”

In Chapter 18, you will cover many of the fundamentals as you create some reports that respond to business needs. You will also create another report customization tool. This one will allow users to determine the grouping and level of detail that they want to see. That means that you can create one report and then enable users to modify the criteria to suit their current needs. For example, users can retrieve summary level reports across all departments or a detailed report for just one group.