Creating a web app in Access is much like creating a desktop database. You can create a web app from the welcome page, which appears when you first start Access, or by choosing File→New if you’re currently working on another database. Either way, you’ll see a list of templates that you can use to create new web apps and desktop databases. To create a web app, click the “Custom web app” template instead of “Blank desktop database.”

When you click “Custom web app,” Access asks you for two key details (Figure 20-1):

Figure 20-1. Every new web app needs a name and a home. The web location points to the SharePoint site that will host the web app.

Once you’ve filled in these two details, click Create. At this point, Access will ask for your login credentials (Figure 20-2).

Figure 20-2. Depending on how you’re hosting your web app, Access may ask you what type of account you’re using (left). Choose “Microsoft account” if you’re using Office 365, or “Organizational account” if you’re using a SharePoint server provided by your company or another SharePoint hosting company. Either way, the next step is to fill in your user name and password (right).

Once you’ve provided the right user name and password, Access creates the web app. It will take a few seconds to get the job done, because Access needs to communicate with the SharePoint server while it creates the web app.

Once Access is finished creating your web app, it opens it in the main Access window. Skip ahead to Adding a Table to learn how to add your first table.

It’s just as easy to create a web app using the SharePoint web interface. Here’s how:

Figure 20-5. Here, the file Boutique Fudge.accdw has just been downloaded to the download tray in the Chrome web browser. When you open the file, Access will spring into action.

The first thing you’ll notice when you create a new web app is the startlingly stripped-down Access interface. The ribbon has just a single tab, named Home. Underneath is the web app’s home tab, where you begin creating your first table (Figure 20-6).

Figure 20-6. Here, the home tab is named Boutique Fudge, because that’s the name of the web app you just created. Inside the tab are a number of links for creating new tables, including the useful but somewhat hidden “add a new blank table” link (circled).

Access gives you three ways to add a table to a web app:

The best way to get started is to create a blank table from scratch. To do that, click the “add a new blank table” link shown in Figure 20-6. Access loads up a new tab for your table in Design view, so you can add the fields you want (Figure 20-7).

Figure 20-7. The Design view that you use to create a table in a web app closely resembles the design view that you use in a desktop database.

Initially, you’ll start with just one field: an AutoNumber field named ID, which uniquely identifies each row in the table. You’re already in the habit of adding an ID field to your own tables, but web apps require this best practice. Every table in a web app includes an ID field, and you can’t delete or modify it.

As you add more fields, you’ll find that you can choose from a similar set of data types as when creating a desktop database. However, because your data is actually stored in a SQL Server database, the data types need to play by the rules of SQL Server. This requirement introduces a few subtle differences—for example, the Attachment data type is replaced with the less-powerful Image data type, and the old-fashioned OLE Object data type is gone altogether. You’ll also notice that the list of field properties you can customize for each data type is far smaller than with a desktop database. For example, you can’t use the nifty custom format string feature (Currency) for any data type.

Table 20-1 details the most important difference and tells you what data type SQL Server is really using to store each type of data, behind the scenes.

As with a desktop database, you can write field validation rules that reject bad data (Validation Rules). You can also write data macros that perform more complex error checking. Macros work essentially the same as in a desktop database—you check for an error with the If action, and then cancel the insert, update, or delete operation with the RaiseError action, as explained on Validation with Before Events. The chief difference is that you use the On Insert, On Update, and On Delete data macros in a web app, while a desktop database performs the same logic with the Before Change and Before Delete data macros.

Once you’ve finished entering all the fields for your table, it’s time to save it. Press Ctrl+S or click the Save icon the Quick Access toolbar. Then, enter a name for your table and click OK.

When you save a table in a web app, Access communicates with the SharePoint server and pushes your new creation online. After a slight delay Access shows its friendly “Hang on while we save your changes message.” Once the process is complete, your app will be live on the SharePoint server. You (or anyone else) can now log into SharePoint and start entering data.

As with an Access desktop database, you can use Datasheet view to add or edit the records in your table. To switch into Datasheet view, right-click the tab for your table (for example, the Customers tab) and choose Datasheet View. Or, you can use the familiar View button in the ribbon. Either way, once you switch to the Datasheet view, you’ll notice that it doesn’t look quite the same as it does for a desktop database (Figure 20-8).

