Microsoft Access for Beginners - Additional Information
Using Lookup Fields
Access uses lookup fields to provide a selection of possible values for a field. This selection can be obtained from another table or from a list of values manually entered into the field settings. A good example from the demo database is the Department field in tblPersonnel. The table stores a number for the department which references a department record in tblDepartments. As shown in the screenshots below, tblPersonnel uses a lookup field to display the department name from tblDepartments although it only stores the department number.
By using its relationship to tblDepartments, the personnel table stores a number corresponding to a given department.
Because of the lookup function added to the Department field of tblPersonnel for this example, the table displays the appropriate title even though only its number is stored.
The settings for lookup fields are found in the Lookup tab under the individual field settings of the table design screen. These settings allow for a lot of flexibility in the way the data is displayed in the table view. Lookup settings are also inherited by form fields created from the table field.
Display Control
This setting has four options; Text Box, Combo Box, List Box and Check Box (for Yes / No fields only). Setting it to text box removes any lookup function from the field. In a table view, there isn't much difference between a combo and list box. On a form, the combo box provides a drop down list which takes up less room than a list box as shown in the comparison below.
Row Source Type
This setting determines what type of lookup will be performed.
A Table / Query lookup is exactly that, with the data coming from either a table or query in the database or a query added to the control. This is a good option for the example shown above where the data is coming from tblDepartments. A field can also reference itself when doing a lookup. This might be done if you have a field where you want the lookup to remember values that the user has entered in the past. Every time a new value is entered into the table, it's added to the lookup because the field is looking up its own values.
A Value List lookup is appropriate when there are only a few items that you want the lookup to present and it's not likely that the list will change. The WorkStatus field in tblPersonnel is a good example of this. There is a small number of status descriptions that can be used. Presenting them in a lookup simplifies data entry and ensures that the descriptions are entered the same way every time without misspellings.
The Field List lookup is a way in which you can list the field names from a specific table in the lookup.
Row Source
This setting specifies the actual source of the lookup data by referencing a table or query, creating a new query or manually entering values. The Department field has a very simple query behind it that pulls the Department ID from tblPersonnel and the Department Name from tblDepartments. The link between the tables does the rest and the department names are shown in the lookup.
In the Work Status field which uses a Value List, the values are entered manually with a semi-colon between each one. This allows Access to recognize the separate values. A Field List lookup will accept the name of any table or query in the database.
Bound Column / Column Count
As you can see by the Department lookup, the Row Source can have more than one column so it's necessary to specify which of the columns supplies the value that's actually saved to the field performing the lookup. In this case, it's the first column since it's the Department ID that's being saved. The department name is only displayed.
The Column Count setting is especially important for a Value List lookup. Remember that In this lookup, you supply the values manually, placing semi-colons between them. Specifying the column count tells Access how many columns of data this actually represents. For example, if you had a field named 'Location' and entered the following string of values under Row Source:
"100";"Seattle";"200";"Denver";"300";"Boston";"400";"Chicago"
This could represent a list of company locations with location IDs and cities so you would want it in two columns. Entering '2' under the column count tells the database to show two columns, using the available values to fill in the columns from left to right and adding rows as needed. You would see something like this.
Depending on which column you chose as the bound column, the table would then save either the location ID on the left or the city name on the right when a user chose a value from the lookup.
Column Heads
This option specifies if a header row should be shown in the lookup. Depending on the source type you specify, this could be field names or, in the example above, the first row of the Value List would be used as headers. In this case, you would want the Row Source to read something like this:
"Location ID";"City";"100";"Seattle";"200";"Denver";"300";"Boston";"400";"Chicago"
Column Widths / List Width / List Rows
The width of the columns in the drop down or list box and the total width of the list itself can be changed with these settings to accommodate headers and content. The Location ID column shown above is set at 0.75".
The List Rows settings specifies the number of rows that will be displayed at one time, not the total number of rows in the lookup. This can be useful in adjusting the amount of space taken up by the lookup on a form or in a table view.
Limit to List
This setting determines if the user can enter new values or is limited to the choices offered by the lookup. In the example above, you probably would not want the user adding new locations and you would want to make sure the available locations were entered correctly for reporting purposes so you would probably set this to 'No'.
On the other hand, if you're entering a large number of addresses, you might put a lookup on the City field and allow the user to enter new cities as needed. This way, the user would be presented with a list of cities already used which would speed up data entry and cut down on error but would still be able to add new ones as needed.
Form Lookups
Combo and list boxes can be added to forms independently of the tables although if they are added to the table when it is designed, the lookup will carry over when the field is added to a form design. Still, if you decide when designing the form that a combo box is just what you need, the designer makes it easy to change between a text box and combo box. In some cases, you might want to have a lookup field on the form but not on the table because of the table size. The properties that the form combo and list boxes use are also identical to the ones described in this article.
© 2010, Andrew Comeau, except where otherwise noted. Material contained on this website should not be republished without permission from the author. Questions? Comments? You can e-mail me at this address.
Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries.