In this section, you will learn how to create a list box that is populated with data from the form. To do this, you need to set up fields in your form so that you can enter the names of the categories and a set of products for each category.

1. On the View menu, click Data Source.
2. In the Data Source task pane, right-click the myFields group, and then click Add on the shortcut menu.
3. In the Name box in the Add Field or Group dialog box, type CategoriesAndProducts.
4. In the Type box, click Group.
5. Select the Repeating check box, and then click OK.
6. In the Data Source task pane, right-click the CategoriesAndProducts group, and then click Add on the shortcut menu.
7. In the Name box in the Add Field or Group dialog box, type Category, and then click OK. This field will contain the names of the categories for the first list box.
8. Right-click the CategoriesAndProducts group, and then click Add on the shortcut menu.
9. In the Name box in the Add Field or Group dialog box, type Product.
10. Select the Repeating check box, and then click OK. This field will contain the names of the products for the second list box.

Because this list box is populated with data from the form, the data needs to be added directly to the form. To do that, you will need to create default values for the Product and Category fields.

Tip

1. On the Tools menu, click Default Values.
2. In the Edit Default Values dialog box, expand the CategoriesAndProducts group, select the Category field (do not clear the check box), and then in the Default value box, type Seafood.
3. Select the Product field (do not clear the check box), and then in the Default value box, type Crab meat.
4. Right-click the Product field, and then click Add another Product above on the shortcut menu. This allows you to add a second product for Seafood as a default value.
5. Set the default value for the second instance of the Product field to Salmon.
6. Right-click the CategoriesAndProducts group, and then click Add another CategoriesAndProducts below on the shortcut menu. This allows you to add a second category as a default value.
7. Use the above steps to set the default values for the second instance of the Category and Product fields to Dairy, Cheddar, and Chocolate milk.

Now that you have entered default values for the Product and Category fields, you can connect the first list box to the data you just entered.

1. In the form, double-click the drop-down list box.
2. On the Data tab in the Drop-Down List Box Properties dialog box, click Look up values in the form's data source under List box entries.
3. Click Select XPath next to the Entries box.
4. In the Select a Field or Group dialog box, expand the CategoriesAndProducts group, and then click the Category field.

Now you need to connect the second list box to the data. While making this data connection, you'll want to apply a filter to the second list box. This filter will populate the second list box with the appropriate products, based on the category selected in the first list box. The filter compares the value selected in the first list box (listBox1 field) with the list of categories saved in the form (Category field).

1. In the form, double-click the standard list box.
2. On the Data tab in the List Box Properties dialog box, click Look up values in the form's data source under List box entries.
3. Click Select XPath next to the Entries box.
4. In the Select a Field or Group dialog box, expand the CategoriesAndProducts group, click the Product field, and then click Filter Data.
5. In the Filter Data dialog box, click Add.
6. In the Specify Filter Conditions dialog box, click Select a field or group in the first box under Display data that meets the following conditions.
7. In the Select a Field or Group dialog box, click listBox1, and then click OK.
8. In the Specify Filter Conditions dialog box, click Select a field or group in the third box under Display data that meets the following conditions.
9. In the Select a Field or Group dialog box, click the Category field.

Now that you have connected the second list box to the data and applied the filter, you are ready to test the list boxes to make sure that they work correctly.

1. On the Standard toolbar, click Preview Form.
2. In the drop-down list box, click Seafood. The standard list box should display a blank line, Crab meat, and Salmon.

   Note  The blank line allows users to clear their choice from the the list box.

If the list boxes do not work correctly, review the filter for the second list box to make sure that it compares the listBox1 field with the Category field.

In this section, you will learn how to create a data connection to an XML document that contains product and category values. The XML document will be your secondary data source. To make this type of data connection, you will need to create the XML document, associate the list boxes in your form with the XML document, and then apply a filter to the second list box so that the list of products changes appropriately.

Before you can associate your form with a secondary data source, you need to create the XML document.