Figure 20-8. The Datasheet view for a web app has almost all the same features as the Datasheet view for a desktop app. However, some of the details have been restyled and rearranged.

Now’s a good time to try adding a record. You’ll find that the web app datasheet behaves much like the datasheet for a desktop database. Here are some examples:

One notably missing feature is the ability to search for a record. But as you’ll see later, web apps have another way to accomplish that (Editing Data in a Web App).

Given that the features of the Datasheet view are so similar in web apps as they are in desktop apps, you might wonder why the Datasheet view looks so different. The reason is that the Datasheet view in a web app is actually a piece of a web page. When you edit your table online, you use the same grid of records, with all the same buttons and features, except your interaction takes place inside your favorite web browser. (You’ll see this happen a bit later, on Running Your Web App.)

This minor miracle works because of a subtle shift in the way the Datasheet view is created. In a desktop database, Access creates the grid of records. But in a web app, SharePoint generates the Datasheet view. It uses a mixture of HTML markup and some carefully crafted JavaScript code to simulate the desktop editing experience. In fact, even when you’re working with your web app inside Access, exactly the same process is taking place—SharePoint is creating the Datasheet view, and Access is simply showing that bit of web content inside its main window.

Every web app has a built-in navigation system. As you add tables, your web app creates a menu that includes a link to each one. SharePoint shows this menu on the home page for your web app. Access shows what this menu looks like on the web app’s home tab—the same place where you started out before you added your first table.

Initially, the home tab showed the helpful Add Tables page (Figure 20-6). But now that you’ve added a table, Access uses the home tab to show the home page of your web app (Figure 20-9).

Figure 20-9. When people use your web app online, they’ll start out at a page that looks like this. On the left is a menu that lets them jump from one table to the next. Right now there’s just one table, which is named Customers. On the right are the controls that let them edit the table’s data.

Few databases have just a single table, and web apps are no exception. To add a new table, choose Home→Create→Table from the ribbon. This action doesn’t actually create a table; instead it takes you back to the home tab and loads up the Add Tables page that you used earlier. Now you can click the “add a new blank table” link to create another new table, which Access will open in another new tab. Each time you create a new table, Access adds it to the list of tables, which it shows on the left of the home tab.

As you’ll soon see, you can change the menu on your web app home page, and you can change the controls it uses to let people edit data. But before you start changing the web app home page, you need to get a better feel for how it works, and to do that you need to view your web app in a browser. The next section shows you how.

At first glance, the home page may seem a bit puzzling. However, it makes more sense once you break it down into the series of steps you follow when you use a web app. Here’s how the process unfolds:

When you create a new table, Access creates a List view and a Datasheet view to help you edit it. If you choose to do your work in the Datasheet view (Figure 20-12), you won’t have any trouble interpreting its compact grid of records. In fact, you’ve already seen this view in Access, where you used it to add the first record to your table (Entering Data in a Table). Using the Datasheet, you can edit, delete, and create records, and you can resize and rearrange columns. If you hover over a column header and click the drop-down arrow, you get a menu that lets you apply basic sorting and filtering.

If you choose the List view (Figure 20-13), you may need a bit of help finding your way around. List view lets you view and edit just one record at a time. That means it can present the data more effectively, especially if the database contains pictures, long text descriptions, or a huge number of fields. To pick which record you see, you find it in the list, which runs along the left side of view. To add, edit, or delete records, you use the icons that appear just above the record data (Figure 20-14).

Figure 20-13. List view consists of a two parts. Running down the left side is a list that lets you select a record. On the right are the editing controls that let you view and edit the current record.

Figure 20-14. Left: The List view shows the data for the selected record. Right: Click the Edit icon, and the record switches into edit more. The text fields turn into text boxes and SharePoint provides a handy drop-down date control.

As you know, you can add as many tables as you want to a web app; you simply choose Home→Create→Table and then click the “add a new blank table” link. Every time you create a table, Access adds a matching link to the navigation menu on the left side of the home page. So if you add three tables to your web app, Access creates three links, and the person using your database can skip from one table to another by clicking these links.

You can also use the navigation menu when you’re editing your web app in Access. On the home tab, right-click the table you want to work with. Access gives you several options: Choose View Data to open a Datasheet view on your table; use Edit Table to open the table in Design view; or click Delete to remove the table altogether.

