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
|
|||
|
|||
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 ... |
#2
|
|||
|
|||
Smart alternative to lookup fields?
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 ... |
#3
|
|||
|
|||
Smart alternative to lookup fields?
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] |
#4
|
|||
|
|||
Smart alternative to lookup fields?
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] |
#5
|
|||
|
|||
Smart alternative to lookup fields?
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] |
#6
|
|||
|
|||
Smart alternative to lookup fields?
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 ... |
#7
|
|||
|
|||
Smart alternative to lookup fields?
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] |
Thread Tools | |
Display Modes | |
|
|