A Better Way to Create Lookup Fields

By Bob S | Comments (9) | Trackbacks (0) (567 views)
Microsoft Office Access

Image via Wikipedia

You might be tempted, when thinking about creating a Lookup field in Microsoft Access, to use the Wizard.

Don’t.

It involves too much cleanup work after you are done. For example, if you use the Lookup Wizard, you must destroy any existing relationship between the two tables. During the running of the wizard, you will see a recommendation to hide the key column. This will also cause confusion, because if you do this, the field stored and the field displayed will be two different items. And finally, if you did have a relationship between the two tables with referential integrity set up, you must go back into the relationships after completing the wizard and reset the integrity.

Why do all of this?

Why not just change the properties of the field on the Lookup tab so that the field becomes a lookup field. It is faster and easier if you know which tabs to change. The list below shows how each property on the Lookup tab should be set. Simply follow these suggestions and you will have Lookup field without the bother of correcting and resetting properties.

Command

Setting

Display Control

Select Combo Box

Row Source Type

Choose Table/Query

Row Source

Create a query that displays all the fields you would like to see in the dropdown area.

Bound column

1

Column Count

Match the number of columns in your query

Column Heads

Leave this as No

Column Widths

Set the width of each column in the drop down area and separate them with semicolons

List Rows

Set the number of items to be displayed when the dropdown button is selected

List Width

Sum the column widths in the previous property

Limit to List

If set to Yes, no new entries are allowed, and auto-complete is enabled

Leave the rest of the properties alone.

Reblog this post [with Zemanta]

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically each day to your feed reader.

Tags:
Topics: Microsoft Access, Office Applications
  • Brian
    It all stemmed from a misunderstanding on my part. I read somewhere that lookup fields were evil and was looking for a way to accomplish them by other means (with your help). But, after rereading the lookups-are-evil article more carefully, I realized they were only saying lookups were bad when used on TABLES (because incorporating queries in a table contaminates the table with non-data elements and other inconsistencies). So your post above, which presumably is meant to apply lookups primarily to FORMS (not tables), ended uop being a red herring for me. In any event, I was eventually able to create the lookup according to your instructions. Thx.
  • Brian
    I tried do as instructed above but how do you set the relationship in relationships viewer so changes from the source table cascade to the other table (with the combobox).
  • Brian
    nvrmind-io figured it out
  • Brian, sorry i didn't get back to you sooner. glad to hear you figured it out. what did you have to do?
  • Really Informative. Thanks For the great blog.
blog comments powered by Disqus