You can customize the navigation menu, but only slightly. If you yearn to give it a new look, move it to a new place, or change the way it works, you’re out of luck. But here are several less dramatic but still practical changes you can make to your navigation menu:

Figure 20-15. To change the icon that appears next to a menu item, click the Formatting icon (it looks like a tiny paint can and paintbrush).

At this point, you might be wondering what happened to the traditional navigation pane, which shows all the database objects in a desktop database. When you create a web app, Access hides the navigation pane, to prevent less experienced Access users from confusing it with the navigation menu. However, you can show the navigation pane whenever you need it by choosing Home→Show→Navigation Pane (Figure 20-16).

One reason to reveal the navigation pane is so you can see, at a glance, all the views that Access has created for your web app, without browsing through every table in the home tab. You can also use the pane to rename a table. (When you right-click the table in the navigation menu and choose Rename, you rename the navigation link, not the table.) And there are other good reasons to call up the navigation panel, like viewing the queries you’ve created and finding views and macros that aren’t linked to any table. You’ll use the navigation panel later in this chapter when you create a filtering query (Views That Use Queries).

Figure 20-16. The navigation panel shows all the objects that are currently in the Boutique Fudge database: two tables and the four views that Access creates automatically to let people edit these tables. Oddly enough, Access calls them forms in the navigation pane, even though it calls them views everywhere else.

To modify a view, you need to open it up in Access. Here’s how:

Editing a view in a web app is like editing a form in Layout view, but simpler. There’s no Property Sheet of settings, for example, and there’s no way for you to actually see the layout that Access is using. Instead, Access creates the layout automatically as you move your fields around.

The best way to understand how view editing works is to give it a whirl. Here are a few basic tasks to try:

Figure 20-18. Here’s what happens if you click the LastName label and drag it downwards. As you move the control, Access bumps everything else out of the way. It won’t let you overlap controls, although you can place them unevenly, so their left and right edges don’t line up.

Rearranging controls is the simplest improvement you can make to any view. You can do helpful things like putting related fields (say, FirstName and LastName) close together, and enlarging image fields and text fields so you have enough room to read long text and view big pictures.

When you get tired of moving the controls around your view, you might want to start changing them. Access doesn’t provide anywhere near the level of control-customization in a web app view as it does in a desktop database form, but there are still plenty of useful modifications you can make. Here are two of the most basic:

Every control also has a small set of configurable settings that are tucked out of view. To see them, start by clicking the control you want to change. You’ll see three tiny icons appear next to it (Figure 20-19):

Figure 20-19. Top: When you click the control for the FirstName field, you see three tiny icons. Bottom: Click one of them to pop open a panel with additional settings.

One of the most useful ways to improve a view is to add some user guidance, which you can do via tooltips and input hints. Tooltips are the brief pop-up messages that explain an onscreen item. You can use them, for example, to provide more details about what kind of information a field should hold. An input hint is the text that SharePoint displays in a text box when the corresponding field is empty and awaiting some data. You can use an input hint to suggest the proper format for a text value, as Figure 20-20 demonstrates (bottom). To add both these details, select a field, click the Formatting icon, and then type some text in the Tooltip and Input Hint boxes.

Figure 20-20. Top: You configure tooltip and input hint using the Data icon. Bottom: The input hint appears when the text box is being edited, if the field is empty. The tooltip appears when the mouse moves over the text box.

The list in List view is a special case. It’s a control that does double-duty: not only does it display a list of values, but it also lets you jump from one record to another by clicking those values.

When Access creates the List view for a table, it grabs the first field (not including the ID field), and uses that to fill the list. As a result, the list may not have enough information to properly identify each record. For example, it may show the FirstName field, even though first names aren’t unique and there may be many people with the same first name in your table. The list may even use a field that has blank values, in which case you’ll need to click the blank spaces in the list to load those records. Clearly, this situation is far from ideal.

To configure the list control, select it and click the Data icon. (This is the only icon that appears, because you can’t change the appearance of the record-selection list or react to its events.) When you click Data, you’ll see a panel with several useful settings, including Primary, Secondary, and Thumbnail (all of which set the data that appears in the list) and Sort Field and Sort Order (which control how the items in the list are ordered).

