Understanding the Issues Template in Access 2010

Summary:  This technical article describes the design patterns used in the Microsoft Access 2010 Issues web database template.

Applies to: Access 2010 | Office 2010 | VBA

In this article
Overview
Logging in to the Database
Understanding the Main Navigation Form
Understanding Dialog Box Save/Save and Close vs. Save/Cancel
Using the BrowseTo Action to Open Parameterized Forms
Understanding the Search Box
Using the Report Center
Conclusion
Additional Resources

Published:  January 2011

Provided by:  Michael Stowe, Microsoft Corporation

Contents

• Overview

• Instantiating the Issues Web Database template

• Logging in to the Database

• Understanding the Main Navigation Form

• Opening an Issue

• Editing an Issue

• Adding a Related Issue

• Understanding Dialog Box Save/Save and Close vs. Save/Cancel

• Using the BrowseTo Action to Open Parameterized Forms

• Understanding the Search Box

• Using the Report Center

• Conclusion

• Additional Resources

Overview

Five web database templates are included with Microsoft Access 2010. They are designed to help users become productive with Microsoft Access immediately in several common scenarios. They can also help developers become productive in building web databases.

This article looks inside the Issues web database template to discuss how the template works, and in particular, the design patterns that you can reuse when you create your web databases.

Instantiating the Issues Web Database template

Before you get started, create a new database from the Issues web database template.

To create a new database from the Issues web database template

1. Start Access 2010.

2. On the New tab of Microsoft Office Backstage view, click Sample Templates.

3. Under Available Templates, click Issues Web Database.

4. In the File Name dialog box, type a file name.

5. Click Create.

Finding the Code in the Access User Interface

If you are an experienced Access developer, you have used Visual Basic for Applications (VBA) code to implement your business logic and program flow. One important difference between web databases and the client-only Access databases is that web databases do not support VBA code. In web databases, custom logic is implemented by using macros.

In earlier versions of Access, this would have made developing a sophisticated application difficult. However, this is less of a restriction than you might expect, because Access 2010 macro capabilities are significantly improved.

You will not find the macros by browsing the Navigation Pane. Where you find a particular macro depends on its type

Macros that capture form and control events are embedded in the control event to which they are attached. That is, they are stored as part of the control instead of as a separate object.

For example, you may want to examine the macro embedded in the On Load event of the Main form. To do this, use the following procedure.

To examine the macro embedded in the On Load even of the Main form

1. Open the Main form in Layout view.

2. In the Property Sheet pane, select Form in the drop-down menu.

3. Click the Event tab.

   The On Load event displays [Embedded Macro], as shown in Figure 1.

   Figure 1. On Load Event in the Property Sheet

   

4. Click the ellipsis button.

The macro opens in Macro Builder.

Data macros, a new kind of macro introduced in Access 2010, are attached to tables and table events. To see the data macros attached to a particular table, you must open the table.

For example, you may want to examine the data macros stored in the Issues table. To do this, use the following procedure.

To examine the data macros in the Issues table

1. Open the Issues Table.

2. On the ribbon, click the Table tab.

The table events are displayed in the Before Events and After Events groups. Events that contain data macros are highlighted, as shown in Figure 2.

Figure 2. Table tab

In this example, a data macro is embedded in the Before Change and After Update events. To display the data macro, click a highlighted event.

A named data macro is attached to a table, but not a particular event. To view the named data macros attached to the RelatedIssues table, use the following procedure.

To view the named data macros attached to the RelatedIssues table

1. Open the Issues Table.

2. On the ribbon, click the Table tab.

3. Click Named Macro, click Edit Named Macro, and then click the name of the macro that you want to examine.

The macro opens in Macro Builder.

The methods shown earlier in this section do not provide a convenient way to browse the macros that have been embedded in controls and the data macros associated with tables.

Macro Builder provides a convenient method that you can use to discover which forms, controls, and tables have embedded macros.

To open Macro Builder, use the following procedure.

To Open Macro Builder

1. On the Create tab, in the Macros & Code group, click Macro.

2. In the Action Catalog pane, double-click the In this Database category.

Double-clicking one of the embedded macros does not open that macro for editing. Instead, the selected macro is added to the current macro open in Macro Builder. If you intent to edit a macro, you should open it from the control event in which the macro is embedded.

