Lookup fields

[Luis Miguel]

One of the advices on MVPS.org is not to use lookup fields in tables, their
commandment reads:
'thou shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One'

I have never seen this kind of advice in an Access book. Could somebody
explain a technique on how to replace the functionality of lookup fields?

Thanks,
Luis.

 

[Michel Walsh]

Hi,


A possible problem with Lookup Field is that it displays data that is
not the one stored in the table, and may mislead someone not aware of it.
Personally, I use them often, but reformat the list so that confusion is
less possible.


Hoping it may help,
Vanderghast, Access MVP

 

[Luis Miguel]

Thanks for your prompt response.

I think I did not explain this correctly. I meant a field in a table that is
a Combo Box and its "row source type" is a Table/Query. That's what I though
they were referring to.

Is there anything wrong with this setup?

tblEmployees
employee_id (autonumber, primary key)
first_name
last_name
supervisor_id (number, Combo box, row source type: Table/Quey {Name:
tblSupervisor.first_name & " " tblSupervisor.last_name}, column width:
0";2")

tblSupervisor
supervisor_id (autonumber, primary key)
first_name
last_name

qrySupervisor
Name: tblSupervisor.first_name & " " tblSupervisor.last_name

In other words there is a relationship between tblEmployees.supervisor_id
and tblSupervisors.supervisor_id. I use the query to form the Name with the
first_name and the last_name fields, and I hide the supervisor_id field
making the first column's width zero.

Thanks,
Luis.

 

[Jeff Boyce]

Luis

Michel's response was germane -- if you've defined a table field as "lookup"
data type, the table will display the "looked up" value, but hold the
underlying key value (a foreign key).

One issue with this happens when querying against the table. It would be
(and is, based on 'group posts) too easy to use one of the displayed values
as a criterion, and then be frustrated when no rows are returned. No rows
would be returned because the actual value in the field is a key (the stored
value), not the displayed value.

Good luck

Jeff Boyce
<Access MVP>

 

[John Vinson]

I have never seen this kind of advice in an Access book. Could somebody
explain a technique on how to replace the functionality of lookup fields?

The criticism is primarily directed against the use of Table
Datasheets for data entry. Sure, a Lookup field makes it easier to
enter data directly into a table datasheet; and if you know its
drawbacks and limitations (creating redundant indexes and
relationships even if they already exist, concealing the actual
contents of your table from view, making it much harder to sort or
search the table, ...) and are comfortable with it, by all means go
ahead.

But in any professional application, the users will NEVER see table
datasheets - all interaction with the data occurs on Forms. And it's
perfectly straightforward to put a Combo Box (a "lookup") on a Form,
with or without the field being defined as a Lookup Field in the
table.

 

[Luis Miguel]

John, I understand your point about not using datasheets for data entry and
would never let a user interact directly with them. That's why I didn't
mentioned anything about it.

Correct me if I'm wrong, instead of using the setup I explained in my
earlier post what I need to do to avoid the drawbacks and limitations you
mentioned is:
1 - Create a relationship between the fields employee_id in the two tables
2 - Create a combo box that looks up the formatted data (Name: first_name &
" " & last_name) in a form, but post the real value (employee_id) to the
foreign key on the other table

Thanks,

Luis.

 

[Luis Miguel]

You are absolutely right, if I run a query using the displayed values I get
a data type error message. In order to retrieve data I have to use the value
of the foreign key.

Thanks,

Luis.

 

[John Vinson]

Correct me if I'm wrong, instead of using the setup I explained in my
earlier post what I need to do to avoid the drawbacks and limitations you
mentioned is:
1 - Create a relationship between the fields employee_id in the two tables
2 - Create a combo box that looks up the formatted data (Name: first_name &
" " & last_name) in a form, but post the real value (employee_id) to the
foreign key on the other table

That's exactly the correct technique.