Creating Enterprise Forms in Microsoft SharePoint 2013

  • 6/15/2013

Creating Access forms

In this section, you will learn about creating SharePoint forms by using Access 2013. You will be given a walkthrough of a Site Request form similar to the previous section, but this time built in Access and using the new SharePoint app model.

Introduction to Access 2013

Access 2013 features a new application model that enables power users and developers alike to quickly create web-based form applications. Most Access databases need a good form in front of them, and fortunately Access 2013 includes a set of templates that you can use to jump-start creating your application. Taking a look at some of the example templates included with Access is a great way to understand how to build your own form app.

As mentioned in Chapter 5 when SharePoint 2013 hosts an Access app, SQL Server 2012 is used as its data storage technology. Therefore, Access 2013 significantly improves the manageability and scalability of Access form applications. Availability of Access Services with Office 365 and SQL Azure can significantly increase the reach of your Access forms.

Building a simple Access form app

Building an Access form like the InfoPath example shown in the previous section is similar in terms of effort. Replicating the basic functionality of the Site Request form can be accomplished without any code; however, from a design standpoint, Access 2013 is a different tool than InfoPath. Using some basic design tactics in the Access Designer, we can essentially replicate the form, but now you can also gain all the inherent advantages of a forms tool that conforms with the new app model.

Before we deconstruct the Site Request form built in Access, let’s cover a few basic Access forms design concepts.


Access forms also have the concept of views, much as InfoPath does.

Access automatically generates two views—List and Datasheet—for each table in your form. You can also create blank views if you prefer to start from scratch with your form views. Each view in an Access form automatically gets some useful built-in UI elements, such as the Action Bar, which that is common in each view and shown in Figure 21-13. The buttons, from left to right, perform the following actions:

  • Add a new record/form

  • Delete the current form

  • Edit the current form

  • Save your changes

  • Cancel your changes

If required for your form, you can add buttons that perform custom actions. You can also delete the default buttons, or hide the Action Bar altogether. In many cases, the default buttons provide the basic editing capabilities that Access form apps need.

Figure 21-13

Figure 21-13 The default buttons on the Action Bar provide basic editing capabilities.


There are two types of macros in Access forms: UI macros and Data macros. UI macros will be used extensively in most Access forms because they provide the intuitive navigation guidance of a well-designed form. UI macros perform actions such as navigating to another view or using logic to show or hide various controls. UI macros can be attached directly to objects, such as command buttons or combo boxes.

When you connect a UI macro to an object in the form, the macro is typically triggered by an event. The types of events supported by UI macros include:

  • On Click. Occurs when a control is selected via a mouse click.

  • On Load. When a view is first opened, On Load macros are instantiated.

  • On Current. When the user moves to a different record in a form view.

  • After Update. After you type data into a control or choose data from a control, this type of macro is triggered.

While UI macros focus on the elements that the form user interacts with, data macros provide the capability for implementing business rules at the data layer. Therefore, data macros can create, edit, and delete records.

Understanding the design of the Access Site Request form

In this section, we will explain the Access version of the Site Request form on a view-by-view basis. This will allow you to fully grasp the design principles at work within each page of the form.

View 1 (Home)

After you have added the Site Request app to a site in which you are an owner, go ahead and use the Add button on the Action Bar of the form to add a few new records. While navigating through the form and its various views, you should begin to get an understanding of the user experience differences between an Access-based form and InfoPath. When you have created and saved a few new records, then use the Settings icon and choose Customize In Access, as shown in Figure 21-14. This will open Access 2013 and put you in Edit mode, which is similar to Design mode in InfoPath.

Figure 21-14

Figure 21-14 Use the Settings icon to edit the form in Access.

Once Access opens the form, ensure that you are in the Home view, as shown in Figure 21-15, and then click the Edit icon.

Figure 21-15

Figure 21-15 The Home view of the Site Request form.

Now that you have entered Edit mode, we’ll take a look at a few of the highlights of this view so that you can understand how to design an elegant, easy-to-use form app using Access 2013.

When you previewed the form in your browser, you may have noticed that the Request Name field contained some text to help the form user. This is called an input hint. A best practice is not to use input hints on every field in your form, or else the user will learn to ignore them. But in a required field, such as Request Name, it makes sense to alert users to the fact that they must enter a value. As shown in Figure 21-16, you can see that there is a toolbar with three options on many of the controls in Access forms: Data, Formatting, and Actions. These will help you provide the functionality that each of the fields in your form may need. In the case of the Request Name field, we use the input hint under Formatting to provide the hint.