There are three different approaches you can use to improve your list:

Figure 20-21. To improve your list, add a secondary field (left) or use an expression (right).

Once you make sure the list includes the data you want, you can set the Sort Field and Sort Order settings to make sure your list puts its values in the right order. The Sort Field is the field Access uses to sort the list (like LastName) and Sort Order lets you choose between an ascending or descending order. To avoid confusion, you should sort on the same field that you display. For example, if you show last names first, it makes sense to sort on LastName, whereas if you show first names first it’s clearer to sort on FirstName. If the sort order isn’t obvious by looking at the list, the people using your view may assume the list isn’t sorted at all.

As you may expect, you can easily add or delete the controls in a view.

To delete a control, click it and then press the Delete key. Alternatively, you can hide the control. That way it isn’t visible (or usable) online, but you can unhide it if you want to add it back in the future. To hide a control, select it, click the Formatting icon, and change the Visible setting to Hidden.

Access gives you two ways to add a control. The quickest approach is to drag the field in from the Field List panel. Access adds a label for the caption and adds a control that matches the field type. For example, you get a text box for a field with the Short Text data type, a hyperlink control for a field with the Hyperlink data type, a checkbox for a Yes/No field, and so on.

The other way to add a control is to double-click the exact control you want in the View | Design→Controls section of the ribbon. Access then adds it to your layout. You’ll need to drag it to the right place, and (optionally) connect it to a field in your database. To do that, select it, click the Data icon, and pick the field you want to use.

Now that you know how to add and delete controls, you also know how to replace one control with another. It’s simply a matter of deleting the control you have now, adding a different control to take its place, and connecting it to the right field. Strange as it may seem at first, there are several good reasons to replace a control. Here are some examples:

Using the techniques you’ve learned up to this point, you can polish a view into sparkling form. You don’t have the fine control of a desktop database for things like exact grid placement and heavy formatting, but you do have enough to get by, most of the time. Figure 20-22 shows a customized view that uses many of the tricks you’ve considered.

Figure 20-22. This tidied-up view enlarges the picture and the description, renames many fields, and makes the most important information bigger and more prominent. It also removes the unnecessary label for the picture field, and adds a new label with copyright text at the bottom.

To add a new view, follow these steps:

Here’s a novel use for a custom view: creating an introductory message that welcomes people to your web app.

Right now, new visitors begin in the thick of things. SharePoint loads up the views for the first table in the navigation menu, creating a dense display that can be dizzying at first glance. Compare that to the web app in Figure 20-24, which uses a special screen to explain what’s going on.

Figure 20-24. New visitors will appreciate a welcome page explaining what to do, although it forces experienced users to waste a click getting to the table they want.

Custom views make this frill possible, although it’s a bit more work than you’d expect (and much more awkward than in a desktop database). The following list of steps takes you through the whole process:

Custom views become more interesting when you combine them with another database ingredient—queries. Your query can filter out specific records, join tables together, and use expressions to manipulate individual fields. Your view shows the data that the query retrieves.

For example, consider the records in the Orders table from the desktop version of the Boutique Fudge database. You may want to create a view that shows only those orders that are currently in progress. On its own, a view can’t accomplish this feat. But with a query, it’s Access 101. You simply need to write a filter condition that matches records that have a status of In Progress.

To create a query, choose Home→Create→Advanced→Query. Access opens the familiar query designer, where you can create your query in the formal way, by picking fields and typing in your filters (Figure 20-25).

Figure 20-25. This basic query matches all the orders that have the In Progress status.

When you’re finished creating your query, press Ctrl+S to save it or close the query tab, in which case Access will prompt you to save changes. Choose a descriptive name for your query, like InProgressOrders.

The web app query designer looks strikingly similar to the query designer in a desktop database. As with a desktop database, you can create a query that groups data, calculates totals, joins tables, requires parameters, and uses expressions. However, certain features are missing, such as action queries, Datasheet view (for testing a query), and SQL view (for editing the underlying SQL command).

Queries don’t appear in the home tab. If you want to edit a query, or just reassure yourself that it’s stored in your database, you need to show the navigation pane by choosing Home→Show→Navigation Pane.

