Lookup fields in tables

[Patti]

I read a discussion thread yesterday and don't remember
which one it was and can't find it now, but it referred
the sender to an article about The Evils of Lookup Fields
in Tables.( I printed the artice but don't remember the
HTML site I found it on.) Some of the information in the
article was not clear to me and so I have a few questions:

What I understood from the article was that is was better
to set the lookup fields on the form via list boxes or
combo boxes rather than in the table structure. Is that
correct and, if so, why? And my next question is, if we
are discouraged from setting up the lookup fields during
the table structure, why is that option even available?

The example the article used was company names field. It
said that "Any query that used that lookup field to sort
by that company name won't work. Nor will a query that
uses a company name in that field as a criteria...."
I have not found that to be a problem.

I am just in the process of beginning a large database
project and have set some Lookups via the table
structure. I want to fix it now before it gets too
complex.

Any thoughts on this???

Thanks, Patti

 

[Allen Browne]

Hi Patti

You are probably referring to this article:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

One of the obvious problems is that if you use the Lookup wizard,then what
you see is NOT what you get. You may be storing a CompanyID number, but
actually viewing the company name. This causes great confusion: when you try
to match on the name (but it is actually a number), it works in some
contexts but not in others, you end up using the incorrect delimiters and
getting no results at all, and so on. It is just plain wrong to have your
tables and queries displaying something that is NOT in the field, and causes
so much confusion.

Your 2nd question was, "Why is the option even available?" Good question!
Someone at Microsoft thought they were being helpful. They were wrong.

You will find the problem. For example, if you get a filter working on your
form that includes this lookup field, and then attempt to print a report
using the same filter, the report will barf.

There are other reasons why it is a bad idea also, e.g. spurious indexes.

Best advice would be to go back to your tables, and on the Lookup tab (lower
pane in table design view), set the Display Control back to text box.

 

[Patti]

Yes, that was the article I was referring to. Thanks,
your explanation was a little more clear. I have started
to change the few tables that I created. So, in my
existing form, I would delete the field "company" and
replace it with a combo box, referencing that
to "company" field. Is that correct?

Also, is it recommended to design a form from a query
rather than directly from the table. (This should
probably be asked in the Query Discussion threads but
will ask it here since its somewhat related to my
original question). It seems it would be easier to
add/change fields in the future from a query?

Thanks!

 

[Allen Browne]

Yes, on the form you would use a combo box for Company, so the end-user sees
the the company name instead of the value of the CompanyID. To put the combo
on the form you can:
- click combo in the toolbox, then drag the CompanyID field from the Field
list onto the form, or
- if you already have the CompanyID text box on the form, right-click it and
Change To | Combo.

It does not matter whether you bind your form directly to a table, or to a
query. Use a query if you want to select only some fields (more efficient),
only some records (e.g. exclude the inactive ones), or wish to sort by a
particular field. If you bind the form to the table, Access will probably
create a hidden query anyway, show all records and fields, and sort by the
primary key of the table.

 

[Patti]

Thanks, Allen!

-----Original Message-----
Yes, on the form you would use a combo box for Company, so the end-user sees
the the company name instead of the value of the CompanyID. To put the combo
on the form you can:
- click combo in the toolbox, then drag the CompanyID field from the Field
list onto the form, or
- if you already have the CompanyID text box on the form, right-click it and
Change To | Combo.

It does not matter whether you bind your form directly to a table, or to a
query. Use a query if you want to select only some fields (more efficient),
only some records (e.g. exclude the inactive ones), or wish to sort by a
particular field. If you bind the form to the table, Access will probably
create a hidden query anyway, show all records and fields, and sort by the
primary key of the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.