2b. Nor will a query that uses a company name in that field as a criteria.

Rebuttal: When writing queries, you do have to specify the criteria using the
underlying value rather than the user-friendly displayed value. However, you
have to assume that someone writing queries is not the target of the end-user
abstraction provided by lookups and will know to supply a numeric criteria
for “CompanyID.”

Having to use an invisible underlying value for a critieria has the potential
to be a real problem. If I want to look up a company by name, or if I want
to do a Like criteria, the lookup field seems to offer no help. In any case,
the OP spoke of end users constructing their own queries.

You also wrote "Wrong, Wrong, Wrong" at the idea of other databases not being
able to understand the lookup fields. In my test, exporting the lookup field
(to Excel, in my test) exported only the bound column. The Company name (to
use the current example) is not exported. This could prove inconvenient.

The article you attacked referred to the "user", which you took to mean the
end use only. In context it would seem to be any person working with the
database.

In your demonstration early in the blog post about the use of a lookup field
you showed the table being used directly for data entry. I would not expose
the table to the end users, as there is too much chance for them to do damage
to the data. I (and most developers) use forms for data entry, in which case
I would build by own combo box there, not in the table. You apparently would
send the users directly to the table for data entry, which would seem to
indicate you regard the practice as acceptable. The blog posting would have
been helped had you started out by saying so. If I misunderstand, what is
the point of building the combo box in the table rather than on the form,
other than that it is automatically a combo box when you drag it from the
field list to the form, or use autoform? If you regard the objections to
lookup fields as utterly without merit, do you go so far as to advocate their
use whenever possible?

JohnFx wrote:
Minor correction. I never said that I don't use Access much. I said "...don’t
do nearly as much Access development as I once did.."

More specifically, for a period of about 10 years I used it daily as one of
my primary tools. Recently I have been down to only about 5 hours a week
with it. I've been using Access since version 1.0 professionally and have
written countless apps in it and trained dozens of people how to use it over
those years.

I'd also like to know which "real objection" you think I glossed over. I
didn't find any of them to be valid and didn't think I implied otherwise.

If you want to talk about condescending, that blog post beats anything I've
seen in a while. The person writing it admits he doesn't use Access much,
[quoted text clipped - 24 lines]
return the primary key to the ID field to get the behavior I'm looking
for. Which means I get to do everyone's work for them.

--
Message posted via http://www.accessmonster.com