Taking a Look at Lookup Properties

As you have been working with table design, you’ve probably noticed that there’s a Lookup tab available in the lower part of the Table window in Design view. You might have also noticed that Access 2007 offers you a Lookup Wizard entry in the drop-down list of data types and a Lookup Column option in the Tools group on the Design tab. This feature allows you to predefine how you want the field displayed in a datasheet, form, or report. For example, if you have a DepartmentID field in an Employees table that stores the primary key value of the department for which the employee works, you might want to display the department name rather than the number value when you look at the data. If you’re displaying a Yes/No field, you might want to provide a dropdown list that shows options for invoiced and not invoiced instead of yes and no or true and false.

In the sample databases, we defined Lookup properties for only a few fields-ones for which we knew that we would later need a combo box with the relevant available choices on one or more forms or reports. (You will also see combo boxes described as drop-down lists.) One such example is in the Housing Reservations sample database (Housing.accdb). Open the database, view the table objects, select tblEmployees, and open it in Design view. Click the DepartmentID field and then click the Lookup tab to see the settings as shown in Figure 5–34.

Figure 5–34: The DepartmentID field in tblEmployees in the Housing Reservations sample database has Lookup properties defined.

As you can see, we have set the Display Control property to Combo Box. You see combo boxes in Windows applications all the time. It’s a box that you can type in with a button on the right that you can click to drop down a list of values to select. In Access, you tell the combo box what type of list you want (Row Source Type) and specify the source of the list (Row Source). Access is a bit unusual because it lets you define a list that contains more than one column that you can display (Column Count), and it requires you to specify which of the columns (Bound Column) actually supplies the value to be stored when you pick an item from the list. This means that you might see a text value, but the combo box stores a number.

You can see this combo box in action by switching to Datasheet view. You can click in the Department field and type a name from the list, or click the arrow on the right and select an item from the list as shown in Figure 5–35. Remember, DepartmentID is actually a number. If you didn’t define the special settings on the Lookup tab, you would see a list of numbers in the Department column. For details about these settings, see Table 5–4 on page 243.

Figure 5–35: The Lookup tab settings show you a combo box in Datasheet view.

Table 5–4: Lookup Properties
Open table as spreadsheet

Lookup Property	Setting	Meaning
Display Control	Check Box (Yes/No fields only), Text Box, List Box, or Combo Box	Setting this property to Text Box or Check Box disables lookups. List Box shows a list of values in an open window. Combo Box shows the selected value when closed and shows the available list of values when open.
Properties Available When You Set Display Control to List Box or Combo Box
Row Source Type	Table/Query, Value List, or Field List	Table/Query specifies that you want rows from a table or query to fill the list. If you select Value List, you must enter the values you want displayed in the Row Source property, separated by semicolons. The Field List setting shows the names of the fields from the table or query you enter in Row Source-not the data in the rows.
Row Source	Table Name, Query Name, or a list of values separated by semicolons	Use a table name, query name, or enter the text of the query (in SQL) that provides the list values when Row Source Type is Table/Query. See Chapters 7 and 8) for details about building queries, and Article 2. on the companion CD for details about SQL Enter a list of values separated by semicolons when Row Source Type is Value List. Use a table or query name when Row Source Type is Field List.
Bound Column	An integer value from 1 to the number of columns in the Row Source	Specify the column in the Row Source that provides the value stored by the list box or combo box.
Column Count	An integer value from 1 to 255	This determines the number of columns available to display. (See Column Widths.) When Row Source Type is Value List, this setting determines how many consecutive values you enter in Row Source make up a logical row.
Column Heads	No (default) or Yes	Choose Yes to display the field name at the top of any displayed column when you open the list.
Column Widths	One width value per column, separated by semicolons	Specify a zero width if you do not want the combo box or list box to display the column. It is common to not display an AutoNumber ID field, but you might need that field in Row Source as the bound column.
Allow Multiple Values	No (default) or Yes	Choose Yes to allow the user to select multiple values from Row Source for each record. Caution: If you set this property to Yes and save the table definition, you cannot change the value back to No later.
Allow Value List Edits	No (default) or Yes	Choose Yes to allow the user to add and edit items in the underlying Row Source.
List Item Edit Form	Form Name	Specify the name of a form that Access will open for the user to add items to the Row Source when the user enters a new value that is not in the list specified in Row Source.
Properties That Apply to Combo Boxes Only
List Rows	An integer value between 1 and 255 (default is 8)	Specify how many rows the combo box displays when you open the list. If this setting is less than the number of rows in Row Source, the combo box makes a scroll bar available to move through the list.
List Width	Auto or a specific width	Specify the width of the list when you open it. Auto opens the list the width of the field display.
Limit To List	No (default) or Yes	Choose No to allow the user to enter a value that’s not in the list. When the bound column is not the first displayed column, the combo box acts as though Limit To List is Yes regardless of the setting.

We decided to go ahead and define these properties in this table because we knew we were probably going to use a combo box in one or more forms that we would build later to display related department information while editing an employee record. By setting the values in the table, we can avoid having to define the combo box settings again when we build the forms. If you want to see how this works on a form, you can open frmEmployeesPlain in the Housing Reservations database. (Although you can open the “production” version of frmEmployees from the Navigation Pane, code in that form prevents you from updating any data unless you are signed on to the application.) You can see the result in Figure 5–36.

Figure 5–36: The table Lookup tab properties were inherited by the combo box on frmEmployeesPlain.

Inside Out-Lookup Tab Settings: For Advanced Users Only 

We recommend that only experienced users set the Lookup tab properties of a field in a table’s Design view. Unless you are fully aware of what the settings do, you can have problems later when you look at the information in a datasheet or try to build a query on the table. For example, if you look at the data in tblEmployees, you could mistakenly decide that “Housing Administration” is a valid value in the DepartmentID field. If you try to build a query and filter the DepartmentID field looking for that department name, your query won’t run.

Table 5–4 gives you an overview of what the lookup settings mean. When you study combo box controls later, in Chapter 11, “Building a Form,” you’ll see how you can also use lookup properties to display lists from related tables in a form. In Chapter 11 we’ll also explore the Combo Box Wizard, which makes it easy to correctly define these settings.

Inside Out-Allowing Space for the Scroll Bar 

When we’re designing a combo box that displays multiple columns when dropped down, we always specify a List Width value that’s the sum of the Column Width values plus 0.25 inch to allow for the vertical scroll bar.

Inside Out-Why You Should Not Use the Lookup Wizard 

Wait a minute! What about the Lookup Wizard entry under Data Types? We recommend that you never use this wizard. It often builds strange SQL for the Row Source property, it always defines a relationship between the table you’re editing and the lookup table, and it defines indexes on both fields. If the lookup table contains only a few rows, the index is a waste of time and resources. As you learned in Chapter 4, there’s a limit of 32 indexes on a table. We have seen some cases where we haven’t been able to build all the indexes we need because the Lookup Wizard built these unnecessary indexes.