The Lookup Wizard (II)

Search column based on a table.


If the search column is based on values extracted from a table or query, then in the first window of the lookup wizard we should select the first option.

Click on the Next button and the following window appears:

We select the table or query from which we choose to obtain data.

In the View box, if we select Tables, only tables defined in the database will appear. If we select Queries, only queries will appear, and if we select Both, both tables and queries will appear

After pressing the Next button a window will appear where we will select the columns that will appear on the list.


In this window we have in the list on the left the columns of the table or selected query, and in the list on the right we will place the columns that we wish to appear in the lookup list.

To select a column click on it so that it stands out and then press the button to send the column selected to the right hand side.

The button allows us to send all the columns from the left to the right side in one go.

The button allows us to remove a column from the list on the right.

The button allows us to remove all the columns from the list on the right in one go.

When we have included all the columns press the Next> button.

A screen will appear where we can adjust the width of the columns before arriving at the last window.


If the lookup list defined in this way contains only one column we arrive at the last window of the wizard where we are asked the name of the column as we saw on the previous page.

If the lookup list contains more than one column it is necessary to indicate to the wizard which one will be the value introduced into the field that we are defining. For this reason a window similar to the one we show next will appear, all we need to do is click on the column we choose to select, and click on Next> to go to the last window of the assistant.


Note: When does one define more than one column in the lookup list field?

Imagine that we have an Invoices table in which we need to store the client. Usually the clients are encoded using a code field or using their ID number, in which case we will store the client code in the invoice instead of having his full name.

We can define the client field in the Invoices table using a lookup list to assure that we introduce in this field a correct client code; but if we include in the list only the client codes, this list will not tell us much, it would be better to include in the list the client codes (to store it in the invoices field) and the client full names so as best to be able to select the client. In this case the list will have two columns.


The Lookup tab - list based on a table


Using the wizard we have defined a field with a lookup list based on a table, we see this reflected in the fields properties in the Lookup tab this way:

The properties that appear are the same as in the case of a list of values (see previous page), the only change is the Row Source Type -now it is Table/Query, and the Row Source. The wizard has generated an SQL instruction to obtain the columns from the table that will appear in the list.

Note: In this example we have created a list composed of two columns, and the first (state_code) will contain the value to be stored in the field (Bound Column).


Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.