Evils of lookup fields?

I have been in the habit of using lookup fields liberally, but have noticed the you more advanced folks don't. I was just reading on another site about the 'evils' of lookup fields.

Why is it better to enter these as a value list from your main table? Say is I start with a list of 5 topics and later need to add 5 more, this can't be achieved with a data entry form, can it?

I'm starting on a new db this week and am trying to evolve. Thanks for any info.

I have found no real problems working with lookup fields, as long as they are based off of tables. Once you enter the values yourself, it is very time consuming to go and change them later. When they are based off of tables, you can easily add and detract from the listing. In the past I have been asked to create "user friendy" databases and found that the fewer opportunities you give the user to create an error (misspelling being #1) the better off you are.

I haven't had any problems with it either. And like you say you can easily change information. Thanks J, I was just wondering if it was that big a no-no. I don't think the more experienced kids on here use them that much. We're at least a crowd of two.

Lookup fields NOT Evil

I have been in the habit of using lookup fields liberally, but have noticed the you more advanced folks don't. I was just reading on another site about the 'evils' of lookup fields.

Click to expand...

- are you sure you're not confusing this with DLookup()?

Best practice is that lookup fields should be used whenever possible. However it is best to implement them with code tables so you can enforce referential integrity.

It is best to use a table rather than value lists. The problem with value lists is that changes to them do not propagate. For example, you create a value list in your table definition and it contains 3 values. You make a form. The value list is transfered to the combo on the form. Life is good. However, the combo on the form and the combo on the table are not sync'd in any way so you can change one without the other. Life is bad. Also, only you can be trusted to change these values. You don't want your users updating the forms or tables themselves. That's why code tables are so much better. 1. They are consistant, 2. You can provide an interface so they can be maintained by users without bothering you, 3. You can enforce RI with tables.

PS, take a look at this post to see how I implement my code tables.
http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=205507#post205507