Logging in to the Database

An important aspect of tracking issues is logging who an issue is assigned to and who opened the issue. To do this, a reliable logon method has to be used.

When designing logon functionality for a web database that may also be used in the Access client, consider the following three scenarios:

1. Logging into an unpublished database.

2. Logging into a published database in the web browser.

3. Logging into a published database in the Access client.

Access 2010 introduces a new function named CurrentWebUser, which is designed to simplify tying the three scenarios together.

The CurrentWebUser function can be used to return the member ID, user name, logon name, or e-mail address of the current user of an Access web application. If the database has been published, this information can be collected based on the user identity in SharePoint Server 2010. If the database has not been published, the CurrentWebUser returns Null.

When you open the Issues web database, the form named Main is loaded and the macro embedded in its On Load event is executed. This macro attempts to determine the logon credentials of the current user.

If the credentials cannot be determined automatically, a form that prompts the user to select their credentials is displayed.

The On Load macro uses an If…The…Else macro block to verify that the application is used in the Access client or in the web browser.

If   Not(IsNull(CurrentWebUser(0))   Then
     OpenForm (Login,,,Dialog)

If the database is used in the Access client, the Login form is displayed in Dialog mode shown in Figure 3.

Figure 3. Login form

In the Login form, the user can select their credentials and then click Login. If their credentials are not listed, they should click New User to add their credentials to the database. Clicking New User displays the User Details form. The User Details form is bound to the Users table so that any changes are automatically saved to that location.

Clicking the Login button in the Login dialog box executes the macro embedded in the On Click event of the Login button.

An If…Then…Else macro block is used to determine whether a user was selected in the lstEmailNames list before the user clicked the Login button. If a user was not selected, a message box prompts for a selection.

If   IsNull([lstEmailNames])   Then
   MessageBox   (Select a user.)

If a user was selected, the Else clause of the If…Then…Else macro block logs the user into the database. To do this, the SetTempVar function creates a TempVar that will be used throughout the session to identify the current user.

A TempVar is a variable that is global to the current Access session. It can be accessed in an expression, macro, or even VBA. To access the value of a TempVar in a macro or expression, use the following syntax.

=[TempVars]![VariableName]

The SetTempVar function creates a variable named tmpUserId and assigns the ID of the selected user to the variable.

Else
   SetTempVar(tmpUserID, [lstEmailNames])
   CloseWindow
End If

The user is now logged in to the application.

If the user is using the database in a web Browser, or the database is being used in the Access client, but was opened in the web browser, macro execution continues in the macro embedded in the On Load event of the Main form.

The Else clause of the If…Then…Else macro block creates several TempVar variables that use the CurrentWebUser function identify the current user.

Else
   SetTempVar (tmpUserName, CurrentWebUser(1))
   SetTempVar (tmpUserLogin, CurrentWebUser(2))
   SetTempVar (tmpUserEmail, CurrentWebUser(3))

Then, the RunDataMacro function runs the named data macro VerifyUsers. The variables created in the previous code example are passed into the VerifyUsers data macro as parameters.

RunDataMacro (Users.VerifyUsers, [TempVars]![tmpUserEmail], [TempVars]![tmpUserName], [TempVars]![tmpUserEmail])

The VerifyUsers data macro is attached to the Users table. It determines whether the specified user credentials are already stored in the Users table. If the Users table does not contain the specified user credentials, they are added to the table.

The first thing the VerifyUsers data macro does is to use the SetLocalVar function to create a variable. Using the SetLocalVar function creates a LocalVar, which is a type of variable that is visible only to the current procedure.

The variable that is named varInsertUser, is initialized with a value of True.

SetLocalVar  (varInsertUser, True)

Then, a LookupRecord data block is used to determine whether the user credentials already exist in the Users table. If the record specified in the LookupRecord data block is found, a set of actions can be performed on the record.

In this case, the varInsertUser variable is set to False if the user credentials are found by the LookupRecord data block.

Look Up a Record in   Users
   Where Condition   = [Email]=[prmUserEmail] Or [Login]=[prmUserLogin]
            Alias
   SetLocalVar (varInserUser, False)

Next, an If…Then…Else block is used to check the value of the varInsertUser variable. If varInsertUser is still set to True, a CreateRecord data block is used create a new record in the Users table.

The SetField function is used to set the value of the fields in the Users table to the user credentials passed into the VerifyUsers data macro.

If   [varInsertUser]   Then
   Create a Record in   Users
                Alias
   SetField   ([Email], [prmUserEmail])
   SetField   ([FullName], [prmUserName])
   SetField   ([Login], [prmUserLogin])
End If

Now that the user credentials exist in the Users table, macro execution continues in the Else clause of the macro embedded in the On Load event of the Main form.

The OpenForm function opens a form named FetchLoginID.

OpenForm   (FetchLoginID, [Email]=[TempVars]![tmpUserEmail] Or [Login]=[TempVars]![tmpUserLogin],,Dialog)
   End If

The macro embedded in the On Load event of the FetchLoginId form is executed. The FetchLoginID form is hidden so this process is transparent to the user.

The SetTempVar function creates a variable named tmpUserId and assigns the ID of the current user to the variable.

SetTempVar(tmpUserID, [ID])
CloseWindow

The user is now logged in to the application.

Understanding the Main Navigation Form

When you have logged in, the Main form is displayed. The Main form serves as the home page for the Issues web database.

The Main form is centered on a control that is new to Access 2010, the Navigation control. The Navigation control makes it easy to build a web-like user interface for your application.

Each tab in a Navigation control is represented by a NavigationButton control. Each NavigationButton is connected to a subform control that hosts the form or report to be displayed when the tab is selected.

Opening an Issue

The Open Issues tab is where you open new issues and edit existing issues. When you activate the Open Issues tab, the IssuesOpen form is displayed.

The IssuesOpen form contains two sections. The top section functions as the ribbon for the Open Issues tab. It contains the New Issue button and the Search text box. The bottom section is a subform control that displays the IssuesDS form.

To open a new issue, click the New Issue button. The macro embedded in the On Click event of the New Issue button ensures that you have logged in to the database, and then displays the IssueNew form in dialog mode.

The IssueNew form enables you to enter the information that you would expect to when describing an issue. When you have described the issue, click either the Save & New button or the Save & Close button to commit the changes.

The macro embedded in the On Click event of the Save & New button and the Save & Close buttons are similar. Both macros start by ensuring that the txtSummary control summarizes the issue. A message box prompts you to summarize the issue.

If IsNull (txtSummary) Then
MessageBox (You must enter a summary.)
StopAllMacros
End If

With one exception, the controls in the IssueNew form are bound to fields in the Issues table. For example, the txtSummary text box is bound to the Summary field. The cboPriority combo box is bound to the Priority field, and so on. The SaveRecord command is used to commit the changes to the Issues table.

SaveRecord

Before the changes are committed, the data macro embedded in the Before Change event of the Issues table runs. The Before Change data macro enforces business rules for the Status and Resolution fields.

The first rule is if the Status field is set to Resolved, then the Resolution field must be set. If the Resolution field has not been set, the RaiseError function stops the data macro and returns an error that is displayed by the submacro named ErrorHandler back in the On Click event of the button.

If [Status] = "3" Then
If IsNull([Resolution]) Then
RaiseError (1, You must select a resolution.)
End If

The next business rule specifies that the Resolution filed must be set if the Status field is set to Closed.

Else If [Status] = "4" Then
If IsNull([Resolution]) Then
RaiseError (2, An issue must be resolved before it can be closed.)
End If

Finally, the Resolution field is cleared if it had been filled, but the Status was not set to Resolved or Closed.

Else If Not (IsNull([Resolution])) Then
SetField (Resolution, Null)
End If

When the Before Change data macro is complete, execution passes to the After Update data macro.

The After Update data macro creates an entry in the Comments table when the Resolution field or the Status field is updated. To do this, the data macro named AddComment embedded in the Comments table is called.

The After Update data macro first checks for updates to the Resolution field. If the Resolution field has been updated, the AddComment data macro is called.

If Updated ("Resolution") then
RunDataMacro (Comments.AddComment, [ID], [ChangedByUserID],"—Issue Resolved as " & [Resolution])
End If

A series of similar checks are applied to the Status field.

The AddComment data macro takes three parameters. The data macro creates a new record in the Comments table that contains the parameters and the current date.

Create Record in Comments
Alias
SetField (Comment,[prmComment])
SetField (IssueID, [prmRelatedID])
SetField (CommentDate, Now())
SetField (UserID, [prmUserID])

At this point, macro execution returns to the On Click event initiated in the IssueNew form. Unlike the other controls on the IssueNew form, the txtDescription control is not bound to the Issues table. The contents of this control are written to the Comments table.

To do this, call the AddComment data macro.

If Not IsNull([txtDescription])) Then
RunDataMacro (Comments.AddComment, [ID], [txtDescription,[TempVars]![tmpUserID])
SetProperty (txtDescription, Value, =Null)
End If

Now, you have successfully added a new issue to the database.

Editing an Issue

When you have created an issue, you can edit on one of two ways. You perform some basic editing directly in the IssuesDS subform. However, a richer experience is available by using the IssueDetail form.

To open the IssueDetail form, click the ID field of the issue to edit. The IssueDetail form is organized in two sections. The top section contains controls that are bound to the Issues table. Many of these are fields that you set in the IssueNew form, such as Summary, Status, and Assigned To, with several new fields, Due Date and Attachments.

The bottom section of the form contains a Tab control named TabCommentsRelated. The TabCommentsRelated control contains two pages, Comments and Related Issues.

On the Comments tab, you can add comments about the current issue in the txtAddComment control. When you click Add a Comment, the AddComment data macro is used to add the comment to the Comments table.

Adding a Related Issue

The Related Issues tab enables you to link issues to the current issue. This enables you to track a related set of issues at a glance.

Related Issues are stored in a junction table named RelatedIssues. Relating one issue to another requires creating two records in the table. For example, you need to relate issue 125 with issue 99. One record is created relating issue 125 to issue 99. A second record is created relating issue 99 to issue 125. This ensures that issue 99 is displayed as a related issue when you edit issue 125, and vice-versa.

Clicking the Add Related Issue button executes the macro assigned to its On Click event. The macro first deletes a temporary variable named tmpRelatedIssueID, and then displays the IssueRelatedID dialog box.

RemoveTempVar (tmpRelatedIssueID)
OpenForm (IssueRelatedID,,,Dialog)

In the cboRelatedIssueID dialog box, you can select any issue from the Issues Table. Clicking OK creates a new TempVar named tmpRelatedIssueID and sets its value to the issue selected in the cboRelatedIssueID combo box. The cboRelatedIssueID dialog box is then closed.

SetTempVar (tmpRelatedIssueID, [cboRelatedIssueID])
CloseWindow

Execution returns to the On Click event of the Add Related Issue button. The On Click macro checks for the tmpRelatedIssueID TempVar, and then calls the AddRelatedIssue data macro. The ID of the current record in the IssueDetail form is passed as the main issue, and the tmpRelatedIssueID TempVar is passed as the record to relate to the main issue.

If Not(IsNull([TempVars]![tmpRelatedIssueID])) Then
RunDataMacro (RelatedIssues.AddRelatedIssue, [ID], [TempVars]![tmpRelatedIssueID])

Execution passes to the AddRelatedIssues data macro.

The data macro creates a local variable named tmpAlreadyRelated. The variable will evaluate to True if the parameters that are passed into the data macro represent the same issue.

SetLocalVar (tmpAlreadyRelated, [prmIssueID], [prmRelatedIssueID]) 

The AddRelatedIssues data macro then uses a LookupRecord data block to verify that the specified relationship already exists in the RelatedIssues table. If the relationship already exists, the tmpAlreadyRelated local variable is set to True.

Look Up A Record In RelatedIssues
Where Condition =[IssueID]=[ prmIssueID] And [RelatedIssueID]=[ prmRelatedIssueID]
Alias
SetLocalVar (tmpAlreadyRelated, True)

Next, the tmpAlreadyRelated local variable is used to determine whether to add a record to the RelatedIssues table. A new record is created if tmpAlreadyRelated has not been set to True.

If Not ([tmpAlreadyRelated]) then
Create a Record In  RelatedIssues
Alias
SetField ([IssueID], [prmIssueID])
SetField ([RelatedIssueID], [prmRelatedIssueID])
End If

Execution returns to the On Click event of the Add Related Issue button. The AddRelatedIssue data macro is called again. This time, the parameters switch positions to ensure that both issues are related to one another.

RunDataMacro (RelatedIssues.AddRelatedIssue, [TempVars]![tmpRelatedIssueID],[ID])

When macro execution returns from the AddRelatedIssue data macro, the On Click event of the Add Related Issue button concludes by requerying the IssuesRelated subform.

Requery (sfrRelatedIssues)
End If

Understanding Dialog Box Save/Save and Close vs. Save/Cancel

Be aware that the dialog forms that require editing do not contain a Cancel button. The IssueNew form contains Save & New and Save & Close buttons. The IssueDetail form contains Save and Save and Close buttons.

To cancel your edits, click the Close button in the upper-right corner of the form.

In earlier versions of Access, you may have always provided your users who have explicit choices for Save and Cancel, while disabling the Close button of the form.

So, why change to a different interaction model? There are two reasons that the web templates use a different model.

First, the web templates are designed to be used in the web browser. Usability tests suggest that users expect the popup windows to behave in a web-like manner, where closing a popup window is the action most often use to cancel the action.

Second, most of the logic that edits and adds the records was moved out of the forms. That logic now resides in data macros.

Using the BrowseTo Action to Open Parameterized Forms

By default, when you click the Closed Issues tab on the Main form, only issues opened in the past 30 days are displayed.

When the Closed Issues tab is active, the IssuesClosed form is activated. The IssuesClosed form contains two sections. The top section functions as the ribbon for the Closed Issues tab. Use the txtStartDate and txtEndDate text boxes to adjust the records displayed in the bottom section of the form. Use the Search text box to search the closed issues.

The bottom section of the form is a subform control that displays the IssuesClosedDS form, which displays the closed issues. The IssuesClosedDS form is a parameterized form that uses the IssuesClosed query as its record source.

When the IssuesClosed form is activated, the macro embedded in its On Load event runs.

The On Load event creates two TempVars. One stores the value that is contained in the txtStartDate text box, the other stores the value that is contained in the txtEndDate text box.

SetTempVar (tmpStartDate, [txtStartDate])
SetTempVar (tmpEndDate, [txtEndDate])

Next, the BrowseTo action is used to load the IssuesClosedDS form. The Where Condition argument specifies that only issued opened between the dates specified in the txtStartDate and txtEndDate text boxes are to be loaded.

BrowseTo
        Object Type  Form
              Object Name  IssuesClosedDS
        Path to Subform Control  Main.NavigationSubform>IssuesClosed.DS
                Where Condition = [OpenedDate]>[TempVars]![tmpStartDate]-1 And [OpenedDate]<[TempVars]![tmpEndDate]+1
                           Page
                      Data Mode Edit

This is not comparable to filtering a recordset. Closed issues that fall outside the dates specified in the txtStartDate and txtEndDate text boxes are not loaded into the form.

If the Issues database contained several thousand closed issues, then using the Where Condition argument to load a smaller recordset may perform better than loading, and then filtering a similar number of records when the running on the web.

To adjust the date range of the issues to be displayed, type a new date in either the txtStartDate and txtEndDate text box. The macro embedded in the After Update event of each control uses the BrowseTo action in the same manner as the On Load event of the IssuesClosed form.

Understanding the Search Box

The IssuesOpen and IssuesClosed forms both contain a Search text box that can be used to find records in either form. It provides the use with a web-like method for filtering records in the previously mentioned forms.

What technology is required to implement the Search text box? Is a special indexing required? Is Bing being called behind the scenes?

No additional technology is required to implement the Search text box. IssuesDS, the subform that displays the open issues, uses the IssuesOpen query as its record source. The IssuesOpen query uses the IssuesExtended query to generate its results.

The IssuesExtended query creates a custom field named Searchable. The Searchable field concatenates data from all of the fields that contain information that you might want to search into one string. This string is the item that is searched when you enter a query into the Search text box.

The expression appears similar to the following code example.

Searchable: [Issues].[ID] & " " & [Summary] & " " & [Status] & " " & [Priority] & " " & [Category] & " " & [Project] & " " & [Keywords] & " " & [Resolution] & " " & [ResolvedVersion] & " " & [Users].[FullName] & " " & [Users].[Email] & " " & [Users_1].[FullName] & " " & [Users_1].[Email]

When you enter a term in the Search text box and then click Search, the macro embedded in its On Click event is executed.

The macro first checks the contents of the Search text box for its default value. If the user clicked Search with the default value in the Search text box, the SetFilter action displays all records.

If  [txtSearch]="Search…"  Then
SetFilter  ([Searchable] Like "*", DS)

If the Search text box contains anything other than the default value, the SetFilter action is used to filter the records displayed in the subform. The contents of the search text box are used to filter the Searchable field by using the LIKE operator.

Else 
SetTempVar  (tmpSearch, [txtSearch])
SetFilter  ([Searchable] Like "*" & [TempVars]![tmpSearch] & "*", DS)
End If

Pressing ENTER after you enter a term in the Search text box executes the macro embedded in its After Update event. The After Update macro repopulates the Search text box that has its default value if it is blank. The remainder of the macro is identical to the On Click macro of the Search button.

Using the Report Center

In the Report Center tab, you can view, customize, and print reports that have been created for you.

Activating the Report Center tab loads the ReportCenter form. This form has three sections.

The top section contains the Open In a New Tab button, which is used to open the current report in a new window for printing.

The bottom-left section of the ReportCenter form contains a subform control named sfrReportPage. The sfrReportPage control is where the form that ultimate hosts the reports is displayed. By default, the ReportCenterDefault form is displayed in the control.

The bottom-right section of the ReportCenter form displays a button that can be used to load each report. This section will examine what happens when you click the Issues by Status button.

Clicking the Issues by Status button executes the macro attached to its On Click event. Before loading the report, the macro creates two TempVar variables.

SetTempVar  (tmpReport, "IssuesByStatus")
SetTempVar  (tmpParameterized, "dates")

Then the macro uses the BrowseTo action to load the ParamterizedReport form into the sfrReportPage control.

BrowseTo
       Object Type  Form
       Object Name  ParameterizedReport
 Path to Subform Control  Main.NavigationSubform>ReportCenter.sfrReportPage
    Where Condition
               Page
                     Data Mode  Edit

The ParamterizedReport form resembles the IssuesClosed form. It contains two text boxes, txtStartDate and txtEndDate, that are used to define the parameters for the issues displayed in the report.

When the ParamterizedReport form is opened, the macro embedded in its On Load event runs.

The On Load event creates two TempVars. One stores the value that is contained in the txtStartDate text box; the other stores the value that is contained in the txtEndDate text box.

SetTempVar (tmpStartDate, [txtStartDate])
SetTempVar (tmpEndDate, [txtEndDate])

Next, The BrowseTo action is used to load the report specified by the tmpReport TempVar. The Where Condition argument specifies that only issued opened between the dates specified in the txtStartDate and txtEndDate text boxes are to be loaded.

BrowseTo
        Object Type  Report
              Object Name  =[TempVars]![tmpReport]
        Path to Subform Control  Main.NavigationSubform>ReportCenter.sfrReportPage>ParameterizedReport.sfrReport
                Where Condition = [OpenedDate]>[TempVars]![tmpStartDate]-1 And [OpenedDate]<[TempVars]![tmpEndDate]+1
                           Page
                      Data Mode Edit

This differs from filtering a recordset. The records that fall outside the dates specified in the txtStartDate and txtEndDate text boxes are not loaded into the report.

Finally, the IssuesByStatus report is loaded. You can use the use the txtStartDate and txtEndDate text boxes to refine the records displayed in the report. When you are ready to print the report, click the Open In a New Tab button. The report will be opened in a new tab that is suitable for printing.

Conclusion

The Issues web database template included with Access 2010 contains several design patterns that you can use to implement common features in your Access web databases. In this article, you learned that these design patterns were implemented by using Macros and data macros, which can be used in databases to be deployed to the web by using Access Services and client computers. This article walked through the code behind the design patterns. This shows how the macros interact with data macros.

Additional Resources

For more information about the topics discussed in this article, see the following resources:

• Introducing the Macro Designer

• Tutorials: Access 2010 Macro Designer

• Access 2010 data macros

• What's New for Developers in Office 2010