Create a lookup column

In a Microsoft Access project connected to a Microsoft SQL Server 2000 database, you can add a lookup column. A lookup column displays values in another table's column that correspond to the lookup column's value. For example, a CustomerID column in an Orders table that is a foreign key to a Customers table. In this case,  you want to display information about the customer, such as the customer name and address, rather than the number in the CustomerID column.

1. In the Database window, click Tables under Objects, click the table you want to open, and then click Design on the Database window toolbar.
2. Select an ID column, such as the foreign key column, and then click the Lookup tab.
3. Define the lookup column by setting the following properties:
   • In the Display Control, select the type of control you want to use for the lookup column in a form.
     • Select Text Box to create a text box.
     • Select List Box to create a list box and make the next six properties available.
     • Select Combo Box to create a combo box and make all subsequent properties available.
   • In the Row Source Type, enter the type of row source Tables/Views/Functions, Value List, or Field List).
   • In the Row Source property, enter the name of the row source:
     • If Row Source Type is set to Tables/Views/Functions, select a table, view, or function from the drop-down list box.

       Note   Although stored procedures are not listed, you can type the name of a single-statement stored procedure that does not modify data.

       You can also click the Build button  to the right and use the Query Builder to create an SQL SELECT statement.
     • If Row Source Type is set to Value List, enter the list of values,  separated by semicolons, for example, "Small;Medium;Large" (without quotes).
     • If Row Source Type is set to Field List, enter the list of fields from the query or SQL statement specified by the Row Source Type setting that you want used in the list box or combo box.
   • In the Bound Column property, enter the number of the column you want bound to the underlying field for a bound multicolumn list box or combo box. This number is an offset: the first column is zero, the second column is 1, the third column is 2, and so on.
   • In the Column Count property, enter the number of columns you want displayed in the list box or combo box.
   • In the Column Heads property, select Yes if you want to display column headers.
   • In the Column Widths property, enter the width of each column in inches, separated by semicolons, for example, 2;3. To hide the bound column from the display, use a width value of zero, for example 0;2.
   • In the List Rows property, enter the maximum number of rows to display in the list box portion of a combo box.
   • In the List Width property, enter the width of the list box portion of a combo box in inches.
   • In the Limit To List, property, select Yes to restrict a combo box to displaying only values in the list. Select No to allow a user to enter text that doesn't match a value in the list.
4. Click Save  to save changes and switch to Datasheet view to verify your results.

Note   You can add more than one lookup column to a table.