Once you’ve created a query, you can use it as the basis for a new view. Follow the normal instructions for creating a view (Creating More Advanced Views), but in the Record Source setting, choose the query you just created. Using the query from Figure 20-25, you can create a list-style view that lets users review and edit in-progress orders.

Web apps are surprisingly adept at dealing with table relationships. If there’s a relationship between your tables, Access pays attention, and uses that relationship to create a better view (Figure 20-26).

Figure 20-26. If your table is the parent in a one-to-many relationship, Access shows related items in the automatically generated List view. Here, the List view for the Orders table shows all the items in the current order, by getting the related records from the OrderDetails table.

Figure 20-27. You don’t need to tell Access what field to use in the parent table. Every table in a web app includes the unique ID field, so Access uses that automatically. However, you can choose what value Access displays in the lookup, which is handy if you want to show a customer name, say, instead of the customer ID. (Sadly, you can’t use an expression that combines more than one field.) Finally, the setting at the bottom lets you choose how you want to enforce referential integrity (page 173).

To show related records, Access uses the Related Items control—a new control that’s available only in web apps. And although Access’ automatically created List views make good use of the Related Items control, you’ll often want to tweak your views to improve the way your data is displayed. Here are some reasons to crack open a view and fine-tune the Related Items control:

To try out these techniques, open a view that uses the Related Items control, like the List view for the Orders table. You’ll see the Related Items control, which looks like a big blank box (Figure 20-28).

To fine-tune the settings for the Related Items control, start by clicking the heading inside it, which corresponds to the child table. In the orders example, the heading is named OrderDetails. Three icons will appear.

Figure 20-28. When you click the OrderDetails page inside this Related Items control, you’ll see three additional icons that allow you to configure it, named Data, Formatting, and Calculation.

Out of the three icons, the Data icon is the most important. It lets you choose the table that the Related Items control uses, and the fields that it shows (Figure 20-29). The Formatting tab lets you change the page title (for example, you could rename OrderDetails to “Order Contents”). The Calculation tab lets you pick a field that will be added, counted, or averaged—the result is displayed at the bottom of the list of related records. Unfortunately, the Calculation tab isn’t nearly as useful as it seems, because it doesn’t support expressions. That means there’s no way to calculate the total cost of an order, for example, by multiplying the cost and quantity of each item.

Figure 20-29. For each page in the Related Items control, you choose the table, you specify how it’s related, and you choose up to four fields that will be shown. Optionally, you can give those fields a new caption, if the field name isn’t clear enough. Don’t worry about the “Popup view” setting for now—you’ll consider that on page 716.

If you don’t need four fields, it helps to be strategic about where you place your fields. Each field is given the same fixed amount of space, so if you have a long value, you can give it more space if you don’t use the slot that follows it. The example in Figure 20-29 uses this technique to give extra space to product names (the first field) by leaving the second field unoccupied.

Adding Child Records

The Related Items control has another trick in store. While you’re viewing a parent table (like Orders), you can use the Related Items control to add a record to the child table (in this case, OrderDetails). The secret is the tiny link that appears at the bottom of the Related Items control. You’ll recognize it because it starts with the word “Add” (like “Add OrderDetails” in Figure 20-26).

When you click the record-adding link, the Related Items control opens a popup window with a new view that lets you add a record to the child table (Figure 20-30).

Figure 20-30. Click the “Add OrderDetails” link and Access pops open a basic view that you can use to add new order items. This view lacks some of the convenience you’d get in a desktop database (for example, when you pick a product it would be nice to fill in the corresponding price automatically, as you did on page 620), but it gets the job done.

By default, Access uses the List view for the corresponding table. So if you click Add OrderDetails, you’ll see the OrderDetails List view. However, you can choose a different view by configuring the Related Items control and changing the “Popup view” setting (Figure 20-29).

This convenience does a lot to centralize the functionality in a web app, and it reduces the number of times you need to hop around from view to view while you’re editing data. In the current example, it lets someone add a new order and fill out all the linked order items in one place, rather than having to edit two separate tables.

Access pros often create a new custom view—one that’s not explicitly linked to any table in the navigation list—to use with the Related Items popup. Choose Home→Create→Advanced and pick Blank View or List View. Once you save the view, you’ll be able to pick it in the “Popup view” setting. If you need to edit or delete the view later, you can find it in the navigation pane (choose Home→Show→Navigation Pane).

