Lookup Field

[Al]

Hio

I am trying to create a lookup on a number field (Indexed Duplicates OK) but
when I select the Lookup Wizard, i am getting an error message telling me
that I cannot change the data type of field size; its part of one or more
relationships. The problem is that as far as i can see it isn't. There are
no relationships defined

Anyone any ideas please?

Thanks
Al

 

[Duane Hookom]

Most of us experienced developers avoid lookup fields. You might want to
read http://www.mvps.org/access/lookupfields.htm.

 

[Tanya Lee]

I saw your post and I must ask, if you don't use a lookup field, what would
you use that would provide the same result? Thanks.
Tanya Lee

 

[Lynn Trapp]

Tanya,
You need to understand the difference between a table level lookup, which is
what is being described in the article Duane referenced, and using a combo
box on a data entry form to look up a value from a table. The first one is
frowned on, the second one is encouraged.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Tanya Lee]

When I want to use a look up, for example, a list of departments, I create
the field Department in the appropriate table (say, employee information),
then I create a table that lists the departments, that can be added to as
time goes on. Then I use LookUp in that Department field in the first table
and connect it to that list of departments. Are you saying that is frowned
upon? Could cause problems later?

If so, are you saying that I should reserve that "look up" for when I create
my form, change that first field into a combo box that will look up the
departments in my 2nd table???

Thanks much!

 

[John Vinson]

When I want to use a look up, for example, a list of departments, I create
the field Department in the appropriate table (say, employee information),
then I create a table that lists the departments, that can be added to as
time goes on. Then I use LookUp in that Department field in the first table
and connect it to that list of departments. Are you saying that is frowned
upon? Could cause problems later?

Yes.

See http://www.mvps.org/access/lookupfields.htm for a critique of this
feature.

There's nothing wrong with Lookup Tables - and using combo boxes on
Forms to look up and insert data is perfectly proper and routine.

However, it is NOT necessary to define the field in your Table as a
Lookup Field in order to do this! You can use the Combo Box Wizard on
the Form to create a combo box which does exactly the same thing.

The one advantage of the Lookup Field type is that it makes creating
the form combo box a couple of mouseclicks easier. See the website
referred to above for the many disadvantages which, in my opinion,
outweigh this one small advantage.

If so, are you saying that I should reserve that "look up" for when I create
my form, change that first field into a combo box that will look up the
departments in my 2nd table???

That would be my recommendation.

John W. Vinson[MVP]

 

[Tanya Lee]

I never knew all of this - thanks so much everyone who contributed about this
post (which wasn't even mine - lol). Great information - too bad I already
developed my huge database with tons of lookup fields in tables...I hope it
doesn't come back to haunt me. In the future though I will do it the other
way - thank you!!

 

[John Vinson]

I never knew all of this - thanks so much everyone who contributed about this
post (which wasn't even mine - lol). Great information - too bad I already
developed my huge database with tons of lookup fields in tables...I hope it
doesn't come back to haunt me. In the future though I will do it the other
way - thank you!!

Well, Microsoft is moving even further toward the "lookup field"
philosophy in its newest release.

Used properly, with a THOROUGH understanding of their limitations and
what they are capable of doing, lookup fields... well, aren't all bad.
They do make creating and editing forms a bit easier.

However, I'd still argue that - to the extent that they encourage you
to use table datasheets as a user interface - they are A Bad Idea.

John W. Vinson[MVP]