Issues creating a lookup in a table

[Pat Hartman]

Hi Pat. I have never been able to get table lookups to work for me very well, so I never use them, but I have a question. If you do add a table lookup field to a form as suggested above, do you still get the same Event Procedure options such as Before Update and After Update as you do by adding a combobox to a form?

There is nothing different about how they behave. The bound column is still the ID. the issue is that most people don't understand that. They open the table or a query and they see the text. Therefore, they assume, incorrectly, that they are working with a string rather than the ID. So they add to the confusion by naming the FK with the name of the text field rather than the ID ensuring that they never know what they are working with. So they create queries that search on the text string but of course, the table holds the ID so nothing is found. They write code that works with a string but the field is actually an ID. That is just the tip of the iceberg. Over time, MS has actually fixed some of the issues but that made them not work for people who knew what they were doing.

 

[GPGeorge]

I've observed that the main problem with Lookup fields in tables is newbies who don't understand how they work are more likely to misuse them, whereas experienced developers who do understand how they work are far, far less likely to risk using them.

 

[LarryE]

There is nothing different about how they behave. The bound column is still the ID. the issue is that most people don't understand that. They open the table or a query and they see the text. Therefore, they assume, incorrectly, that they are working with a string rather than the ID. So they create queries that search on the text string but of course, the table holds the ID so nothing is found. They write code that works with a string but the field is actually an ID. That is just the tip of the iceberg. Over time, MS has actually fixed some of the issues but that made them not work for people who knew what they were doing.

Thanks

 

[spaLOGICng]

And you've been so very generous over the past 12 years with all your help to the folks who come here because they need it. You've amassed a whopping 162 posts. We will be happy to learn from you. I'll get my notepad out.

I am not the one here trying to run a popularity contest. I come here occasionally when I might have the time to offer some insight when I have the opportunity. It is not my goal to insult or otherwise condescend on the intelligence of others.

I have a business to run, a couple as a matter of fact, as well as being the IT Director for another consulting firm. I work 50-70 hours a week and have been for at least the last 20+ years. And for the record, the reason I did not respond sooner is that at some point this week I needed to run to the grocery store. But I just do not have the time to come in here and run races.

Thank you for the kind words. Once again, have a great day.

 

[Mike Krailo]

I am not the one here trying to run a popularity contest. I come here occasionally when I might have the time to offer some insight when I have the opportunity. It is not my goal to insult or otherwise condescend on the intelligence of others.

I have a business to run, a couple as a matter of fact, as well as being the IT Director for another consulting firm. I work 50-70 hours a week and have been for at least the last 20+ years. And for the record, the reason I did not respond sooner is that at some point this week I needed to run to the grocery store. But I just do not have the time to come in here and run races.

Thank you for the kind words. Once again, have a great day.

Do come back with whatever you have going on in your projects, this is just a hot button issue for many members. You sound experience to me, and I don't have any issue at all with table lookups for those that wish to work that way. You even stated that you remove them after your done designing your forms, so that's what exactly what you should do. So let's just move on to a different topic as this one has been beat to death many times over.