Smart alternative to lookup fields?

Okay, I'm hearing there is 'great evil' underneath the coding for Lookup
fields, so ...
What is the best alternative?
--
dBaser from back in the day ...

Name your lookup field for example ColorID and make it Number data type -
long integer. Create a table:
TblColor
ColorID
Color

Create a relationship between your lookup field and ColorID in the table.

Now, when you want to enter a record in your table with the lookup field,
use a form that contains a combobox or listbox whose rowsource is TblColor.

Steve



"BJ" wrote in message
news
Okay, I'm hearing there is 'great evil' underneath the coding for Lookup
fields, so ...
What is the best alternative?
--
dBaser from back in the day ...

On Fri, 10 Jul 2009 13:23:17 -0700, BJ wrote:

Okay, I'm hearing there is 'great evil' underneath the coding for Lookup
fields, so ...
What is the best alternative?

There's nothing wrong with lookup Tables.
There's absolutely nothing wrong with combo boxes on forms ("lookups" if you
will).

What people object to is the "Lookup Field" * in a Table *.

Tables should be used only for data storage. A lookup field *in a table*
interferes with that goal by concealing the actual contents of the field from
view, making it harder to construct queries based on the field (e.g. a sort or
a criterion on the field will refer to the hidden numeric ID, not the visible
value), etc.

Having a lookup field saves you maybe 10 seconds when it comes to adding a
combo box to a Form. This is its ONLY virtue, and IMHO it's far outweighed by
its disadvantages.

Just use the Combo Box wizard in the toolbar to add combo boxes to your forms,
and plan to do all your interaction with data via Forms, not via table
datasheets.
--

John W. Vinson [MVP]

I've got two textbooks (course material for community college classes)
sitting in front of me for Access 2007 that don't explain any of this. One of
them actually explains "creating a field to look up values in another table".
When I read it I shouted "a-ha, that's just what I need!" - I am in the
design stages of a database which I think would have relied heavily on lookup
fields in tables had I not read about their evil ways ;

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

Now I think I understand why I shouldn't use lookup fields in tables and
what the best alternative is; I can still have my lookup tables (containing
the data to be "looked-up") but the act of "looking-up" will be done by
combo-boxes on my forms.

My question is; can you recommend a textbook for the novice - intermediate
user that understands that some features of Access 2007 should NOT be used,
and explains what the alternative is - for this particular case, and I'm sure
there must be others. Or is the look-up wizard the only egregious "feature"?




"John W. Vinson" wrote:

On Fri, 10 Jul 2009 13:23:17 -0700, BJ wrote:

Okay, I'm hearing there is 'great evil' underneath the coding for Lookup
fields, so ...
What is the best alternative?

There's nothing wrong with lookup Tables.
There's absolutely nothing wrong with combo boxes on forms ("lookups" if you
will).

What people object to is the "Lookup Field" * in a Table *.

Tables should be used only for data storage. A lookup field *in a table*
interferes with that goal by concealing the actual contents of the field from
view, making it harder to construct queries based on the field (e.g. a sort or
a criterion on the field will refer to the hidden numeric ID, not the visible
value), etc.

Having a lookup field saves you maybe 10 seconds when it comes to adding a
combo box to a Form. This is its ONLY virtue, and IMHO it's far outweighed by
its disadvantages.

Just use the Combo Box wizard in the toolbar to add combo boxes to your forms,
and plan to do all your interaction with data via Forms, not via table
datasheets.
--

John W. Vinson [MVP]

Well another one is the multi-value field. I would avoid this one except
in very rare cases since it is actually a set of hidden tables that
handle the multi-value field. You have no ability to manipulate the tables.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Derek wrote:
I've got two textbooks (course material for community college classes)
sitting in front of me for Access 2007 that don't explain any of this. One of
them actually explains "creating a field to look up values in another table".
When I read it I shouted "a-ha, that's just what I need!" - I am in the
design stages of a database which I think would have relied heavily on lookup
fields in tables had I not read about their evil ways ;

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

Now I think I understand why I shouldn't use lookup fields in tables and
what the best alternative is; I can still have my lookup tables (containing
the data to be "looked-up") but the act of "looking-up" will be done by
combo-boxes on my forms.

My question is; can you recommend a textbook for the novice - intermediate
user that understands that some features of Access 2007 should NOT be used,
and explains what the alternative is - for this particular case, and I'm sure
there must be others. Or is the look-up wizard the only egregious "feature"?




"John W. Vinson" wrote:

On Fri, 10 Jul 2009 13:23:17 -0700, BJ wrote:

Okay, I'm hearing there is 'great evil' underneath the coding for Lookup
fields, so ...
What is the best alternative?
There's nothing wrong with lookup Tables.
There's absolutely nothing wrong with combo boxes on forms ("lookups" if you
will).

What people object to is the "Lookup Field" * in a Table *.

Tables should be used only for data storage. A lookup field *in a table*
interferes with that goal by concealing the actual contents of the field from
view, making it harder to construct queries based on the field (e.g. a sort or
a criterion on the field will refer to the hidden numeric ID, not the visible
value), etc.

Having a lookup field saves you maybe 10 seconds when it comes to adding a
combo box to a Form. This is its ONLY virtue, and IMHO it's far outweighed by
its disadvantages.

Just use the Combo Box wizard in the toolbar to add combo boxes to your forms,
and plan to do all your interaction with data via Forms, not via table
datasheets.
--

John W. Vinson [MVP]

Thanks - confirms my thinking!
--
dBaser from back in the day ...


"Steve" wrote:

Name your lookup field for example ColorID and make it Number data type -
long integer. Create a table:
TblColor
ColorID
Color

Create a relationship between your lookup field and ColorID in the table.

Now, when you want to enter a record in your table with the lookup field,
use a form that contains a combobox or listbox whose rowsource is TblColor.

Steve



"BJ" wrote in message
news
Okay, I'm hearing there is 'great evil' underneath the coding for Lookup
fields, so ...
What is the best alternative?
--
dBaser from back in the day ...

Yes - that makes it much clearer - thank you!
--
dBaser from back in the day ...


"John W. Vinson" wrote:

On Fri, 10 Jul 2009 13:23:17 -0700, BJ wrote:

Okay, I'm hearing there is 'great evil' underneath the coding for Lookup
fields, so ...
What is the best alternative?

There's nothing wrong with lookup Tables.
There's absolutely nothing wrong with combo boxes on forms ("lookups" if you
will).

What people object to is the "Lookup Field" * in a Table *.

Tables should be used only for data storage. A lookup field *in a table*
interferes with that goal by concealing the actual contents of the field from
view, making it harder to construct queries based on the field (e.g. a sort or
a criterion on the field will refer to the hidden numeric ID, not the visible
value), etc.

Having a lookup field saves you maybe 10 seconds when it comes to adding a
combo box to a Form. This is its ONLY virtue, and IMHO it's far outweighed by
its disadvantages.

Just use the Combo Box wizard in the toolbar to add combo boxes to your forms,
and plan to do all your interaction with data via Forms, not via table
datasheets.
--

John W. Vinson [MVP]