1. Open Microsoft Notepad or another text editor.
2. Copy and paste the following XML data into the text editor:

   <?xml version="1.0" encoding="UTF-8"?>
   <ListBoxData>
        <CategoriesAndProducts>
             <Categories>Seafood</Categories>
             <Products>
                  <Product>Crab meat</Product>
             </Products>
             <Products>
                  <Product>Salmon</Product>
             </Products>
        </CategoriesAndProducts>
        <CategoriesAndProducts>
             <Categories>Dairy</Categories>
             <Products>
                  <Product>Chocolate milk</Product>
             </Products>
             <Products>
                  <Product>Cheddar</Product>
             </Products>
        </CategoriesAndProducts>
   </ListBoxData>

3. Save the file on your computer as listBoxData.xml

Now that you have created an XML document, you can connect it to your form. This will result in a secondary data source.

1. On the Tools menu, click Data Connections.
2. In the Data Connections dialog box, click Add.
3. In the Data Connection Wizard, click Receive data, and then click Next.
4. On the next page of the wizard, click XML document, and then click Next.
5. On the next page of the wizard, click Browse, select the listBoxData.xml file that you saved to your computer, and then click Next.
6. In the Enter a name for this data connection box on the next page of the wizard, type listBoxDataXMLFile.
7. Select the Automatically retrieve data when form is opened check box. This ensures that the list box data will be available each time the form is opened, rather than requiring a user to query for it.
8. Click Finish.
9. When the dialog box appears asking whether to add the file to your form so that it's accessible from other computers, click Yes.

Now you are ready to connect the first list box in your form to the data in the XML document.

1. In the form, double-click the drop-down list box.
2. On the Data tab in the Drop-Down List Box Properties dialog box, click Look up values in a data connection to a database, Web service, file, or SharePoint library or list under List box entries.
3. In the Data Connection box, click listBoxDataXMLFile.
4. Click Select XPath next to the Entries box.
5. In the Select a Field or Group dialog box, expand the CategoriesAndProducts group, and then click the Categories field.

   Note  The yellow background in the Select a Field or Group dialog box indicates that you're working with a secondary data source.

Now you will need to connect the second list box to the XML document. While making this data connection, you'll want to apply a filter to the second list box. This filter will populate the second list box with the appropriate products, based on the category selected in the first list box. The filter compares the value selected in the first list box (listBox1 field) with the list of categories saved in the form (Category field).

1. In the form, double-click the second list box you added.
2. On the Data tab in the List Box Properties dialog box, click Look up values in a data connection to a database, Web service, file, or SharePoint library or list under List box entries.
3. In the Data Connection box, click listBoxDataXMLFile.
4. Click Select XPath next to the Entries box.
5. In the Select a Field or Group dialog box, expand the CategoriesAndProducts group, expand the Products group, and then click the Product field.
6. Click Filter Data.
7. In the Filter Data dialog box, click Add.
8. In the Specify Filter Conditions dialog box, click Select a field or group in the first box under Display data that meets the following conditions.
9. In the Data source box in the Select a Field or Group dialog box, click Main.
10. Click listBox1, and then click OK.
11. In the Specify Filter Conditions dialog box, click Select a field or group in the third box under Display data that meet the following conditions.
12. In the Data source box in the Select a Field or Group dialog box, click listBoxDataXMLFile (Secondary), and then click the Categories field.

Now that you have connected the second list box to the XML document and applied the filter, you are ready to test the list boxes to make sure that they work correctly.

1. On the Standard toolbar, click Preview Form.
2. In the drop-down list box, click Seafood. The standard list box should display only a blank line, Crab meat, and Salmon.

   Note  The blank line allows users to clear their choice from the the list box.

If the list boxes do not work correctly, review the filter for the second list box to make sure that it compares the listBox1 field with the Category field.

In order to connect the list box to a database, you need to have Microsoft Office Access 2003 and the Northwind Sample Database for Access (the default location is C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\) installed on your computer.

Tip

In this section, you will learn how to create a data connection to two tables in an Access database. In this case, you will be working with the Northwind sample database. To do this, you need to associate the list boxes in your form with the Northwind sample database, and then apply a filter to the second list box so that the list of products displayed in the second list box changes appropriately based on the category selected in the first list box.

First, you need to create a data connection between the form and the tables in the database. Because you're only displaying the data from the database, rather than modifying it directly, the Access database will be a secondary data source for your form.

