4.1. Starting the Form Based on a Data Source
When creating forms based on data, the data that is to be used with the form needs to be specified. This does not mean merely which database you want to use, but also the tables or queries, and even the fields. While the InfoPath team has been kind enough to give us a wizard to connect to data sources, there are specifics you need to understand to have the correct data available for use on your InfoPath form.
4.1.1. Specifying the Right Data for the Right Job
When using more than one table for a form, all the tables necessary must be chosen. For example, if you wanted to list customers and their invoices with details, you would choose tblCustomers, tblInvoices, and tblInvoiceDetails. For this chapter, you will be use a Microsoft Access table from the Chapter 4.mdb titled tblCustomers. You can see some data from this table displayed in Figure 4-1.
Figure 4.1. Figure 4-1
To make it simpler all the data from this table will be used. If using only a few fields from a table, then only those fields should be specified. The Data Connection Wizard guides you in making these choices.
4.1.2. Working with the Data Connection Wizard
The Data Connection Wizard steps you through accomplishing the following tasks:
Pick a Database: Besides picking the type of database (for example, Access, SQL Server, or Web service), the actual database can be specified.
Choose Tables and/or Queries: As mentioned in Chapter 3, "Understanding Data," tables contain the actual data, and queries provide views of the data, letting you combine and limit data as needed. These tables and queries are generally set up ahead of time by the developer or system administrator of the database.
Verify and Create Relationships: Usually created ahead of time by the database creator, the Data Connection Wizard lets you create or modify relationships for use with your InfoPath form when you specify more than one table or query.
Choose Fields to Use: Although you can have InfoPath bring all the fields in tables or queries into your form for possible use, performance is enhanced when you only use fields that you will be using. This is especially true when you use SQL Server or Web services based on other client/server products, because only that data specified in the wizard will come down over the wires (network or Internet).
Edit the SQL Created: If you happen to be comfortable with writing SQL statements, the wizard provides the ability to modify the SQL created by InfoPath for the data connection. This is especially useful if you want to create more extensive statements with subqueries or even just add an Order By clause on the SQL to specify a sort order.
When picking the data for the initial form, don't worry about picking the data sources for all your controls such as value lists for drop-down list boxes that are going to be on your form. Only the data that is going to be used for inputting or modifying your data on the main form will be chosen using the initial Data Connection Wizard. The other data sources are called secondary data sources and can be chosen later in the form design.
4.1.2.1. Try It Out: Creating the Form and Specifying the Data Source
Open InfoPath by choosing All Program
Microsoft Office
Microsoft InfoPath 2003 from the Start menu. The Fill Out a Form dialog box is opened.Click the Design a Form task. You will then see the Design a Form task pane, with a blank design layout.
Click New from Data Connection . . . The Data Connection Wizard starts, as shown in Figure 4-2.
Figure 4.2. Figure 4-2
Because you are going to use an Access database, click the Next button. The next page displays selected tables and queries. Currently it is disabled, except for a button that reads Select Database.
Click the Select Database button. The Select Data Source dialog box opens. This dialog box is used by applications that are selecting data sources. Besides selecting Access .mdb files, you also can specify ODBC connections and SQL Server connections. You can see these options in the Select Database dialog box shown in Figure 4-3.
Figure 4.3. Figure 4-3
For this example locate the database for Chapter 4 (Chapter 4.mdb). You can see this file selected in Figure 4-4.
Figure 4.4. Figure 4-4
Click Open. You are now taken back to the Data Connection Wizard and shown a display of the tables and queries in the database you choose.
Highlight tblCustomers in the Select Table dialog box, as shown in Figure 4-5.
Figure 4.5. Figure 4-5
Click OK. As you can see here in Figure 4-6, the Data Connection tables and queries page with tblCustomers and its fields specified is now displayed.
Figure 4.6. Figure 4-6
In addition to tblCustomers and its fields being displayed, there are also a number of command buttons, a tree view, and a check box that accomplish the following:
NOTE
If a mistake is made and data not specified correctly, the wizard can be rerun to correct the changes. Modifying the data source, including using the Add Table, Modify Table, and Edit SQL . . . options will be discussed later in this chapter in the section "Updating a Form's Data Source."
Click Next because the tblCustomers table and all its fields are utilized for this example. The Summary page of the Data Connection Wizard is displayed, as shown in Figure 4-7.
Figure 4.7. Figure 4-7
You also have the opportunity to specify the data connection name for the InfoPath form, with Main connection being the default.
Click Finish, leaving the default connection name. Once you have completed the Data Connection Wizard, InfoPath creates a form, with a number of controls already on it, as shown in Figure 4-8.
Figure 4.8. Figure 4-8
