Trigon Blue

Products

Services

Text Market

Book of Knowledge (articles & whitepapers)

SQL Server 2005 Stored Procedure Programming
in T-SQL and .NET, 3rd Ed.

SQL Server 2000 Stored Procedure and XML Programming, 2nd Ed.

SQL Server 2000 Stored Procedure Programming

e-Business News

SQL Server News

About Us

Contact Us

Join Us

Links

Search


2003 - Trigon Blue Inc.  
All rights reserved.



Lookup Tables in Access

When we say that a table is a lookup table for another main table, it means that the lookup table contains values that will be used as the contents of a field located in the main table. Usually the lookup table has at least two fields Id and Description. The main table stores only the values from the Id field, but since there is a relationship between the lookup and the main table, the value of the associated Description field is also associated with the record in the main table. On the level of the user interface, lookup tables are often implemented as list boxes or combo boxes.

Linking tables

We can demonstrate this concept using tables which contain information about companies and cities. First, we will link the tables on the table level. The link will be implemented as a list box on the user interface level.

Create following tables:

CITY  
CityId identification number of a city
CityName name of a city

 

COMPANY  
CompanyId identification number of a company
CompanyName name of a company
CityId identification number of a city

The CityId field links the tables. They are in a 1:n relationship, which means that: a company can be situated only in one city, but a city can have many companies. A record in the Company table has only one record in the table City, but a record in the City table can have more than one record in the Company table.

It is possible to display a list box to select a city instead of a text box in which to enter a CityId into the Company table. Behind the scene Access will operate with the CityId field. An identifier, which corresponds to a selected city, will be stored in the CityId field of the Company table. Another result of such a link is that the user is prevented from entering a value which does not exist in the City table. You can accomplish this feat in following way:

First, you create the City table in the standard way:

Note that the Data Type of the CityId field in this table is set to AutoNumber.

When creating the Company table, you should include the CityId field as well. Usually, we would set the Data Type to Number (Long Integer). That type corresponds to AutoNumber. In this case we will select another one.

Select Lookup Wizard from the Data Type drop-down list.

Since we want to use values from a lookup table, select the first option, then click Next.

Select the City table as the source of values for your lookup column then click Next.

Select the fields that will appear in the lookup (list box).

Click Next. The Lookup Wizard shows your lookup column.

Usually, you do not need to display the key column (CityId). Click Next to continue. Name the lookup column and click Finish.

If you open the table definition, you will notice that the CityId field's properties are set as follows:

Row source type table/query
Row source SQL query
Bound column The property tells Microsoft Access which column values to use as the value of the control. If the control is bound to a field, the value in the column specified by the bound column property is stored in the field named in the Control Source property.
Column widths specify the width of each column in a multiple-column list box.

It is also interesting to see how a lookup table is affected by linking. Open the City table and notice the '+' boxes along the left border. When you click on a '+' in one record, Access opens the section of the main table with the records associated with the selected lookup record.

Linking forms

Tables in Access can also be linked on the form level. We can do this on top of linked tables, but it isn't necessary. Forms can be linked even when the tables are not linked. In fact, that is what we recommend. It is better to link forms rather than tables.

Create the City and Company tables as you would usually. Now we want to use forms to view and edit the content of these tables. We should have a list box to display cities on the form that displays Company information. Again, users will be prevented from adding a city for which there is not already a record in the City table.

Create a form to show the Company table in following manner:

  • In Design mode, put text box controls CompanyId and CompanyName and a combo box to show CityName from City table.

As soon as you create the combo, Access opens the Combo Box Wizard. Specify that the combo box will look up the values from a table. Click Next.

The wizard opens a window where you select a table to provide the values for your combo box. Click Next.

Select the fields that contain the values you want included in your combo box. Click Next.

The wizard displays a window with a preview of the combo box. Usually, you will not display the key column as a part of your combo. You can also adjust the width of the column(s). Click Next.

The wizard prompts you for the field that this column will be bound to. Select CityId and then click Next.

Name the combo box and then click Finish.

Now, on your form you have a combo box.

If you view the Properties of the combo box, you will notice that the wizard has changed the following properties:

Control source Specify what data appears in a control (CityId)
Row source type table/query
Row source SQL query
Bound column The property tells Microsoft Access which column values to use as the value of the control. If the control is bound to a field, the value in the column specified by the bound column property is stored in the field named in the Control Source property.
Column widths width of each column in a multiple-column list box To hide first column set its width to zero (0";1").

When you activate the new form, you see the following:

When you add a new record, it is enough to type the first character of the name of the city (if the record is in table City), or to select CityName in combo box and it will be added.

Linking forms manually

This whole process can be performed manually as well. You need to create a form that shows the Company table and add text boxes for all fields. Then you need to change the text box for displaying CityName field into a list box. Right click the field and select Change Type from the menu. Select List Box. Then view the properties of the CityName field. Change the following parameters:

Property Description New value

Row source

to define rule how to load combo box or list box.

Select CityId, CityName from City

ColumnCount

specifies a number of columns displayed in a list box or in the list box portion of a combo box

2

ColumnWidths

specifies the width of each column in a multiple-column combo box or list box. You can also use this property to hide one or more columns.

0";1"

Control Source

specifies what data appears in a control

CityId

Bound column

which column values to use as the value of the control. If the control is bound to a field, the value in the column specified by the bound column property is stored in the field named in the Control Source property

1

After everything is set, the combo box displays only CityName, but your program will use CityId behind the scenes.

Home | Products | Services | Book of Knowledge | e-Business News | SQL Server News
About Us | Contact Us | Join Us | Links | Search