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: Microsoft Access 2010
Published: January 2011
Provided by: Michael Stowe, Microsoft Corporation
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
Start Access 2010.
On the New tab of Microsoft Office Backstage view, click Sample Templates.
Under Available Templates, click Issues Web Database.
In the File Name dialog box, type a file name.
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
Open the Main form in Layout view.
In the Property Sheet pane, select Form in the drop-down menu.
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
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
Open the Issues Table.
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
Open the Issues Table.
On the ribbon, click the Table tab.
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
On the Create tab, in the Macros & Code group, click Macro.
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:
Logging into an unpublished database.
Logging into a published database in the web browser.
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.
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.
To examine the FetchLoginID form, press and hold the Shift key, right-click the form in the Navigation Pane, and then click Layout Mode.
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.
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.
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.
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.
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.