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 am not sure we are on the same page all the way through, so I want to verify some assumptions.
"... in a form I have a field called Members."
"...I can then tick the checkboxes for the people who attended the meeting,..."
That sounds like you have a combo (dropdown) bound to a field the table. It also sounds like that field is actually set up as a Multi-Value Field (MVF) in the table itself.
You mention a query in the field for Members. That further suggests a Multi-Value Field using a lookup into another table.
Are those assumptions correct? If so, we have some heavy lifting to do to get you to a resolution.
Actually a screenshot of the form in design view showing that control would be helpful.
Also a screenshot of the table in design view would be helpful.