Collecting Data via E-Mail
Access 2007 introduces a new feature that allows you to collect data through e-mail and import it into your database. The data is collected through either HTML forms or rich Microsoft Office InfoPath 2007 forms. Access 2007 allows you to either update existing data in a table or add new records to a table. By integrating Access 2007 with e-mail collection capabilities, you can have users update and add information to your database from different locations. This feature can be used, for example, in a club membership application that periodically needs to update its member records.
To use the e-mail data collection capabilities in Access 2007, you must also have Microsoft Office Outlook 2007 installed to send the data entry forms and to process the data returned. If you are sending the data collection forms in HTML format, your recipients need only have an e-mail client program that accepts HTML. If you want to send Office InfoPath 2007 collection forms, you also need to have InfoPath 2007 installed. The users receiving these forms must also have Office Outlook 2007 and InfoPath 2007 installed to fill out the forms and send them back.
| Note | |
Collecting Data via HTML Forms
Open ContactsDataCopy.accdb from the folder where you installed your sample files. Click the Navigation Pane menu, click Object Type under Navigate To Category, and then click Tables under Filter By Group to display the list of tables in the database. For the continued success of the Conrad Systems business, its owners need to find new customers who want to purchase their products. One of the best ways to find leads to potential new customers is to ask existing customers. The existing contacts in the Conrad Systems Contacts database could provide names of people through their own personal network of friends who could in turn become new clients. By using the data collection feature in Access 2007, you could send an e-mail form to all the existing contacts in the database, asking whether they know of anyone who might be interested in Conrad Systems’ products. To entice existing contacts to provide some names, you could offer an incentive of free support time.
You can update a single table using e-mail data collection, or you can update two or more tables if you use a saved select query as the record source for the data entry form. You’ll learn about creating queries using multiple tables in Chapter 8, “Building Complex Queries.” For this example, let’s create an e-mail form to collect data to add a new record to the tblContacts table. Because we’re going to send an e-mail message to the contacts in the tblContacts table, you’ll need to add your name and e-mail address if you want to follow along in this section. Open the tblContacts table in Datasheet view, go to the new record at the end, add your name and valid e-mail address, and save the record. Do not add any text to display if you use the Insert Hyperlink dialog box. (We’ll explain why later in this section.) Close the table when you’re done. Next, right-click the tblContacts table in the Navigation Pane and click Collect And Update Data Via E-Mail on the shortcut menu, as shown in Figure 6–15. Alternatively, you can select the table in the Navigation Pane and click the Create E-Mail button in the Collect Data group on the External Data tab on the Ribbon.
Figure 6–15: You can begin the process of collecting data via e-mail by using the table’s shortcut menu or the Create E-Mail button on the Ribbon.
Access opens the first page of the e-mail collection wizard, as shown in Figure 6–16. This first page is an introductory page, which outlines the steps you take to complete the process. For this example you need to complete six major steps. (If you are adding records to a table with no records, Access displays five steps on this first page.) Click Next to proceed to the second page of the wizard.
Figure 6–16: The first page of the e-mail collection wizard is an introductory page.
The second page of the wizard, shown in Figure 6–17, asks you to choose between creating an HTML form or an InfoPath form for data entry. Remember that the people who receive the InfoPath form need to have InfoPath 2007 and Outlook 2007 installed on their computers to be able to read and fill out the data form. When you send an HTML form, the recipient needs only an e-mail client that can handle messages in HTML. For this example, select HTML Form (the default) and click Next.
Figure 6–17: The second page of the wizard asks you to choose a type of form.
The next page of the wizard, shown in Figure 6–18, asks you to choose whether users receive a blank form or a form that includes data from within the database they will update. If you chose a table that has no records, a table that does not have a primary key defined, or a query that combines two or more tables, you will not see this page of the wizard. In those cases, Access assumes you only want to add new records, so it does not display this page. Note also that to send data to update, you must have available within one of your database tables the e-mail address of the recipient.
Figure 6–18: You can choose to collect new information or update existing records on this page of the wizard.
When you select the Collect New Information Only option after choosing to send an HTML message, the recipients can only send back a reply that inserts one record into your table. To insert multiple records, the recipients must send multiple messages. If you select Update Existing Information after choosing to send an HTML message, recipients can view and update all the records you send. The information the recipient returns in a reply will overwrite the existing rows. For this current example, select Collect New Information Only, and then click Next to continue.
On the next page of the wizard, shown in Figure 6–19, you decide which fields from the table or query to include in the e-mail form. By default, Access automatically places any fields whose Required property is set to Yes in the Fields To Include In E-Mail Message list on the right. Access also puts the * symbol next to any required fields.
Figure 6–19: Select which fields to include on the form, in what order to display them, and what labels to display on the form.
You can select any field in the Fields In Table list and click the single right arrow (>) button to copy that field to the Fields To Include In E-Mail Message list. You can also click the double right arrow (>>) button to copy all available fields to the Fields To Include In E-Mail Message list. If you copy a field in error, you can select that field and click the single left arrow (<) button to remove it from the list. You can remove all fields and start over by clicking the double left arrow (<<) button.
| Inside Out-Including the Primary Key in the Data Collection Process | Notice that Access did not include the primary key of the tblContacts table (the ContactID field) in the list of available fields. Whenever the primary key is the AutoNumber data type, you cannot include the field regardless of whether you are adding or updating records. If the primary key is not an AutoNumber data type, you should include it only when adding records. When updating records, Access includes the key value in the data it sends in the e-mail form, but the user cannot see nor update it. The only case that requires the primary key field is when the key is not an AutoNumber and the user will be inserting records. When adding records to a table that has a primary key that is not an AutoNumber data type, the user must supply a key value that is unique to be able to add data to the table via e-mail. |
For this example, you want to include some of the fields in the tblContacts table, but not all of them. The existing Conrad Systems contacts might not know the information for every field, but they should be able to fill out at least the basic information for new contacts, such as last name, first name, and e-mail address. Select the LastName field and then click the single right arrow (>) to move the field to the Fields To Include In E-Mail Message list. Now repeat this procedure and move the following fields to the Fields To Include In E-Mail Message list: FirstName, MiddleInit, Title, Suffix, WorkPhone, WorkExtension, MobilePhone, EmailName, and Website. These 10 fields should suffice for obtaining information on prospective leads.
After you move fields to the Fields To Include In E-Mail Message list, the up and down arrows to the right of this list become available along with the Field Properties section. You can click the up and down arrows to change the order of the fields you are including in the form. For this example, select the LastName field and click the down arrow twice to move it down two spots in the display order. (You could have moved the FirstName and MiddleInit fields before the LastName field, but we wanted you to see how to change the order of fields within the list.) Access fills in the field’s Caption property (or the field name if the field does not have a caption) in the Label To Display In Front Of The Field In The E-Mail Message box under Field Properties. You can customize this label to display different text or leave it as is. (You’re limited to using 64 characters for a custom label.) For this example, click the FirstName field and type Enter the First Name of the new contact here... as the label for the FirstName field, as shown in Figure 6–20.
Figure 6–20: Change the display order by moving the LastName field down two positions and type a more descriptive label for the FirstName field.
If you do not want to allow the recipient to enter any information or change existing data in that field, you could select the Read-Only check box. (This option is more suited for updating data when you want to display data in a field from an existing record, but do not want the user to change it.) For this example, do not select this check box for any of the fields. If you like, you can experiment with entering captions for some of the other fields-we had you change one caption so that you can see the result in the e-mail message. Click Next to continue to the next page.
Troubleshooting
 |
