If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup fields
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. |
#2
|
|||
|
|||
Lookup fields
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" wrote in message ... 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. |
#3
|
|||
|
|||
Lookup fields
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. "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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" wrote in message ... 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. |
#4
|
|||
|
|||
Lookup fields
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 |
#5
|
|||
|
|||
Lookup fields
On Thu, 17 Jun 2004 07:03:33 -0400, "Luis Miguel"
wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#6
|
|||
|
|||
Lookup fields
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. "John Vinson" wrote in message ... On Thu, 17 Jun 2004 07:03:33 -0400, "Luis Miguel" wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#7
|
|||
|
|||
Lookup fields
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. "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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 |
#8
|
|||
|
|||
Lookup fields
On Thu, 17 Jun 2004 17:58:11 -0400, "Luis Miguel"
wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Thread Tools | |
Display Modes | |
|
|