Figure 21-16

Figure 21-16 An input hint on the Request Name field of the Site Request form.

As users proceed in the Home view of the Site Request form, they are given a drop-down combo box from which to select the desired type of record. The items in the drop-down box can come from either a table in your Access database, or they can be entered manually into the table properties, as shown in Figure 21-17. To access the list of values for a combo box, select the Modify Lookups button on the ribbon.

Figure 21-17

Figure 21-17 The items in the drop-down field can come from a table in your Access database or they can be entered manually into the table properties.

Also on the Home view, there is an arrow graphic similar to the picture button on the InfoPath version of the form. This helps the user easily navigate through the various views of the form in a wizard-like manner, thus eliminating any potential confusion. If you select the Actions property of the arrow graphic, you will notice in Figure 21-18 that the On Click Macro button is green, indicating to the form designer that a macro has been assigned to this particular action.

Figure 21-18

Figure 21-18 The Action property of the arrow graphic is selected.

When you select the On Click button, you will be taken into the Macro Designer in Access. As you can see in Figure 21-19, the action macro in this case performs two quite simple tasks. First, it saves the record when the user selects the arrow. This eliminates the need for the form to ask the user to save the changes before moving to the next view. The macro will perform the Save function immediately on behalf of the user when the user clicks the arrow. Second, the macro performs a ChangeView action to take the user to the Formats view when the arrow is clicked.

Figure 21-19

Figure 21-19 The macro is instantiated when a user clicks the arrow graphic in the Site Request form.

View 2 (Formats)

As the user progresses to the second view of the form (Formats), the form design approach selected on this view was to simply present the user a series of check box controls to select one or more record formats that will be stored in their SharePoint site. Each of the check boxes will store a Yes or No value as the user fills out this view. You can see in Figure 21-20 that the check boxes are connected to their respective fields in the database schema via the Data properties for each control.

Figure 21-20

Figure 21-20 The check boxes are connected to their respective fields in the database schema via the Data properties for each control.

One other design tactic worth noting on this view is that the entire view itself has a macro that is instantiated immediately when the view is opened by the form user. The purpose of this On Load macro is to ensure that the form is in Edit mode when the user is moved into the view. If we did not add this macro, the user would have to click the Edit button from the Action Bar—an unnecessary step.

In order to see a macro on a view when in Design mode, you need to click anywhere with some empty space (not on a control) in the view. As shown in Figure 21-21, this will enable the properties for the view. The macros again are under the Actions properties.

Figure 21-21

Figure 21-21 An action macro can be applied to the entire view.

View 3 (Questions)

The Questions view, shown in Figure 21-22, is the third view that the user encounters when filling out our simple Site Request form. The purpose of this view is simply to ask the user four questions about the nature of their records content and display extra information about each selection the user makes.

Figure 21-22

Figure 21-22 The third view of the Site Request form is the Questions view.

Similar to the Formats view, the Question view of the form has a macro that runs when the form initially loads. The purpose of this macro, as shown in Figure 21-23, is to ensure that the extra information for each question is set to be hidden. So if the respective item’s selection is not equal to 1 (true), then hide the detailed information. This helps reduce clutter when the view is initially displayed to the user.

Figure 21-23

Figure 21-23 The Form Load macro for the Questions view ensures that the extra information for each question is set to be hidden.

As you just saw, when the Questions view initially loads on a new record, the extra information about each question is hidden until they select it. Once selected, however, then another set of macros (which are tied to each check box’s After Update property) take over. The purpose of these macros (see Figure 21-24) is to toggle the extra detailed information on or off, depending on whether the form user has selected that particular item.

The result is that users get a very clean form that only displays extra detail about each question if they need it. If after reading the details they decide they don’t need that item checked, they can simply clear it, and the details disappear because the macro returns the Visible value to False.

Figure 21-24

Figure 21-24 The After Update macro is connected to the HIPPA check box control in the Questions view of the form.

View 4 (All Records)

The final view of the form is simply an Access List view that has had the data fields rearranged on it slightly to make it more usable and aesthetically pleasing (see Figure 21-25). From here, the user can easily navigate or search for any of the forms, and then, using the Action Bar, modify the form if necessary. In many form scenarios, this wouldn’t be the approach you’d take on the final view, but this is a nice example of using the built-in Access List view to give users a convenient landing spot once they’ve added a new record.

Figure 21-25

Figure 21-25 The All Records view of the form allows the user to select different records and see the details of a particular site request easily.