Adding Parent Records

Web apps introduce a second new control that simplifies editing with related tables: the Autocomplete control.

The Autocomplete control takes the place of the traditional Combo Box control. In a desktop database, you see plenty of Combo Box controls, because they let you pick a parent record when editing a child record. For example, you might use a Combo Box to choose the product for a given order item, as shown in Figure 20-31.

Web apps also support the Combo Box. But when Access creates a view, it assumes you would prefer to use the new Autocomplete control, which works a little differently. In the Autocomplete control, you begin typing part of text from the item you want to match (in this case, the product name). As you type, SharePoint shows potential matches, which you can click to select. But if you don’t find what you want, you can make use of an additional option, “< Add a new item >,” which appears at the top of the list (Figure 20-31). Click that, and SharePoint opens another popup view where you can add the parent record you want.

Figure 20-31. Top: In a Combo Box control, you pick the product from the Products table. Bottom: In an Autocomplete control, you can choose a product that matches what you’ve typed. Or, you can add a new product to the Products table, on the fly.

In some cases, you may not want to use the Autocomplete control that Access has given you. Perhaps you worry that it may lead novice users to accidentally create new products rather than pick the one correct record that already exists in the table. The solution in this case is easy—edit the view, delete the Autocomplete control, and add a Combo Box control in its place.

If you already have the perfect set of tables in a desktop database, there’s no need to recreate them by hand in a web app. Instead, you can use a nifty import feature to transfer their tables to your web app, with or without their data. Obviously, some tables can cause problems (for example, ones that use the unsupported Attachment data type) and other database objects, like queries, forms, and reports, won’t come along for the ride. But overall, the database import feature works surprisingly well.

To import the tables from a database, follow these steps:

The Cacophoné Studios database uses several many-to-many relationships. For example, there’s a many-to-many relationship between teachers and classes—one teacher may teach several classes, and the same class may be taught by more than one teacher. Similarly, there’s a many-to-many relationship between students and classes, because one student can be enrolled in multiple classes and a single class holds a number of students.

To create many-to-many relationships, you create a junction table that sits between the two tables you want to link (Many-to-Many Relationship). For example, to link teachers and classes, you can create a table named Teachers_Classes. Each record pairs up one teacher with one class. (To review how junction tables work in closer detail, and to review all the relationships in the Cacophoné Studios database, refer to Relationship Practice.)

The problem with many-to-many relationships is that they confuse the Related Items control. It displays the related records from the junction table, but it doesn’t get any of the information from the table that sits on the other side of the relationship. That means if you’re looking at a teacher record, you’ll see a list of all classes taught by that teacher, but you won’t see the rest of the class information, such as the start date, class description, and so on (Figure 20-33).

Figure 20-33. The List view for the Customers table indicates that Donald teaches four classes. Thanks to the lookup that binds the Teachers_Classes records to the Classes table, you can see the name of each class. However, other information is off limits, because it’s part of the Classes table, not the Teachers_Classes table.

There’s no way to solve this problem by using the Related Items control, because you can’t control what data it shows. However, there are two approaches that work around this problem:

Either way, you follow essentially the same steps. First, you create a query that fetches all the data you want to display (not just the limited details that the Related Items control shows on its own). Second, you create a view that uses your query. Third, you show your view as a popup or with the help of the Subview control. This example uses the popup approach, which requires slightly more work but provides a nicer viewing experience.

Creating a Query to Get Related Data

The first step is to create a query that gets the data you need. This query needs two join two tables: the junction table (like Teachers_Classes) and the table on the other side of the relationship (Classes). You use the Teachers_Classes table to get the TeacherID field, and you use the TeacherID field to filter out the classes that belong to a specific teacher. Then, you use the Classes table to get the class data that you want to show.

Creating a query like this is a quick bit of work. Just choose Home→Create→Advanced→Query, add the two tables you need, and choose the fields you want to show (Figure 20-34). Don’t worry about the filter criteria yet—that part will be set by your view. Instead, save your query with a name that communicates its role (like ClassesByTeacher).

Figure 20-34. This straightforward query links the Teachers_Classes table to the Classes table in order to get the class information.

Creating a View for Your Query

