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.
Consider this:
- Open your report in Design View.
- Go to Group, Sort, and Total (from the Design tab).
- Add a Sort on the field that indicates voting eligibility (e.g., CanVote DESC) and then by MemberName ASC.
Ug, I replied, but it appears to have disappeared.
So I did try your suggestion, but it didn't work.
After doing some digging, I see that the problem actually goes all the ways to my table. When I go into Design View for my table, for my Members field in LookUp, I have the following information:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: my query of my Members, with the custom sort
...
Limit to List: Yes
Allow Multiple Values: Yes
Allow Value List Edits: No
Show Only Row Source Values: No
The custom sort works fine in the query, but when I go into Datasheet View, my members are listed in order of their primary key again. I can't seem to bypass that.