MS-Access / Getting Started

---

Creating Lookup Fields in a Web Database

If you've been following along in this tutorial, creating the Vendors web table in your Restaurant Database, you'll remember we are still missing one more field found in the tblVendors web table in the Back Office Software System sample web database on the companion CD-the State field. If you open the tblVendors web table in the BOSSDataCopy.accdb database in Datasheet view and tab to the State field, you'll notice that Access displays a drop-down list of states.

We defined the State field in this web table to be a lookup field. A lookup field can generally be classified as one of two types-a field that "looks up" its data from a predefined list stored with the field itself, or a field that looks up its data from a different table. The State field in the tblVendors web table looks up its data from a pre-defined list we provided when we created the field. Microsoft also uses the term value list to describe this type of field because the field gets its data from a list of values.

Let's create this State lookup field in the Vendors table that you've been working on. Open the Restaurant Database file again if you closed it and then open the Vendors web table in Datasheet view. Next, tab over to the City field so the focus is in that field. (Remember that Access creates the new field to the right of the field that currently has focus.) Now click the More Fields button in the Add & Delete group on the Fields contextual tab, and then click Lookup & Relationship from the drop-down list of options.

Access opens the Lookup Wizard and displays the first page. You must use this wizard if you want to create lookup fields in web databases. The first page of the wizard needs to know where you want to get the values for the field. You can either choose to have the values come from another table or type in the values yourself. (We'll discuss the first option in the next section of this tutorial.) Select the second option-I Will Type In The Values That Want-and then click Next to proceed to the next page of the wizard.

The second page of the Lookup Wizard, now needs to know specifically what values you want displayed for this field. By default, Access shows one column in the drop-down list of choices for the lookup field. If you want to display more than one column, enter the number of columns you want to display in the Number Of Columns text box. In the bottom half of this page, Access displays a datasheet where you can type in the specific values you need-one per row. For example we typed in the first six state abbreviations in alphabetical order. You can resize the column by dragging its right edge to the size you want or you can double-click the right edge of the column and Access adjusts the width to just fit the data you provide. Enter several state abbreviations now and then click Next to proceed to the next page of the wizard.

Access now displays the final page of the Lookup Wizard. On this page, you can provide the name you want to use for this lookup field. Type State as the name of your new field in the text box. Select the Limit To List check box-cleared by default-if you want Access to not allow users to enter any other data into this lookup field other than the values you provided. The last option-Allow Multiple Values-tells Access to create a Multi-Value Lookup Field. If you select this option, Access allows you to select multiple options from the drop-down list of choices you provided. In our case, it does not make sense to have a vendor address with more than one state abbreviation, so leave this option cleared.

Click Finish to save your changes and dismiss the Lookup Wizard. Access creates your new State field to the right of the City field. When you tab or click into the State field, Access displays a down arrow on the right edge of the field. When you click that arrow, Access displays all the state abbreviations you typed into the Lookup Wizard.

You'll notice that just below the drop-down list of state abbreviations is the Edit List Items button. Click this button, and Access opens the Edit List Items dialog box. In this dialog box, you can add, edit, or delete items from this value list lookup field. At the bottom of the dialog box, you can also select a default value to use for this field for all new records.

In addition to using the Edit List Items dialog box to change the values of your lookup field, you can also make changes to the values and settings by clicking the Modify Lookups button in the Properties group on the Fields contextual tab. Access reopens the Lookup Wizard, where you can make adjustments to your settings and save the changes. Be sure to click the Save button on the Quick Access Toolbar to save this latest change to your web table definition.

When you create a value list lookup field in a web database and then publish it to a SharePoint server, Access creates a Choice data type in the SharePoint list for that field. If you create a multi-value lookup field in a web database and publish it to the server, Access creates a Choice data type with the Checkboxes display property set to True to allow multiple selections.