Why can’t “I see all the fields from my table in the data collection process?
You might have noticed that not only the AutoNumber primary key ContactID field but also the ContactType and Photo fields from the tblContacts table are not shown as available fields. You cannot use AutoNumber, Attachment, OLE Object, or Multi-Value Field Lookup data types in the data collection process. None of these data types are supported for e-mail collection in Access 2007. You will have to add or update these fields manually.
|
The next page of the wizard, shown in Figure 6–21, asks you to specify whether you want to manually process the replies as they arrive (the default) or let Outlook and Access automatically process the replies. By default, all replies are stored in an Outlook folder called Access Data Collection Replies. If you want to change where Outlook saves replies, click the Access Data Collection Replies link on this page. The wizard opens the Select Folder dialog box in Outlook where you can select the Outlook folder you want to use or create a new folder. After you select a different folder, the link on the wizard page changes to the name of this folder.
Figure 6–21: Choose whether to have the replies automatically processed or to manually process them yourself.
To have the replies automatically processed, select the Automatically Process Replies And Add Data To tblContacts check box (the default). For our example, make sure this option is selected. The Set Properties To Control The Automatic Processing Of Replies link allows you to customize various settings for automatic processing. Click this link to open the Collecting Data Using E-Mail Options dialog box, as shown in Figure 6–22.
Figure 6–22: You can set options for processing e-mail forms in the Collecting Data Using E-Mail Options dialog box.
The default settings for adding data are as shown in Figure 6–22. If you clear the Automatically Process Replies And Add Data To The Database check box under Import Settings, all the other options in this dialog box become unavailable, and you will have to manually process all the e-mail replies. Selecting the second check box, Discard Replies From Those To Whom You Did Not Send The Message, instructs Outlook 2007 not to process replies from people to whom you did not send the e-mail form. “Discard” in this case is a bit of a misnomer because the replies will remain in the data collection folder in Outlook-Outlook simply won’t process them. You can, however, manually process these replies if you so choose.
Selecting the third check box, Accept Multiple Replies From Each Recipient, allows a recipient to reply to the e-mail form message more than once. Each reply is automatically processed upon arrival. If you clear this check box, only the first reply is processed, but all other replies remain in the Outlook folder. You can then choose to either manually process these additional replies or delete them. In our example, one of your contacts might know the names of several people who could potentially be interested in buying products that Conrad Systems offers. Leaving this check box selected allows the recipient to provide more than one name to you.
| Note | The Accept Multiple Replies From Each Recipient check box does not apply to multiple records within an InfoPath form. If a recipient adds more than one record to an InfoPath form, each record is processed because all the records are stored in one e-mail message. |
The fourth check box, Allow Multiple Rows Per Reply, applies only to InfoPath forms. If you select this check box, the recipient can insert additional rows in the form to create more records. Each record in the form is then automatically processed. The fifth check box, Only Allow Updates To Existing Data, also applies only to InfoPath forms. Select this check box to prevent a recipient from adding new records in the InfoPath form when updating data. If you clear this check box, the user can add new records in the InfoPath form as well as update existing data. Because we have asked the wizard to collect new information only, this option is unavailable.
Under Settings For Automatic Processing, you can specify the maximum number of replies to be automatically processed. The default for Number Of Replies To Be Processed is 25. You can enter any positive integer between 1, and 10 billion for this setting. If Outlook receives more replies than the number you entered, you can still process the remaining replies manually. If you want all replies to be automatically processed, enter a number larger than the number of replies you expect to receive.
Under Date And Time To Stop, you can define a date and time to have Outlook stop processing any further replies to this message. Any replies received after that time are stored in the Outlook folder, but are not automatically processed. You can, however, choose to manually process replies received after this date. Leave all the settings at their defaults for this example, click Cancel to close the dialog box, and then click Next to go to the next page of the wizard.
| Inside Out-Why Set a Date to Stop Processing Replies? | This setting might be required in time-sensitive situations. For example, you might be conducting a survey that has to be completed before a certain date. To ensure that no records are updated or added after the cutoff date, you can designate a date and time to stop automatic processing in the Collecting Data Using E-Mail Options dialog box. |
On the next page of the wizard, shown in Figure 6–23, you can choose whether to enter the e-mail addresses using Outlook 2007 or have Access use e-mail addresses that are stored in the database. If you choose Enter The E-Mail Addresses In Microsoft Office Outlook (the default), you can either type each recipient’s address in the Outlook message or you can choose addresses from your Outlook address book. If you select Use The E-Mail Addresses Stored In A Field In The Database, you can use addresses that are stored in the table you are adding or editing. You can also use e-mail addresses stored in a field in a related table. For this example, you’ll use the e-mail addresses stored in the tblContacts table, so select Use The E-Mail Addresses Stored In A Field In The Database, and then click Next to continue.
Figure 6–23: Access asks for the source of the e-mail addresses on this page of the wizard.
| Note | This page of the wizard appears only when you are adding new information. If you are updating records, you must have a field containing the e-mail addresses in the table you are updating or in a related table being updated. |
| Note | If you select the Enter The E-Mail Addresses In Microsoft Office Outlook option and click Next, the wizard allows you customize the subject of the message and include an introductory message for your recipients. After Access creates the message, you can click the To button in the Outlook message to open your Outlook address book and select one or more recipients for this message or manually type an e-mail address, and then click Send to send the message. |
On the next page of the wizard, shown in Figure 6–24, you tell Access which field contains the recipients’ e-mail addresses. When you first see this page of the wizard, the default option is to select a field within the current table or query. If a field with e-mail addresses exists in the table you are updating, click the arrow in the Select A Field box below The Current Table Or Query and select the field that contains the e-mail addresses. In this example, Access correctly found the EmailName field and selected it for you.
Figure 6–24: You can choose a field in the current table or query or in an associated table from which to obtain the e-mail addresses.
You can also select the An Associated Table option to find the e-mail addresses in an associated table (one that is defined as related to the target table in the Relationships window). After you select An Associated Table, Access enables the Select A Field box beneath this option. First specify the linking field from the current table or query, and then select the field containing the e-mail addresses in the list of fields from the related table. In this sample database there is a relationship defined between the ContactID field in the tblContacts table and the ContactID field in the tblCompanyContacts, tblContactEvents, and tblCompanies tables. Because ContactID is associated with more than one table, Access displays two lists, as shown in Figure 6–25. The first list displays the names of the associated tables. Choose the table you want, and then select the associated field from the second list, which displays the names of all the fields in the table you select from the first list. Note that if the field you choose under An Associated Table is associated with only one table, Access displays only one list with the names of the fields in the associated table. Because the tblContacts table already contains the e-mail addresses you need, select The Current Table Or Query (if it isn’t already), select the EmailName field from the list, and then click Next to continue.
Figure 6–25: Select the associated table and field that contain the e-mail addresses.
On the next page of the wizard, Customize The E-Mail Message, you can specify the Subject line of the e-mail, write an introductory message, and choose in which Outlook message field the address will appear (To, Cc, or Bcc), as shown in Figure 6–26. We designed our introduction to explain to our customers what the purpose of our message is and to offer them an incentive to provide us with some new contact information In the Subject box, type
Figure 6–26: Enter a descriptive subject line and introduction on this page of the wizard and specify where to place the e-mail address in the sent messages.
Special offer for BO$$ customers!
In the Introduction box, type
We hope you’re enjoying our BO$$ software and find it useful. Send us the contact information for any of your friends whom you think would benefit from our software, and we’ll extend your support contract for one month for free for each contact who buys our product. Simply fill in the information below and send it back to us, and we’ll do the rest!
To start, please make sure to click Reply first to fill out the form. Click Send when you are finished. To send information for more than one contact, click Reply again, fill out another form, and then click Send when you are finished.
Thank you for your business!
Conrad Systems Development.
If you were updating existing rows, Outlook must be able to validate the update by matching the e-mail address of the sender with an e-mail field stored in the table being updated or in a related table. You won’t be able to specify an e-mail address not already in the database, but you can instruct Access where to put the e-mail address in the message being sent. Leave this set to the default-To Field-to have the address displayed in the To field in the Outlook message. Click Next to continue.
| Inside Out-Instruct the Recipient to Click Reply | We recommend that in your introductory message for HTML forms, you always include text instructing the recipient to click the Reply button before starting. It’s not always intuitive from an end user’s perspective that they first need to click Reply in order to fill out the form. By including some additional instruction in the introduction, you can avoid potential misunderstandings and support calls. |
The next page of the wizard, shown in Figure 6–27, informs you that Access has all the information it needs to create the e-mail form and message. This page also provides information on how to view the status of e-mail messages and how Outlook will process the replies if you chose to have them automatically processed. Click Next to go to the final page of the wizard.
Figure 6–27: Access is now ready to create your e-mail form.
You can choose which recipients to send the HTML form to on this page of the wizard, as shown in Figure 6–28. Because you asked Access to fetch the e-mail addresses from a field in your table, the wizard shows you the list of addresses that it found. Each e-mail address to which you’re about to send the message is shown in the list with a check box at its right. Above the list, on the right, is a Select All check box. If you select this check box, Access selects all the e-mail addresses in the list. You can individually clear any check boxes next to the addresses to have Access not send the e-mail form to that person. If you added your e-mail address in the tblContacts table as instructed at the beginning of this section, select the check box for your e-mail address and clear all the other check boxes. (Much as we like to hear from our readers, we really don’t need an e-mail message from you every time you follow this example!) Click Send to have Access create the form message and send it using Outlook. You won’t be able to edit the messages further before Access sends them.
Figure 6–28: You can select the people to whom you want to send the message on the final page of the wizard.
| Inside Out-Considerations with Hyperlink Fields in Data Collection | In the initial release of Access 2007, there is a bug in the program that might prevent Access from sending the message if the field containing e-mail addresses is a Hyperlink data type and the address is formatted to display different text. As you’ll learn in Chapter 7, you can add a descriptor in the Text To Display box at the top of the Edit Hyperlink dialog box. All the e-mail addresses in the ContactsDataCopy.accdb sample database display the name of the contact-Jeff Conrad, John Viescas, and so on-as shown in Figure 6–28. If you want to be sure the data collection process works, use e-mail addresses in a plain text field or ensure that the hyperlink includes no display text |
If you chose to enter the e-mail addresses instead of using addresses in the database, Access opens a new Outlook message with the information you provided in the wizard, as shown in Figure 6–29. (The message might not receive immediate focus, so look on your Windows taskbar for the message and maximize the window if necessary.) Figure 6–29 shows what your preview message looks like if you followed the previous instructions but chose to enter the e-mail addresses instead of using addresses in the database.
Figure 6–29: You can preview your message if you choose to manually enter the e-mail addresses.
You can see the subject line you typed in the wizard and the custom introductory message at the top of the body section. Following the introduction, you can see Access has added some important information in a Note section. You can manually adjust the text in this part of the message body or leave it as is. (The warning about not altering the message applies to the recipient.) The rest of the message body has the 10 fields we selected to include in this form. In Figure 6–29, you can also see the custom label we used for the FirstName field. After previewing the message, you can click the To button to open your Outlook address book and select one or more recipients to send this message to or manually type an e-mail address, and then click Send to send the message.
Filling Out the HTML Form
When recipients receive the e-mail message form, it appears in their Inbox with the subject line you specified in the wizard, as shown in Figure 6–30. If you sent this message to yourself to see how this process works, open the message now to see what it looks like.
Figure 6–30: An e-mail data collection form has arrived in the Inbox.
When you open the message as a recipient, you see all the information you previewed in Figure 6–29 if you chose to manually enter the e-mail addresses. (If you chose to use the e-mail addresses found in the table, this is the first time you’ll see the completed message.) If you scroll down the message, you can see the form fields you need to fill in, but Outlook locks the fields and prevents you from filling them in. To be able to fill in the form fields, you need to click Reply first, as we discussed previously. Click Reply now and begin filling in the 10 fields, as shown in Figure 6–31. (We entered the information for a fictitious employee found in the Housing Reservations sample database.)
Figure 6–31: After clicking Reply, you can fill in the fields on the form.
In Figure 6–31, you’ll notice additional instructions beneath each form field. The HTML form recognizes the data type and size of each table field and displays some helpful text for the user. The FirstName field’s Field Size property is set to 50, so the HTML form instructs the user to enter any text up to 50 characters. For fields defined as a Hyperlink data type-such as the EmailName and Website fields-the HTML form instructs the user to enter a hyperlink address in this field. For fields defined as the Number data type, the user is prompted to enter a numeric value. If you look at Figure 6–31, you’ll also see that the HTML form includes a link under both the Title and Suffix form fields. These fields are designed to require a specific set of values with a lookup combo box or list box. When Access builds your e-mail message using HTML, it includes the lookup values at the bottom of the HTML form. Press and hold down the Ctrl key and click the link labeled Click This Link (a bookmark actually) beneath the Title field. Outlook moves you to the bottom of the HTML form, as shown in Figure 6–32. You can see the four title values and their numeric equivalents defined in the tlkpTitles table (Dr., Mr. Mrs., and Ms.) and the four suffix values defined in the tlkpSuffixes table (II, Jr., PhD, and Sr.). Find the values you need to enter for the two fields and then either scroll back up in the form or click the Title or Suffix link on the left to move back up in the form. At the bottom of the message, Outlook places a sentence instructing the user to click Send to submit the information. After you finish filling in the form, click Send to send the message back to yourself.
Figure 6–32: Access includes the lookup values at the bottom of the HTML form to help you enter the correct values in the form fields.
| Inside Out-Formatting Rich Text Fields | You might see HTML tags in an HTML or InfoPath update data collection form for fields defined in your table as Memo data types with the Text Format property set to Rich Text If you highlight all the text and HTML tags in the HTML form field, you’ll see a mini-bar appear over the field with a group of controls to format the text (You won’t see this mini-bar on an InfoPath form.) These controls are similar to the controls in the Rich Text group on the Home tab in Access 2007. If you change or remove any rich text formatting on the data collection form, Outlook exports the new formats into the Access field. |
| Caution | Be careful when using fields defined as lookup values in a data collection form because Access needs to load all of these values into the HTML or InfoPath form. If your lookup table has hundreds or thousands of records, it will take Access a very long time to load all this information into the data collection form. In this example, we purposely omitted the WorkPostalCode and HomePostalCode fields because the related lookup table (tlkpZips) has more than 50,000 rows! |
Having Outlook Automatically Process the Replies
After the message returns to you, Outlook places it in the folder you designated in the wizard. In our example, we left the designated folder set to the default-Access Data Collection Replies. In Figure 6–33, you can see that the message has arrived in the appropriate folder. You chose to have this message automatically processed, so Outlook immediately exported the new record to the tblContacts table. Notice, under the Data Collection Status column, that Outlook successfully exported the record to Access. You can open the message to see how the recipient filled in the form fields, but it’s not necessary to open the reply in order to have Outlook export the data.
Figure 6–33: Access automatically processes the message when you receive it.
| Caution | If you’re using an HTTP-based e-mail system such as MSN or Hotmail, your new messages arrive in the inbox for your e-mail account, not the primary Inbox in Outlook. You’ll have to move the messages to your Outlook Inbox and process them manually by opening each message and clicking the Export To Access button on the message Ribbon. |
Minimize your Outlook program and then maximize the ContactsDataCopy.accdb database again (or open the database if you closed it) to see if the record has been added. Select the tblContacts table in the Navigation Pane and then open it in Datasheet view. As you can see in Figure 6–34, a new record has been added to the table with the contact information you provided on the HTML form. Our client has helped add new information to the database without ever opening Access; in fact, the recipients do not even need to have Access installed for this process.
Figure 6–34: Our new contact information has now been added to the tblContacts table.
Collecting Data Using InfoPath Forms
Access 2007 also allows you to send InfoPath forms to e-mail recipients to collect data. Unlike HTML forms, InfoPath forms provide richer tools, such as the ability to display drop-down lists. To be able to reply to messages sent with InfoPath forms, your recipients need to have Outlook 2007 and InfoPath 2007 installed on their computers. In addition to demonstrating collecting data through InfoPath forms, we’ll choose different options in the wizard so that you can understand some of the other features available with using InfoPath.
Open TasksEmailCollection.accdb from the folder where you installed your sample files. For this example, we’ll update an existing record in the Tasks table and add a new record to the Tasks table. However, because updating data via e-mail requires an e-mail address in the database to validate your authority to perform the update, you’ll need to add yourself and your e-mail address if you want to follow along in this section. You’ll also need to add a task assigned to you that you can update. Open the Contacts table in Datasheet view, go to the new record at the end, add your name and valid e-mail address, and save the record. Open the Tasks table in Datasheet view, and add a new sample task assigned to yourself. Close both tables when you’re done.
Close any open database objects, select the Tasks table in the Navigation Pane, and on the External Data tab, in the Collect Data group, click the Create E-Mail button. Alternatively, you can right-click the table in the Navigation Pane and click Collect And Update Data Via E-Mail on the shortcut menu. Access opens the first page of the e-mail collection wizard, as shown in Figure 6–35. This first page is an introductory page, which outlines the steps you take to complete the process. For this example, you need to complete six major steps. Click Next to proceed to the second page of the wizard.
Figure 6–35: The first page of the wizard outlines the steps you take to collect data through e-mail.
The second page of the wizard, shown in Figure 6–36, asks you to choose between creating an HTML form or an InfoPath form for data entry. Remember that the people who receive the InfoPath form need to have InfoPath 2007 and Outlook 2007 installed on their computers to be able to read and fill out the data form. For this example, select Microsoft Office InfoPath Form and click Next.
Figure 6–36: Select the second option to create an InfoPath form.
The next page of the wizard, shown in Figure 6–37, asks you to choose whether users should receive a blank form or a form that includes data from within the database they will update. If you chose a table that has no records, a table that does not have a primary key defined, or a query that combines two or more tables, you will not see this page of the wizard. (You’ll learn more about creating queries using two or more tables in Chapter 8.) If you select the first option after having chosen the InfoPath form on the previous page, a recipient of your e-mail can add one or more records with each reply. The recipients can add additional records by clicking the Insert A Row command on the InfoPath form.
Figure 6–37: You can choose to collect new information or update existing records on this page of the wizard.
You can use the second option, Update Existing Information, only if e-mail addresses are stored in a field in the table or a related table. With this option, e-mail recipients can view or update one or more records with each reply, depending on how many records are associated with their e-mail address. Recipients can also add additional records by clicking the Insert A Row command on the InfoPath form unless you disable this capability in an option presented later in the wizard. In this example, you will update the existing record in the Tasks table and create a new record, so select Update Existing Information and then click Next to proceed.
On the next page of the wizard, shown in Figure 6–38, you decide which fields from the table or query to include in the e-mail form. By default, Access automatically places any fields whose Required property is set to Yes in the Fields To Include In E-Mail Message list on the right. Notice that Access places the Title and Status fields in the Fields To Include In E-Mail Message list because they are required fields. For this example, we want to use all the fields in the Tasks table, so click the double right arrow (>>) button to move the remaining six fields to the list on the right, as shown in Figure 6–38. If you don’t want to include all fields and you move a field in error, you can select that field and move it back by clicking the left arrow (<) button. If you decide you want to start over, you can remove all fields (except the required fields) by clicking the double left arrow (<<) button.
Figure 6–38: Move all the fields in the Tasks table to the Fields To Include In E-Mail Message list.
Notice that Access fills in the field’s Caption property (or the field name if the field does not have a caption) in the Label To Display In Front Of The Field In The E-Mail Message box under Field Properties. You can customize this label to display different text or leave it as is. For this example, leave each of the label captions set to their defaults. The recipients will see the fields in the order that they appear in the list on the right. You can select any field in this list and move it up or down in the sequence by clicking the up and down arrows to the right of the list. The default sequence is fine for this example, so you don’t need to move any fields. Also, leave the Read-Only check box cleared for all of the fields, and then click Next to continue to the next page.
The next page of the wizard, shown in Figure 6–39, asks you to specify how you want to manage the replies. By default, all replies are stored in an Outlook folder called Access Data Collection Replies. If you want to change where Outlook saves replies, click the Access Data Collection Replies link. The wizard opens the Select Folder dialog box in Outlook where you can select the Outlook folder you want to use or create a new folder. In this case, leave the default Access Data Collection Replies folder as the destination folder. In the HTML example earlier, you chose to have Outlook automatically process the replies. In this example, leave the Automatically Process Replies And Add Data To Tasks check box cleared so that you can see how to manually process the replies. The Only Allow Updates To Existing Data check box is available only when you are updating data and you have chosen to automatically process the replies. If you choose to automatically process the replies and then select this option, Access does not allow new records to be added to the table. This option appears dimmed in our example because we are going to manually process the replies. Remember also from the HTML example that when you ask Outlook to automatically process replies, you can click Set Properties To Control The Automatic Processing Of Replies. For details, refer to Figure 6–22 on page 314. Click Next to continue.
Figure 6–39: Leave these check boxes cleared to manually process the replies.
On the next page of the wizard, shown in Figure 6–40, you tell Access which field contains the recipients’ e-mail addresses. When you first see this page of the wizard, the default option is to select a field within the current table or query. If an e-mail address exists in the table you are updating, click the arrow in the Select A Field box below The Current Table Or Query and select the field that contains the e-mail addresses. However, in this case, no e-mail addresses are stored in the Tasks table in this database; rather, they are stored in the E-mail Address field in the Contacts table, so select the An Associated Table option.
Figure 6–40: You can choose a field in the current table or query or in an associated table from which to obtain the e-mail addresses.
After you select An Associated Table, Access enables the Select A Field box beneath this option. You need to select the field in the Tasks table that links to a related field in the Contacts table. In this database there is a relationship defined between the Assigned To field in the Tasks table and the ID field in the Contacts table. Select Assigned To in this box, and Access displays one additional option, as shown in Figure 6–41. Access displays all the fields in the Contacts table in the Select A Field box. Select E-mail Address from the list of fields and then click Next to go to the next page of the wizard. Note that if the field you choose under An Associated Table is associated with more than one table, Access displays two lists. The first list displays the names of the associated tables. Choose the table you want, and then select the associated field from the second list, which displays the names of all the fields in the table you select from the first list.
Figure 6–41: Select E-mail Address from the field list for the Contacts table.
On the next page of the wizard, you can specify the Subject line of the e-mail, write an introductory message, and choose in which Outlook message field the address will appear (To, Cc, or Bcc), as shown in Figure 6–42. In the Subject box, type
Figure 6–42: Enter a descriptive subject line and introduction on this page of the wizard and specify where to place the e-mail address in the sent messages.
Update Assigned Tasks
In the Introduction box, type
Please fill out the form included in this message to update the Task information and send it back to me.
Click Submit when you are finished.
Thank you.
Because you chose to update data, three options appear at the bottom of this page, under Add Recipients’ E-Mail Addresses In The. To be able to validate the updating of existing rows, Outlook must be able to match the e-mail address of the sender with an e-mail field stored in the table being updated or in a related table. You won’t be able to specify an e-mail address not already in the table, but you can tell Access where to put the e-mail address in the message being sent. Leave this set to the default option-To Field-to have the address displayed in the To field in the Outlook message. Click Next to continue.
The last page of the wizard, shown in Figure 6–43, informs you that Access has all the information it needs to create the e-mail form and message. You’ll notice in Figure 6–43 that Access displays a warning message at the bottom of this page. You might see any one of three possible messages in this box:
-
“Note: These e-mail messages might contain data that is of a confidential or sensitive nature.” Access always displays this message when you update existing records because the recipients might see data currently in the table that is of a sensitive nature. You might consider removing from the e-mail any fields with sensitive data if you are concerned that this could be a problem.
-
“You currently have an exclusive lock on the database; automatic processing will fail until the lock is released.” Outlook cannot process replies if you have the database open in exclusive mode. In order to process the replies, you need to close the database and then reopen it in shared mode. By default, Access should open all databases in shared mode unless you changed the setting in the Advanced category of the Access Options dialog box. You can also open a database in exclusive or shared mode using the Open dialog box if you click the arrow on the Open button and then click the appropriate command (such as Open Exclusive).
-
“Some records do not contain a valid address in the specified e-mail address field. No data will be returned for these rows.” Before you get to this page of the wizard, Access runs a quick scan for any Null values in the e-mail address field you designated on the previous page of the wizard. Access displays this warning message if it finds any Null values because it will not be able to send a message for all records that could be updated. You can either proceed with the data collection process with some being left out, or cancel the wizard and then add any missing e-mail addresses to the table.
Figure 6–43: Access displays potential issues that might cause problems with the data collection process.
Click Next to go to the final page of the wizard.
You can choose which recipients to send the InfoPath form to on the final page of the wizard, as shown in Figure 6–44. Each e-mail address related to a record you’re about to send out for update is shown in the list with a check box at its right. Above the list, to the right, is a Select All check box. If you select this check box, Access selects all the e-mail addresses in the list. You can individually clear any check boxes next to the addresses to have Access not send the e-mail form to that person. When we ran our example, there was only one e-mail address related to the one record in the Tasks table. If you added your e-mail address in the Contacts table and a related record in the Tasks table as instructed at the beginning of this section, select your e-mail address and clear the check box next to jconrad@mvps.org. (Much as we like to hear from our readers, we really don’t need an e-mail message from you every time you follow this example!) Click Send to have Access create the form message and send it using Outlook 2007.
Figure 6–44: You can select the people to whom you want to send the message on the final page of the wizard.
Filling Out the InfoPath Form
When recipients receive an e-mail form created using InfoPath, the message arrives in their Inboxes with an attachment. After opening the message, a recipient can fill out the form and update the information. Unlike HTML forms, InfoPath allows you to send forms that are easier for the recipients to fill out. In Figure 6–45, you can see that the Status field is displayed as a combo box. The recipient must select from one of the five available Status options because this field is a lookup field in the Tasks table.
Figure 6–45: InfoPath forms allow you to use combo box controls for data entry.
With InfoPath forms, you can have more control over the data that comes back in the replies. You achieve this by designing fields in your tables that require a specific set of values with a lookup combo box or list box. When Access builds your e-mail message using InfoPath, it includes the lookup values in the InfoPath form. If you were, for example, to send an HTML form with the same fields in the Tasks table, the recipients can see all the lookup values listed at the bottom of the form. However, they could still type any value into the Status box, including text that is not one of your five choices. Outlook will encounter errors processing the HTML reply if it tries to export that record into the Tasks table in Access because the Limit To List property of the Status field is set to True. In Figure 6–45, you also see a button next to any date field, which opens a calendar for you to select a date, just as you can from within Access. If you use InfoPath forms, you can reduce the number of problems with inaccurate data, and make it easier for users to fill out the form with the built-in InfoPath form controls.
For this example, change the status from Not Started to Completed by selecting Completed in the Status box, and change the value in the % Complete field from 0) to 100 to indicate the task is 100 percent complete. You can also create a new record in the InfoPath form by clicking the Insert A Row link in the lower-left corner of the form, as shown in Figure 6–46. For example, you might want to assign a new task to yourself or another contact in the database.
Figure 6–46: Click Insert A Row on an InfoPath form to add additional records.
After you click the link, a new InfoPath form appears below the existing form with blank fields, as shown in Figure 6–47. You can now fill out the form fields for a new record. You’ll notice the two required fields-Title and Status-have a red asterisk on the right to indicate to the recipient that those fields are required. You might also notice the InfoPath form recognizes the data type and size of each table field and displays some helpful text for the user. The Title field’s Field Size property is set to 150, so InfoPath instructs the user to enter any text up to 150 characters. The % Complete field is a Number data type in the Tasks table, so InfoPath instructs the user to enter a numeric value in this field.
Figure 6–47: The recipient of the e-mail can add new records in the InfoPath form if you selected this option in the wizard.
If you like, you can create a new record in this blank InfoPath form using the following information:
| Title | Review Chapter 6. |
| Status | Not Started |
| Priority | (1) High |
| % Complete | 0 |
| Assigned To | John Viescas |
| Description | Please review all the changes to Chapter 6. |
| Start Date | Click the calendar control and click the current date |
| Due Date | Click the calendar control and select the date three days after the start date |
Your completed record should be similar to Figure 6–48.
Figure 6–48: Fill out the blank form to add a new record to the Tasks table.
If you want to create more records, you can scroll down and click the Insert A Row link again to display an additional blank form. If you need to delete a record, click the arrow button in the upper-left corner of the InfoPath form and then click Remove on the menu that appears, as shown in Figure 6–49. You can also insert a new blank record above or below the current record by clicking the Insert Above or Insert Below option.
Figure 6–49: Click Remove to delete a record in the InfoPath form.
After you update the first record and add the new record on this InfoPath form, click Submit on the Outlook Standard toolbar to send this message. Outlook opens a confirmation dialog box where you can modify the To, Cc, or Bcc addresses, change the subject, and enter explanatory text. Click Send in that dialog box to send your updates. Outlook displays a confirmation message box indicating that the form was submitted successfully.
| Note | After you click Submit to send the message and then close the message, InfoPath displays a message that the form was changed and asks if you want to save the changes. You don’t need to save the form because Outlook has already sent the message. |
Manually Processing the Replies
When you collect data via e-mail, you can choose to have the replies automatically processed or to manually process them yourself. In our example using InfoPath forms, you chose to manually process the replies. In Figure 6–50 you can see the message is in the destination folder, but the Data Collection Status column indicates Message Unprocessed. No data has yet been exported to the Tasks table.
Figure 6–50: The Data Collection Status column in Outlook indicates that the message has not been processed.
To manually process this reply, right-click the message in Outlook 2007 and click Export Data To Microsoft Office Access on the shortcut menu, as shown in Figure 6–51.
Figure 6–51: Use the message’s context-sensitive menu within Outlook to export the data to Access.
Access opens the Export Data To Microsoft Access dialog box, as shown in Figure 6–52. You can see that the first record is the one with updates, and the second is the new record. You can use the horizontal scroll bar to scroll to the right to see what data will be added or changed in the various fields. If you find something inaccurate, perhaps an invalid Start Date or Due Date value, you can click Cancel to stop the export process. You can then send the message back to the recipient and ask for corrected data. See “Managing and Resending Data Collection Messages from Access” on page 343 for more information. All the data in this example should be fine, so click OK to have Outlook export the data to Access.
Figure 6–52: You can review the data to be exported to Access in this dialog box.
If the export process is successful, Outlook displays a confirmation dialog box. Click OK to close the dialog box. If Outlook encounters any errors in the export process, it displays a message indicating that it failed to export all or part of the data. In this case, you should resend the message and ask for corrected data.
Return to the TasksEmailCollection.accdb sample database and open the Tasks table in Datasheet view. In Figure 6–53, you can see the first record is updated with the correct data-the Status field is now changed from Not Started to Completed and the % Complete field is changed from 0. to 100 to indicate the task is 100 percent complete. You can also see that the new record concerning Chapter 6. has been added.
Figure 6–53: The updated and new data is now added to the Tasks table.
Managing and Resending Data Collection Messages from Access
You can review the status of sent data collection messages and resend messages by clicking the Manage Replies button in the Collect Data group on the External Data tab on the Ribbon. Access opens the Manage Data Collection Messages dialog box, as shown in Figure 6–54. Under Select A Data Collection Message, you can see the example message we sent, which table or query the message was based on, the message type, and the destination Outlook folder. When you select a message in the list, the Message Details section in the bottom half of the dialog box displays the fields included in the message, the date and time the message was created and last sent, whether the reply was automatically or manually processed, and the date and time to stop automatic processing.
Figure 6–54: You can review the status of sent messages in the Manage Data Collection Messages dialog box.
To delete a message from this dialog box, select it and then click the Delete This E-Mail Message button. To review the message options, select the message and click the Message Options button. Access opens the Collecting Data Using E-Mail Options dialog box previously shown in Figure 6–22 on page 314. Here you can review and modify the settings for the message. For example, if you set the original message to automatically process replies, you can turn off automatic processing, change the number of messages to be processed, or change the automatic processing end date. You can also turn on automatic processing if it wasn’t enabled in the original message. Note that any changes you make apply to new replies you receive.
If want to send a message again-either because the recipient entered incorrect or incomplete data or you want the recipient to send new data-you can select the message and click the Resend This E-Mail Message button. You might also want to send the same message to additional people or resend it to someone who failed to receive the initial message. Note that you cannot go to Outlook and forward a data collection message; you must use the resend process. When you click the Resend This E-Mail Message button, Access restarts the Collecting Data Through E-Mail Messages wizard beginning with the page that asks you how you want to process replies, as shown in Figure 6–55. (This page is similar to Figure 6–39 page 332.) You can adjust any settings-such as manual or automatic replies or where the e-mail addresses come from-and customize the message details. You can click through the remaining steps of the wizard and then click Create or Send depending on what type of message (HTML or InfoPath) you are sending. Access creates a new message and sends it to the recipients using Outlook 2007.
Figure 6–55: The resend process takes you back to this data collection wizard page.
| Inside Out-Starting Over or Resending? | Note that the resend process starts beyond the point in the wizard where you select the format of the message (HTML or InfoPath), choose whether records will be added or updated, and specify the fields in the message. If you do not need to adjust the format of the message, the add or update option, or the fields used in the data collection message, you can simply resend a previous message. If, however, you need to choose different fields or a different table or query to update, you must create a new message. |
Now that you know how to build tables, modify them, and import and link them, it’s time to move on to more fun stuff-building queries on your tables-in the next chapter.
EAN: 2147483647
Pages: 234