Forum Discussion
Sort in Query not Displaying
I had time to review this again. I think I spotted the reason this does not work. Your screenshot does not show that you actually removed the Lookup and replaced it with a normal number field containing the foreign key. The screenshot shows the Lookup field in the properties DISPLAYS only a textbox. That means the problem remains in the Lookup itself.
One of the reasons so many seasoned Access developers strongly recommend against use of Lookup fields is problems like this one are so hard to resolve for less experienced developers. Sure, they look snazzy. And if you really know what you're doing, they can be implemented with minor difficulties. In most cases they create more complications than they are worth.
I recommend you create a new field for Members. Make it a number of Long Integer type. Do not use the Lookup Wizard to create it.Update it with the MemberID field corresponding to the current values in the original field. Then delete this Lookup field.
Handle the row source for a combo box on the form instead.
I might get back with more thoughts after having had time to wade through the screenshots and sample SQL.
I had time to review this again. I think I spotted the reason this does not work. Your screenshot does not show that you actually removed the Lookup and replaced it with a normal number field containing the foreign key. The screenshot shows the Lookup field in the properties DISPLAYS only a textbox. That means the problem remains in the Lookup itself.
One of the reasons so many seasoned Access developers strongly recommend against use of Lookup fields is problems like this one are so hard to resolve for less experienced developers. Sure, they look snazzy. And if you really know what you're doing, they can be implemented with minor difficulties. In most cases they create more complications than they are worth.
I recommend you create a new field for Members. Make it a number of Long Integer type. Do not use the Lookup Wizard to create it.
Update it with the MemberID field corresponding to the current values in the original field. Then delete this Lookup field.
Handle the row source for a combo box on the form instead.
You are absolutely correct. Moving Members to it's own form and query solved the issues. I seriously cannot thank you enough! :)
Unfortunately I suspect our Access database is littered with other Lookup Fields from the original developer, and possibly myself for not knowing any better. At least I know the solution now and can tackle issues as they come up.
