Nov
19

A Better Way to Create Lookup Fields

By Bob S (8 posts -- read other by posts Bob S)

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]

Technorati Tags:

Related Posts:


Filed Under Desktop Education, Microsoft Access, Office |  

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...


Comments