1. On the Tools menu, click Data Connections.
2. In the Data Connections dialog box, click Add.
3. In the Data Connection Wizard, click Receive data, and then click Next.
4. On the next page of the wizard, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.
5. On the next page of the wizard, click Select Database, and then navigate to and double-click the Northwind sample database on your hard drive.

   Note  By default, the Northwind sample database is located at C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\.

6. In the Select Table dialog box, click the Categories table, and then click OK. This specifies the Categories table as the primary table for the data connection.
7. In the wizard, click Add Table.
8. In the Add Table or Query dialog box, click Products, and then click Next.
9. In the Edit Relationship dialog box, make sure that the connecting fields are both CategoryID, and then click Finish.

   Note  The categories table has a one-to-many relationship to the products table, which means that for every one category there are many products. In a database, this type of relationship is usually established with key fields. In this case, the CategoryID key field allows Access to associate the correct set of products with the appropriate category.

10. In the wizard, review the data source structure. You should see two tables connected to each other: The primary table (Categories) and the secondary table (Products). To see the individual fields in the table, you can select the Show table columns check box. Once you are done reviewing the data structure, click Next.
11. In the Enter a name for this data connection box on the next page of the wizard, type listBoxDataDatabase.
12. Select the Automatically retrieve data when form is opened check box.

Now that you have created a data connection to the secondary data source, you can connect the first list box in your form to the tables in the database.

1. In the form, double-click the drop-down list box.
2. On the Data tab in the Drop-Down List Box Properties dialog box, click Look up values in a data connection to a database, Web service, file, or SharePoint library or list under List box entries.
3. In the Data Connection box, make sure that listBoxDataDatabase is selected.
4. Click Select XPath next to the Entries box.
5. In the Select a Field or Group dialog box, click the d:Categories group, and then click OK.

   Note  The yellow background in the Select a Field or Group dialog box indicates that you're working with a secondary data source.

6. Click Select XPath next to the Value box.
7. In the Select a Field or Group dialog box, make sure that the :CategoryID group is selected, and then click OK. This is the value that will be saved when the user makes a selection in the list box.
8. Click Select XPath next to the Display name box.
9. In the Select a Field or Group dialog box, click the :CategoryName group, and then click OK. This is the value that will be displayed to the user in the list box.

Now you will need to connect the second list box to the database. While making this data connection, you'll want to apply a filter to the second list box. This filter will populate the second list box with the appropriate products, based on the category selected in the first list box. The filter compares the value selected in the first list box (listBox1 field) with the list of categories saved in the form (:categoryID field).

1. In the form, double-click the standard list box.
2. On the Data tab in the List Box Properties dialog box, click Look up values in a data connection to a database, Web service, file, or SharePoint library or list under List box entries.
3. In the Data connection box, make sure that listBoxDataDatabase is selected.
4. Click Select XPath next to the Entries box.
5. In the Select a Field or Group dialog box, expand d:Categories, and then click the Products group.
6. Click Filter Data.
7. In the Filter Data dialog box, click Add.
8. In the Specify Filter Conditions dialog box, make sure that CategoryID is selected in the first box under Display data that meets the following conditions.
9. In the third box under Display data that meets the following conditions, click Select a field or group.
10. In the Data source box in the Select a Field or Group dialog box, click Main, click the listBox1 field, and then click OK. You have now set up a filter that compares the value selected in the drop-down list box (listBox1 field) with the list of products in the database (CategoryID field).
11. Click OK three times to return to the List Box Properties dialog box.
12. Click Select XPath next to the Value box.
13. In the Select a Field or Group dialog box, make sure that the :ProductID group is selected, and then click OK. This is the value that will be saved when the user makes a selection in the list box.
14. Click Select XPath next to the Display name box.
15. In the Select a Field or Group dialog box, click the :ProductName group, and then click OK. This is the value that will be displayed to the user in the list box.

Now that you have connected the second list box to the database and applied the filter, you are ready to test the list boxes to make sure that they work correctly.

1. On the Standard toolbar, click Preview Form.
2. In the drop-down list box, click Produce. The standard list box should display the following values: Uncle Bob's Organic Dried Pears, Tofu, Rössle Sauerkraut, Manjimup Dried Apples, and Longlife Tofu.

If the list boxes do not work correctly, review the filter for the second list box to make sure that it compares the listBox1 field with the CategoryID field.