![]() 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 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. One of the coolest features of Microsoft Access 2007 is the new and improved template features it provides. All of the database templates included in Microsoft Access 2007 are completely new, and best of all, you can create your own templates with the Access 2007 ADE (Access Developers Extensions) tools. Access 2010 offers a long list of database templates to let you easily fill out pre-defined database elements, including, table, queries, reports and forms. 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. 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 IfTheElse macro block to verify that the application is used in the Access client or in the web browser. 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 IfThenElse 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 IfThenElse 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. 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. 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. 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. 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. 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. 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. 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. BrowseTo Object Type Form Object Name IssuesClosedDS Path to Subform Control Main.NavigationSubform>IssuesClosed.DS Where Condition = [OpenedDate]>[TempVars]![tmpStartDate]-1 And [OpenedDate]. 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. 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. 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. 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]. Microsoft Office Free Templates Official Microsoft Office Templates - Free Online Resources You only get to make one first impression. To put your best foot forward, presentation is crucial. This applies whether you are using Microsoft Word, Excel, PowerPoint or any of the other Office programs. From cover letters to CVs, spreadsheets to pie charts, all of these can be enhanced using the latest and most popular Microsoft Office templates. For anyone who has used the stock templates that are pre-installed within your software, it can be quite a welcome surprise to discover all of the free resources available for genuine Microsoft Office software. You are only a click away from giving your work that well organised, professional look. Microsoft Office Template Categories:|||||||||||||||||||||||||||||||||||||| The Microsoft Office online community allows users to edit and share templates. If the template doesn't suit your exact needs, with a few simple edits you should be able to adjust it appropriately. New Microsoft Office templates are added regularly so there is always a new design to try out. Feel free to bookmark this page for reference so you can quickly return whenever you need it.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2018
Categories |