Lookup fields Q? -shocked and awed-
Greg
I read the "10 commandments" and subsequently, the "evils
Lookup fields" on "The Access Web"
(http://www.mvps.org/access/)recently. I was intrigued
and motified. How do you keep end users from having to
manually enter their values for routeen entries. (i.e.
assigning an attending nurse form your staff list to a
particular care event you want to catelog). I have been
opperating under the impression that the best way to hold
down input errors ( not to mention increas user
friendliness) was to use lookup feilds. Some one tell me
what I am missing. Thanks
Greg
Rick Brandt
news:071a01c33c60$e0e61d50$a301...@phx.gbl...
Lookup "tables" which hold values for use in data entry that
are referenced in queries or with Combo and List boxes are
fine. Lookup "fields" designed into the table structure as
is possible in Access is what people discourage.
grge
Greg
>.
>
Albert D. Kallal
> http://www.mvps.org/access/lookupfields.htm
The above is saying to not use the look feature AT THE TABLE LEVEL.
You most certainly will as a matter of general design use lookups, but NOT
at the table level. You will, and should have tons of lookups in your
application. The above is simply saying don't use the table lookup feature.
In other words, don't define the lookup in the table design view.
You don't have this feature in Oracle, or in Sql-server. If you start using
the feature in ms-access, you will wind up with a mess.
You most can, and certainly will use joins. You most can, and certainly
will use combo boxes to lookup values. You most can, and certainly will use
joins in sql to lookup values.
You most can, and certainly will set relations in the query builder.
So, a very large portion of the time you will still use a combo box to do a
lookup. You should, and will have tons of look ups in your application.
The very essence of a relational database is relations to other tables.
However, don't touch that built-in lookup feature AT THE TABLE level in
ms-access.
When you drop a lookup field on to a form from the field list view, it gets
turned into a combo box anyway. So, the substitute for this is lookup field
is simply use the combo box wizard. So, AVOID THE LOOKUP feature and DO NOT
define this lookup in the table design. Of course you will define the
relationship
for the tables in the relationships window as always.
Many people state that the reason to avoid using the table lookup is because
then you don't know what is going on. I consider that a lame argument.
You can create views, or "queries" in ms-access that does the join for you.
There is NOTHING wrong with this.
The real reason why you don't use the table lookup feature is because then
you can't get to the actual value used in the table! If you reference the
field you get the lookup value! This is fine if you actually want to the
lookup value, but what if you need the actual value in the table, and NOT
the lookup? You are now in big trouble! Worse, you will find that reports
groups sort on the original "id" value, but display the lookup value!. This
means your sort orders are all messed up. So, you cant sort correctly, you
cant view, or use the id value. Gosh, how do you use the actual vale in the
field anymore? (answer, you can't!). It is a mess if you all of a sudden
need to use the actual field value. These pit falls are only a scratch of
the
problems that this lookup feature brings on.
If the lookup feature is NOT
bothering you, then I suggest you continue developing until you do find this
out on your own. It is not really that bad, and perhaps for some simple
things, it might be ok. The instant you start writing some code and working
with the data, you will hit a wall. This is just one of those things that
you can only learn by doing.
Good luck....and feel free to use the combo box wizard on a form..but go
near that lookup feature at the table level.
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
kal...@msn.com
http://www.attcanada.net/~kallal.msn
Albert D. Kallal
near that lookup feature at the table level.
I meant to say ....
but don't go near that lookup feature....
Greg
come accros that problem at various times but I usually
fond a work-around. Needless to say I havn't yet run ito
it writing code but, I'm still new at that peice. Some
things are worth learning by example rather than doing.
Ill take your work for it.
Thanks
Greg
>.
>
John Vinson
wrote:
>How do you keep end users from having to
>manually enter their values for routeen entries.
By providing them with a Form with Combo Boxes ("lookups", if you
wish) to select the desired values.
Reread the "evils of the lookup". It very explicitly says that what's
evil is the Table Lookup Field - using combo boxes in their proper
context, on Forms, is laudable and perfectly standard practice.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Albert D. Kallal
need to be able to suggest that one should avoid those pesky little guys,
but NOT be so harsh! Further, as mentioned, many people get away using them
without any trouble at all.
I mean, if some users are happy with the feature...then I really can only
suggest the downfalls, and after that it is simply a case of:
"Your mileage may vary"