Alternative to Lookup fields

[Athar Khan]

Some developers don't like to use Lookup fields, Why? And if they are right, then what is the alternative (that work like a Lookup field)

[ranman256]

Lookup fields are wonderful, a godsend.
Why haters gotta hate?

[aytee111]

Lookup fields are a real PITA to deal with, always messing up my queries and forms! They cause issues which are hard to track down. I much rather have things under MY control so that I know when they are broken and how to fix them. I make my own lookups using queries.

[kd2017]

I always read to avoid the lookup field type in regard to lists with basically a hidden lookup table, per aytee and Allen brown etc, so I don't use them. That said I just noticed today that on a table you can make a number type field for example (fk), go to the lookup tab in the table field editor, select display combobox, and use sql as a row source to lookup another table??? Do it once here so I don't have to do it on every form the field shows up. Is there anything wrong with utilizing this feature?

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

[ssanfu]

<snip>
That said I just noticed today that on a table you can make a number type field for example (fk), go to the lookup tab in the table field editor, select display combobox, and use sql as a row source to lookup another table??? Do it once here so I don't have to do it on every form the field shows up. Is there anything wrong with utilizing this feature?

Uhhhh...... this is known as a "Look up FIELD"!!

Which you referenced here:
The Evils of Lookup Fields in Tables

[kd2017]

Oh.... Okay clearly I haven't paid much attention to them and misunderstood what a lookup "field" was. I thought it was a field with just a list of items... simply a 'value list' not associated with a user defined table. My bad

[Athar Khan]

When used in queries, I think a Lookup field is only confusing when we refer to an ID Field in our query, which is always numeric, but we expect a string value in the query results.
For example if I want to list customer names in a combo box on a form, I use Lookup field. As a result, I see customer names in "CustomerID" field but when I want to use the value (of a column) of that combo box somewhere else, I expect the same customer name which is being displayed in "CustomerID" combo box field, but in fact, I don't have customer name saved in "CustomerID" field.
The mistake or confusion here is that I did not include the CustomerName field in my new query because I always see the customer name in "CustomerID" field on the form, which is actually a key column, but it displays the CustomerName which was requested by Lookup field query.

[June7]

Lookups with alias display set in table are fine if you know what is really happening. As you noted, the text alias would display, not the actual saved ID. This can be quite confusing to novices. Innumerable threads are posted by novices with this topic - 'Why can't I filter when I enter name?'. Then there's "Why does my crosstab show ID and not the text as column headers?"

When I view tables I want to see the actual stored value, not the alias. I build comboboxes on form.

Build queries that join tables to retrieve related descriptive text.