The next step is to build a view that uses this query, so you can show your data when you need it. You don’t want this view to be linked to any of the tables in the navigation menu, so you should create it using the Home→Create→Advanced→List View command.

Initially, your view is empty, but you can quickly attach it to your query (Figure 20-35). After that it’s simply a matter of dragging the fields you want to show from the Field List pane onto your new view, and arranging them. Save your view with a name that matches the query (like ClassesByTeacherView).

Figure 20-35. To set the data for a new view, click the outer box that wraps around the empty view, and then click the Data icon. You can choose your newly created query from the Record Source list.

Now it’s time to return to the original view that started this odyssey. In this example, it’s the Customers List view. Delete the Related Items control, so you have room to implement your improved solution.

You can then put your newly created view to work in a Subview control or using a popup window, as explained in the next section.

Showing Your View in a Popup

To show your view, you need two ingredients that work together: a button and a macro. The button sits on your form, waiting for someone to click it. When it’s clicked, the button triggers your macro, which opens a popup window and shows ClassesByTeacherView.

Adding a button is easy: it’s one of the small set of controls you can find in the View | Design→Controls section of the ribbon. Once you’ve added the button, click the tiny Actions icon to show the events you can react to. In the case of a button, there’s just one: the On Click event that fires when the button is clicked (Figure 20-36).

Figure 20-36. When clicked, this button will load a popup window that shows the classes the current teacher is teaching. Click the On Click event to begin writing the macro that does the job.

The final step is to create the macro that shows the popup window. Creating a macro in a web app is identical to creating one in a desktop database, except you have far fewer commands to work with. In this case, you need to use the OpenPopup command, and specify two arguments. The first argument is View, the view that you want to open (in this case, that’s ClassesByTeacherView). The second argument is Where, which sets a filter condition that cuts the results down to just the matching records. What’s needed here is an expression that matches the current teacher ID (provided by the ID field) with the TeacherID that’s stored in the Teachers_Classes table and returned by the ClassesByTeacher query. Here’s the expression that does the trick:

TeacherID=ID

Figure 20-37 shows the finished macro.

Figure 20-37. This brief macro powers the “View Classes for This Teacher” button shown in Figure 20-36.

This example is notable because it takes you through all the features of a web app. You create a custom query that’s shown in a custom view, and you call that custom view into action with a macro. The end result is a polished experience, and a decent impersonation of an Access database in a browser.

Note

This example suffers from one limitation. Because of the join, SharePoint won’t let you edit the class information. If you need to make changes, you need to go directly to the Class table and use one of its views.

POWER USERS’ CLINIC: Opening Up Your Database to the World

At the beginning of this chapter, you learned that one of the advantages of the web app model is that it uses a standard SQL Server database, which means custom written programs and all-purpose reporting tools can connect directly to your database. Now that you’ve reached the end of your web app exploration, one question remains: how do you do it?

To let other programs talk to your database, you need to take two steps. First, you need to tell SharePoint that it should allow this sort of direct connection. (Otherwise, it will refuse to let anyone talk to the database, unless they go through the web app.) Second, you need to get the connection details that will tell that other piece of software how to find your database on the Internet.

You can take care of both details by choosing File→Info and clicking the big Manage button. It pops open a large menu of options ( Figure 20-38).

First you need to decide whether you want to allow people to connect to your SQL Server database from any computer or just from the current computer (where you’re working right now). Depending on your choice, choose either From My Location or From Any Location in the menu.

Next, you need to allow connections. You can tell SharePoint to allow read-only connections (which let you see data but not make changes) by clicking Enable Read-Only Connection. Or, you can tell SharePoint to allow full connections that can read and change data by clicking Enable Read-Write Connection.

Now you need to collect the connection information. Choose either View Read Only-Connection Information or View Read-Write Connection Information to open a new window that has all the information a SQL Server expert needs, including the server’s network address, the database name, and the user name password. All of these details are computer-generated, so you’ll need to copy the strings of gibberish-like characters to a text file or email message.

If at some point you decide to lock down your database, you can choose Disable All Connections. Or, if you want to continue allowing connections but you want to generate new passwords, so no one can gain access to your database with the old information, click either Reset Read-Only Connection Password or Reset Read-Write Connection Password.

Figure 20-38. The Manage button has a slew of options that let you control who can talk directly to your SQL Server database.