Creating a Web Database with Access 2013
In this chapter you’ll create a web database using Access 2013, which will be published to SharePoint Online in your Office 365 platform. This database implements a simple job tracking application that allows you to create, estimate, and track customer jobs. Publishing a special type of Access database, called a web database, is probably the easiest way to create a form-based SharePoint site. In this chapter you will create a fully working website with numerous tables and forms (views).
Out of necessity, I will cover this briefly without explaining every detail as much of it is fairly repetitive. You can also download the complete Access database from www.apress.com and then follow along as you read this chapter.
You will implement this application using the typical approach of designing the tables first. Then you’ll add a few queries that will simplify some of the form development. You’ll also create a fairly complex data macro that will compute the estimated job total by adding up the products and services included. You will then customize the forms for each of these tables. Finally, you’ll implement a custom form for displaying a summary of all of the jobs.
Designing the Tables
Start Access 2013 and create a new database using the Custom web app template. When using this template, Access will create new objects such as tables and forms that are compatible with SharePoint. You’ll be prompted to enter a name for your app as well as the web location where this will be deployed. Enter JobTracking and the address to your Team Site as shown in Figure 5-1.
Figure 5-1. Setting up the Access app
Access will then connect to your SharePoint site and setup your new app. This includes provisioning a SQL Server database on Azure and may take several seconds to complete. The initial, empty, database will look like Figure 5-2.
Figure 5-2. The initial Access app
Access 2013 provides several ways to get started building your database. You can search through the available table templates or you can import a data structure from an existing source such as previous versions of Access, a SQL Server database, or a SharePoint list. For this exercise, you will create your own custom tables.
Note You can import the table definition from Access 2010 into Access 2013, but the forms cannot be imported; you will need to recreate them in Access 2013.
Data Model Overview
The logical starting place for creating a web database is to design the tables. You will need the following tables to support the job tracking application. Figure 5-3 shows how these tables are related.
Figure 5-3. Data model diagram
Addresses and phone numbers are stored in separate tables, Address and Phone, which allows each customer to have more than one. The Job table also references the Address table to indicate the location of a particular job. The Contact table records correspondence with a customer.
A job is a collection of products and services. The Product and Service tables provide static information about each product or service such as description and price. The JobProduct and JobService tables are used to indicate when these are included in a specific job and provide job-specific details as well.
Creating the Customer Table
You’ll start by creating the Customer table. Click the Add a new blank table link in the initial Table page, shown previously in Figure 5-2. This will create a blank table as shown in Figure 5-4.
Figure 5-4. Starting with a blank table
Note In Access, the terms column and field can be used interchangeably. You will see field being used most often so we will follow that convention and refer to both columns in a table and fields on a form as fields.
Click the ID field and change the field name to CustomerID. The next field will indicate the current status of this customer, such as Prospect or Active. To add another field, click in the first empty cell and enter the field name, CustomerStatus. In the Data Type field, select the Lookup link as shown in Figure 5-5.
Figure 5-5. Adding a Lookup field
Defining Static Lookup Values
The Lookup Wizard will then appear and it has two options for providing values for a lookup field. The first is to get the values from another table. This will create a foreign key relationship between the tables. The second option is to specify a static list of allowed values. For this field, you will use the second option; select this in the dialog box.
Then, enter the following values as illustrated in Figure 5-6:
Figure 5-6. Specifying the CustomerStatus values
Tip If you need to later adjust this list of values, you can display the Lookup Wizard again by clicking the Modify Lookups button in the DESIGN tab of the ribbon.
Add the following additional fields to the Customer table:
Click the Save button in the title bar to save the table design. Enter the table name Customer when prompted. The completed table should look like Figure 5-7.
Figure 5-7. The completed Customer table
Creating the Phone Table
The Phone table contains customer phone numbers. By placing these in a separate table, each customer can have more than one. The Phone table will have the following fields:
To add a new table, go to the HOME tab of the ribbon and click the Table button. This will take you to the initial page that was displayed when you first created the Access database. Click the add a new blank table link again to create a new blank table just like you did for the Customer table and rename the ID field to PhoneID.
Creating a Foreign Key Relationship
For the next field, enter the name Customer, and then select the Lookup data type. This time, in the Lookup Wizard, select the first option, to get the values from another table. The dialog box will then list the existing tables and views for you to select the source. The Customer table should be the only one listed; select it.
You’ll then have several other options for configuring the table relationship. First, when displaying a record from the Phone table, you need to select which field in the related Customer record should be displayed. Select the CustomerName field.
Then you need to decide how deletes are handled. The first option is to prevent a delete from a table if there are other records referencing it. The second option is to cascade the delete, which means that when a Customer record is deleted, all related Phone records are also deleted. The third option will allow these orphaned Phone records. Select the first option. The completed dialog box will look like Figure 5-8.
Figure 5-8. Setting up a foreign key relationship
Creating the Remaining Fields
Add a new field named Status and select the Lookup data type. Select the first option and enter the following list of values:
Then add a PhoneType field using the Lookup data type with the following list of values.
After creating the Status field, you should provide a default value. Select this field and enter “Active” in the Default Value property as shown in Figure 5-9. This will default all new records to the Active status.
Figure 5-9. Specifying the Default Value
Finally, add the PhoneNumber field and select the Short Text data type. Click the Save button in the title bar and enter the table name Phone when prompted. The completed Phone table should look like Figure 5-10.
Figure 5-10. The completed Phone table
Viewing the App in SharePoint
When you created the Access database, the template automatically created the app in your Team Site. Also, a SQL Server database was provisioned in Azure. As you defined tables, these were created in SQL Server. All of this happens behind the scenes as you edit your Access database. (This is why saving may seem a little slow.)
To demonstrate the integration, let’s go to your Team Site and look at the JobTracking app. You’ll find this in your Apps in Testing list. The initial page will look like Figure 5-11.
Figure 5-11. The initial app page
At this point the tables are empty so let’s insert some data. Select a CustomerStatus from the dropdown list and enter a name and e-mail address. Then click the Add Phone link, which is underneath the empty list of phones. A pop-up window, shown in Figure 5-12, will appear for you to enter the phone details.
Figure 5-12. Adding a Phone record
Notice that the Customer field is already pre-filled using the CustomerName field from the Customer table and the Status was defaulted to Active. Enter some data, click the Save icon and close the dialog box. The completed Customer record will look similar to Figure 5-13.
Figure 5-13. The Customer form with Phone records
Tip In previous versions of Access, even with web databases, Access was used to edit a local (.accdb, or .mdb) file. With web databases, this could be later published to a SharePoint site and also downloaded from SharePoint. With Web apps in Access 2013, there is no local file, Access is communicating directly with SharePoint and SQL Server. When you close Access and then want to later make additional changes, you’ll need to go to the SharePoint site. From the Settings menu, select the Customize in Access link. This will launch Access 2013 and open your app.
Now you’ll define the remaining tables. To add a new table, you’ll need to go to the HOME tab on the ribbon and click the Table button.
Creating the Address Table
The Address table is similar to the Phone table with a foreign key relationship with the Customer table. Create a new table with the following fields:
I added a default value for the Country field so the users would not have to enter this. You can choose to leave this without a default value or enter a different value as appropriate. Save the table and enter the name Address when prompted. The completed Address table should look like Figure 5-14.
Figure 5-14. The completed Address table
Creating the Product and Service Tables
Next you’ll create the Product and Service tables that will store static information about the products and services that can be included in a job or job estimate. You’ll create these much like the previous tables.
The Product table will contain the following fields:
The Service table will have the following fields:
Tip To define a default value for a boolean Yes/No field, enter the expression as Yes or No.
Creating the Job Table
The Job table will contain the proposed, active and completed jobs. It will generally start out as an estimate. When the job is completed, the actual values will replace the estimates and this will then be used for billing purposes.
Create the Job table as you have the previous tables. It will contain the following fields:
Creating the JobProduct and JobService Tables
The JobProduct and JobService tables specify the products and services that are included in a job. The referenced tables, Product and Service, respectively, define the static properties such as price and description. The JobProduct and JobService tables supply the job-specific details such as quantity and status. These tables also allow for a custom price to be assigned that is applicable for this job only.
Creating the Contact Table
The Contact table is used to record correspondence with a customer, such as e-mails or phone calls. Create the Contact table and add the following fields:
Save the table and enter Contact for the name, when prompted.
A select query can be used to combine the fields of two or more tables into a single view. This technique is often used to simplify form development since a form can use a query just like it would a table. The form can then be developed from the query, which combines the fields from each table.
For example, the JobProduct table provides the job-specific details but the Product table contains some important static details such as product code and description. You’ll combine these two tables into a single, de-normalized view (query). The form will use the query and can include fields from both tables.
You will create the following queries:
Implementing the qryJobProduct Query
We’ll start with the qryJobProduct query that combines the JobProduct and Product tables.
Figure 5-15. Creating a new query
Figure 5-16. Adding the JobProduct and Product tables
Tip You can remove or edit these joins in the query window without affecting the table relationships.
Figure 5-17. The completed qryJobProduct query
Implementing the qryJobService Query
Implement the qryJobService query the same way except use the JobService and Service tables. Include the following fields:
Save the query and enter the name qryJobService when prompted. The completed query should look like Figure 5-18.
Figure 5-18. The completed qryJobService query
Creating the qryJobSummary Query
Create a new query and add the following tables:
You may need to rearrange the tables but the initial query will look similar to Figure 5-19.
Figure 5-19. The initial query design
Notice that the Address table is linked to both the Customer and Job tables. A customer can have multiple addresses but a job can only have one address. For this query, the Job table is the main table and you’ll want to keep the join between Job and Address but remove the one between Customer and Address. Click this join and then press the Delete key to remove it. This will have no effect on the table relationships.
Double-click the following fields to add them to the query:
The completed query should look like Figure 5-20.
Figure 5-20. The completed qryJobSummary query
Implementing a Data Macro
Data macros are a handy place to implement data processing procedures. They can be automatically executed when certain data events occur, such as a record being added or modified. Because of this, they are particularly useful for keeping related tables in sync and enforcing business rules. A data macro can be assigned to a data event directly, or you can create a named macro that can be called by data events. Named macros can also be called manually from a form.
To add an event macro, edit the table that you want the macro to run on. Then, from the DESIGN tab of the ribbon, click either the On Insert, On Update, or On Delete button as shown in Figure 5-21, depending upon which event to want the macro to respond to.
Figure 5-21. The DESIGN tab of the ribbon
Creating the Data Macro
For this application, you’ll create a named macro that calculates the total amount of the job and updates the TotalEstimate field of the Job table. You will then call this macro from all the data events that could potentially affect the total such when a record is added to the JobProduct table.
From the HOME tab of the ribbon, click the Advanced button and then select the Data Macro link as shown in Figure 5-22.
Figure 5-22. Creating a data macro
The macro will need to know which job should be updated so you’ll create a parameter that will be passed in when the macro is called. Click the Create Parameter link in the upper right-hand corner of the macro editor. Enter the name jobID, select the Number (No Decimal) type, and enter a description as shown in Figure 5-23.
Figure 5-23. Adding the jobID parameter
Using the Macro Editor
The macro editor allows you to define the logic of your data macro. Data macros will typically perform actions such as reading data from a table, and updating or inserting a record. The Action Catalog, shown in Figure 5-24, illustrates the kinds of things you can do in a macro.
Figure 5-24. The Action Catalog
Actions are often nested inside other actions. For example, an EditRecord action specifies the record to be updated. Within this, you can add one or more SetField actions, which modify a single field within that record. Figure 5-25 shows a really simple macro that uses several of the most common actions. We will walk through this to explain how the individual actions are used.
Figure 5-25. Sample macro
The Group action functions much like the #region directive in C#. You can include a set of related actions within it and collapse or expand the entire group, as necessary. For large, complex macros this will allow you to more easily follow the overall process and drill into the areas you need to focus on. A Group action can be nested inside another Group as well.
The ForEachRecord action returns data from a table or query and you can define a Where Condition to filter the rows that you want returned. In this case, we’re using the Customer table and processing only Active accounts. All the actions nested inside this action are repeated for each record. You can break out of this loop by including the ExitForEachRecord action.
For each record returned, the If action checks to see if the e-mail address includes .onmicrosoft.com. We could have included that in the Where Condition but wanted to demonstrate the If action. You can also include an Else or an Else If section to define actions to be executed when the e-mail does not include this string. An EditRecord action is inside the If action, which means it will be executed only on records where the If condition is true.
The EditRecord action operates on a single record, which must be a record already read in through one of the read actions (either the ForEachRecord or LookupRecord action). By the default, it updates the current record; the record that was last accessed. In this case this will be the Customer record currently being evaluated by the ForEachRecord action. In a complex macro, you can have multiple nested read actions. In this situation, the “current” record may not be as obvious, or you may want to update a record that is not the current one. To deal with this, you can define an Alias on each of these actions. The EditRecord action will then operate on the record returned by the read action with the matching Alias. An Alias works much like they do when defining joins in an SQL statement. You specify an alias for each table in the query and then when selecting or updating a field, it is referenced using the table alias.
As we mentioned, you include one or more SetField actions inside the EditRecord action. Each one specifies the field name and the value it is to be updated to. In this macro, we’re simply appending a -A to the CustomerName field.
Tip You’ll notice that an Add New Action dropdown appears in various places in the macro. These are places where you can insert an action. The list of available actions in each location is limited based on its context. For example, you can only include a SetField action inside an EditRecord or CreateRecord action. You can also drag an action from the Action Catalog to the macro.
Computing the Job Total
Now you’ll employ some of these actions to compute the total cost of a job. The macro will compute this by adding up the products and services included in the job. The macro is fairly long so we will describe it in pieces.
Tip To implement this macro, you’ll probably find it easier to just use the figures that are shown. This will indicate exactly where each action should be placed and the specific expressions that you’ll need to enter. The instructions that follow provide an overview of what the macro is doing.
Figure 5-26. Adding a SetLocalVar action
Figure 5-27. Designing the data macro – Product Costs
Note The JobProduct table contains both an EstimatedQty field as well as an ActualQty field. If the actual value is populated, the formula will use that field instead of the estimated quantity. If a price has been specified in the CustomPrice field, this value is used instead of the UnitPrice. It is also assumed that this value will be an extended amount and does not need to be multiplied by the quantity to get the total amount for this product.
Figure 5-28. Designing the data macro – Part 2
Figure 5-29. Designing the data macro – Part 3
Figure 5-30. Designing the data macro – Part 4
Entering Test Data
To test this macro, you’ll need to first enter some data. For now, you’ll just enter the data directly into the tables. To enter data, you can open a table in Datasheet View and key in the values for each field. From the Job Tracking tab, right-click the table and select the View Data link.
Figure 5-31. Enter data into the Product table
Figure 5-32. Entering data into the Service table
Figure 5-33. Setting up a job address
Figure 5-34. Adding a Job record
Figure 5-35. Adding JobProduct records
Figure 5-36. Adding JobService records
Calling the Named Macro
The job total can change any time a record is added, changed, or deleted from either the JobProduct or JobService tables. So now you’ll add an event-triggered macro for each of these events that simply calls your named macro.
Open the JobProduct table in Design View. In the DESIGN tab of the ribbon, you’ll see a button for each of the three events as we showed earlier. Click the On Insert button, which will display the macro editor. Add a RunDataMacro action and select the CalculateJobTotal macro from the dropdown. Since this macro has a jobID parameter, the macro editor shows this and gives you a place to specify the value that should be passed in. Enter Job as shown in Figure 5-37. Click the Save button in the ribbon to save the change and then click the Close button.
Figure 5-37. Defining an Event Macro
Repeat this process to define macros for the update and delete events. The macros will be identical to the On Insert macro. Then, create macros for all three events on the JobService table. Again, these will be identical to the On Insert macro shown in Figure 5-37.
Testing the Data Macro
Now you’re ready to test your macro. You can invoke the macro by updating one of the JobProduct or JobService records. But first, we’ll turn on the macro tracing facility. This is a really nice feature with Access 2013.
Open the CalculateJobTotal macro in the macro editor. If you don’t have it already open, you can find it in the Navigator pane. Just double-click it to open it in Design View. Select the DESIGN tab of the ribbon. You’ll see a Data Macro Tracing button as demonstrated in Figure 5-38. Click the Data Macro Tracing button to turn on tracing. This button acts like a toggle; if you click it again it will disable tracing.
Figure 5-38. Turning on the trace mode
Now open the JobProduct table and change the ProductStatus field of one of the records. Then click off of this row to trigger the save operation, which will also invoke your macro. Go back to the CalculateJobTotal macro and click the View Trace Table button.
A portion of the trace information is shown in Figure 5-39.
Figure 5-39. The trace log
The first entry is from the On Update event of the JobProduct table. You can see that it called the CalculateJobTotal macro. The remaining entries are from the CalculateJobTotal macro, starting with the initializing of the jobTotal variable to 0. At the end of the procedure, you can see where it looked up the Job record and set the TotalEstimate field to 465.00.
Now for a final check, open the Job table and verify the TotalEstimate field has the correct value.
Designing Forms
Now that you have the tables designed you can customize the forms that allow the users to view and edit the data contained in them. Generally, you will need a form for each table. In some cases you’ll use a query that combines the data from multiple tables. When there is a parent-child relationship such as with the Customer and Phone tables, you use a Related Items control, which we’ll demonstrate later.
There are three types of forms that you can create. If you click the Advanced button in the ribbon, you will see this listed as Blank View, List View, and Datasheet View, as demonstrated in Figure 5-40.
Figure 5-40. The form options
The blank view is pretty self-explanatory. Use this type if you want to setup the data binding manually. The name List View is somewhat misleading. A list view is just a form that displays a single record. In contrast, the datasheet view displays multiple records in a grid format. In Access 2010 these were called Form, and Multiple Items. Use a list view if you want to show a single record, and a datasheet view if you need to show multiple.
In Access 2013 the forms are automatically created for you. For every table, there will be a list view as well as a datasheet view. You can find these in the Navigation pane as demonstrated in Figure 5-41.
Figure 5-41. Listing the existing forms
The generated forms provide a good starting point and will save you some time. They attempt to deal with parent-child relationships by embedding a Related Items control within a form. We’ll walk you through the changes that are needed. You’ll also need to make some new forms.
Caution You cannot use VBA in a web form as this is not supported with SharePoint. Instead you’ll use macros to perform advanced features such as form manipulation.
Exploring the Form Designer
Open the Phone Datasheet form by double-clicking it in the Navigation pane. The form will be displayed in the Layout view shown in Figure 5-42.
Figure 5-42. The Phone Datasheet form
The design experience in Access 2013 is very powerful and fairly intuitive. The first thing you’ll generally need to do is adjust the fields included in the form. You can resize a field and drag it around to change the order. You can also include additional fields by dragging them from the Field List pane.
Because the Phone table has a foreign key to the Customer table, you can also include any of the fields from the Customer table on the form. All the other tables are listed in the Field List pane and you can include fields from these tables as well. However, you will need to define how the record should be selected because there is no database relationship that defines that.
To the right of the grid you’ll find three small icons that are used to configure data, formatting, and action properties. One or more of these may be hidden depending on what element in the form is selected. The data properties allow you to configure the data source for the form or control. The formatting properties include defining the tooltip or caption or making the control visible or not. The actions properties allow you define custom actions for certain events such as On Click or On Load.
The action bar is shown above the grid and contains two default actions; a plus symbol for adding a new record and a trash can for deleting the selected row(s). You can also create your own custom actions, by clicking the green plus sign.
Creating the Phone and Address Forms
We’ll start with the phone and address forms. Let’s begin with the datasheet views.
Modifying the Phone Datasheet Form
The Phone Datasheet should already be displayed. If not, double-click it from the Navigation pane.
Tip The value of the primary key field is auto-generated and in many cases is not meaningful to the end users. These have been removed from the generated form to save real estate. The notable exceptions to this rule are the CustomerID and JobID fields. These are often used in documents such as invoices.
Delete the Customer field from the grid. Since the Phone Datasheet form will only be used as a child form of the Customer form, displaying the customer name here would be redundant. Arrange the controls so the PhoneNumber field is before Status and PhoneType. Also use the formatting properties icon to change the captions to Phone Number and Type. The form should look like Figure 5-43.
Figure 5-43. The layout of the Phone form
Modifying the Address Forms
Open the Address Datasheet form. Remove the Customer field from the grid and resize and rearrange the fields just like you did with the Phone Datasheet form. The completed form will look similar to Figure 5-44.
Figure 5-44. The layout of the Address Datasheet form
Now open the Address List form and remove the Customer field and caption. Re-arrange the controls to look like Figure 5-45.
Figure 5-45. The Address List form
Creating the Product and Service Forms
Next, you’ll adjust the forms that will display the products and services that can be included in a job. First, you’ll customize the Product Datasheet and Service Datasheet forms by resizing the fields and editing the field labels. These will be similar to the Phone and Address forms. Then you’ll modify the list views to remove the related item information.
The Product Datasheet and Service Datasheet forms just need some resizing of fields and customizing the captions. Modify these so they look like Figures 5-46 and 5-47.
Figure 5-46. The Product Datasheet form
Figure 5-47. The Service Datasheet form
However, the list views have some related information that needs to be removed. For example, the Product List form, shown in Figure 5-48, includes a Related Items control to display related JobProduct records. This is here because there is a foreign key relationship defined between these tables. For this application you’ll navigate from JobProduct to Product and not the other way around.
Figure 5-48. The initial Product List form
Delete the Related Items control and adjust some of the field labels so the form looks like Figure 5-49.
Figure 5-49. The adjusted Product List form
Save your changes. To see what this form will look like in your SharePoint site, right-click the view label in the Job Tracking tab and select the Open in Browser link as shown in Figure 5-50.
Figure 5-50. Opening a form in SharePoint
Click the edit icon and the form should look like Figure 5-51.
Figure 5-51. The Product List form in edit mode
Tip When saving changes in the Access application, if you already have the SharePoint site open in your browser, the changes may not be immediately available. If you experience this, close all your browser windows and go back to the SharePoint site.
Make the same changes to the Service List form. The completed form should look like Figure 5-52.
Figure 5-52. The final Service List from
Creating New JobProduct and JobService List Form
Now you’ll create new list view forms for the JobProduct and JobService tables. These tables establish the link between a job and the product and services that are included. The forms will need to display this information as well as data from the associated product or service, such as description and price. You will delete the existing JobProduct List form and create new forms from scratch. These forms will be different from the other forms you have worked on so far because:
Figure 5-53. Displaying the JobProduct views
Figure 5-54. Defining the list view
Figure 5-55. The initial list form
Figure 5-56. The final list form
Figure 5-57. Disabling the ProductCode field
Figure 5-58. The Job List form
Figure 5-59. The JobProduct List form
Figure 5-60. The layout of the JobService form
Modifying the Job Form
Next you’ll customize the Job List form. First you’ll do some basic re-arranging of controls like you have done with the other forms. Then we’ll show you how to configure the Related Items control.
Figure 5-61. The modified Job List form
The bottom portion of this form contains a Related Items control. This is a handy way of way of displaying multiple one-to-many relationships. A job consists of products and services; the sample job that you created earlier to test the data macro had four of each. When displaying a job you’ll need to show both lists. In Access 2010 this was done by including a subform for each list. In 2013 you’ll use a Related Items control.
Each child list will be put into a different tab. Two tabs were created for you, JobProduct and JobService. As you configure each list, you can choose up to four fields of the child record that will be displayed in this control. You can also choose which form will be used as a pop-up form when one of the rows is selected. Click the JobProduct tab and then click the data properties icon.
Figure 5-62. The initial layout of the Job form
Figure 5-63. Changing the tab caption
Figure 5-64. Configuring the JobService tab
Figure 5-65. The completed Job List form
Modifying the Customer Form
The Customer form will be used to view and update customer information. This is where you’ll view and add phone numbers and addresses for a customer. This form will also allow you to add and modify customer jobs. You’ll also record correspondence (contacts) with a customer from here. This is the portal of your applications where you’ll bring all of the other forms together.
Figure 5-66. The modified Customer List form
The section on the left side of the form is used to search for a specific record. So far, we have been ignoring this. It is not used when the form is presented as a pop-up window. Most of the other forms will generally be used that way so this was not that important. However, since the Customer List form is the primary form the users will start with, we’ll need to address it.
Tip As you have seen, the users can go directly to any form. If you expect a form to be used directly, then you should consider how searching will be done.
You can display up to two fields in the search box. The primary field is shown on top and the secondary, if used, is displayed on a separate line underneath. So if you use two, keep in mind that you will be able to display about half as many results. Also, be aware that the searching logic will compare the input string to any of the fields in the main table; you don’t have to display a field for it to be used in the search. You can also include an image on the search box, if the main table has a field that stores images.
Figure 5-67. Configuring the search window
Figure 5-68. Configuring the Address tab
PhoneType - Type
PhoneNumber - Number
Status - Status
JobStatus - Status
StartDate - Start
EndDate - End
TotalEstimate - Total
ContactDate - Date
ContactType - Type
Inbound - Inbound?
FollowUp - Followup
Figure 5-69. The completed Customer List view
Creating a Job Summary Form
Now you’ll create a custom view for the Job table. The Summary form will list all of the jobs in the database using a datasheet view. This will use the qryJobSummary query that you created earlier in the chapter and will include fields from both the Customer and Job tables as well as details from the Address table. You will also create a macro that will display the job detail when a record is selected.
Figure 5-70. Adding a new datasheet view
Figure 5-71. Listing the available fields from the query
Figure 5-72. Arranging the fields in the grid
Figure 5-73. Marking this as a read-only form
Figure 5-74. The completed Job Summary view
Figure 5-75. Adding an On Click action
Figure 5-76. Calling the OpenPopup macro action
Summary
In this chapter, I took you on a whirlwind tour of Access web databases and you built a fairly sophisticated application, which is now hosted on your Office 365 account. The application consists of:
With relative ease and essentially no coding, you have created a cloud-based web application.
18.118.20.231