Thoughts on Lookup Fields

[Stimpy707]

I was wondering if a few of you could share your wisdom regarding lookup
fields.

I could be mistaken but it seems that I have read some posts that have
recommend staying away from using the Lookup Wizard on fields in a table and
that it is wiser to use querries instead. Is there some truth to that and if
so, could you explain the logic behind that advice?

I'm in the beginning stages of creating a database and I don't want to start
going down the wrong path.

 

[BruceM]

This will provide some moe information about lookup fields:
http://www.mvps.org/access/lookupfields.htm

Note that this is very different from using a query to provide a combo box
row source. One of the problems with lookup fields is that what they are
really doing is hidden from the user, and that it is very difficult to
figure out what value is really being stored.. That is not the case with a
query you build, either from scratch or by using the query wizard.

 

[Douglas J. Steele]

http://www.mvps.org/access/lookupfields.htm at "The Access Web" explains
some of the many reasons why most of us abhor lookup fields.

 

[Fred]

These guys are super experts and developers. For us mere mortals, look-up
fields in the table have a few advantages:

- Easier for new folks to create
- They show up automaticaly everywhere, including in tables and queries
- Although it's considered a no-no on developed applications, lots of people
use queries to view/edit/add data, and in-table ones are the only ones that
show up in a query.

 

[BruceM]

I am far from being an expert, but I have run into problems of my own when I
used lookup fields early in my Access "career". Read the linked article to
be sure you understand their limitations. You will discover those
limitations on your own soon enough, I expect.

 

[Fred]

Bruce,

We actually use a mixture.

I did run into #7 already, which was one of the many reasons I retreated
from moving our back ends to a SQL server.

Sincerely,

Fred

 

[Tony Toews [MVP]]

These guys are super experts and developers. For us mere mortals, look-up
fields in the table have a few advantages:

- Easier for new folks to create
- They show up automaticaly everywhere, including in tables and queries
- Although it's considered a no-no on developed applications, lots of people
use queries to view/edit/add data, and in-table ones are the only ones that
show up in a query.

I like using them because the combo boxes are automatically added to
forms. Saves me some effort. Also you can see the data in the
tables rather than the raw foreign key.

So long as you realize how they work you'll be ok with them. If you
don't understand the problems that can crop up then you're going to be
in trouble.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Ken Sheridan]

Tony:

True, but you can achieve just the same by setting the Display Control
property of the column to Combo Box, without the associated problems of the
lookup wizard 'data type'. Nevertheless I'd never do so.

Ken Sheridan
Stafford, England