Looksups - Good or Bad?

[GSCaupling]

I have a form with an agent field that is connected to an Agent Table via a lookup. I type "S" and it brings up Smith, "W" brings up Williams, etc. It is a terrific time saver, in my opinion.

But it also seems like I've seen some of you pros argue against lookups and even referred to a published paper on the subject.

Could someone give me a brief reason why lookups are bad in Access (2007, btw) and perhaps where I could find the paper in question?

Thanks,
GS

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[MajP]

BAD, BAD, BAD

That functionality is great in a form, but the problem is using that functionality in a table. You should have this in your form, but not in your table. In my opinion the only one who should enter data directly in a table is the person who built the database, all other users only enter data through forms. This way you can check and secure the data entry. Even the developer can quickly run into trouble going into the tables because you take away a lot of the ability to secure the data entry.

Lookup fields in tables can get you into trouble quickly. The problem is they mask what is really going on. Most lookup fields pull info from a table like

ID Last Name
1 Smith
3 Jones

The lookup field usually displays Smith, but stores 1. So when I look at the table I assume the field is string, but in fact it is numeric. When I query lookupfield = 'Smith', I expect to see records returned instead get a type mismatch.

 

[MajP]

http://access.mvps.org/access/lookupfields.htm

 

[dhookom]

Agreed, what MajP said.

All user interface should be through forms where you can use combo and list boxes as well as other nice controls.

Duane
Hook'D on Access
MS Access MVP

 

[GSCaupling]

Thank you for your replies.

Duane, do I understand that even in forms you prefer list or combo boxes to lookups? Or, are lookups okay in forms but not in tables?

BTW - my Agents table does not have an autonumber ID field. The table has only one field and it is for agents' names. The agent's name field is the PK and therefore the names have to be unique entries.

GS

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[dhookom]

I use lots of combo boxes in forms for users to select values. I never use lookup fields in tables.

I almost always add an autonumber primary key field to every table. How do you handle when agent names change? Mary Smith marries Joe Anderson and becomes Mary Smith-Anderson?

Duane
Hook'D on Access
MS Access MVP

 

[GSCaupling]

Well, we're so small that no names have changed yet, but that is absolutely a valid point. My goal is design something that will grow easily with the company, and the name-only table won't cut it.

Thanks for your advice.

GS

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]