Forum Discussion
Sort in Query not Displaying
I have no clue how to word this. Basically, in a form I have a field called Members. This field is a drop down menu that lists all of the Members for a meeting via check boxes. I can then tick the checkboxes for the people who attended the meeting, click 'ok', and then when when I run the report, their names will appear. I don't need this information saved; just to display on the report so I can print it.
Here's my problem. Not all of the members can vote, so I want the members who can vote listed first, and the rest of the members listed next. I've put in my custom sort formula in the Members query. When I open my Members dropdown box, they are listed exactly the way I want. However, as soon as I tick on the names and then click 'ok,' it's displayed in order of the primary key. When I run the report, the names are still sorted by the primary key. I've gone to every query and table I can find to create my custom sort, but it keeps defaulting to sorting by the primary key. It's driving me INSANE.
Any idea why it's doing this? I've dug through google and haven't had luck finding an answer that works.
Thanks in advance!
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.
16 Replies
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.
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.
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.
Tried that, but it didn't work.
I did some more digging, and it's actually my table that isn't sorting right. When I go to design view for my table, in LookUp, my Display Control is: Combo Box and my Row Source is a query. The custom sort is done in the query. When I go to Datasheet View, the custom sort I did is ignored.
The problem originates in the use of a Lookup field in the table. While it is supported in Access tables, and very few other databases, using an interface feature like a Lookup field in a table is generally a good way to create problems like the one you face here. It's a mash up of tables and interface that simply leads to complications best avoided in the first place. Unless you have a very simple database indeed.
The more appropriate, more easily managed approach is to keep interface features like combo boxes, or lookup fields, in forms, where they belong.
I suggest you convert the lookup field in the table to a number of long integer data type. In that field, you store only the Foreign Key values for the related records.In the combo box on the form, create the sorted row source to display to the user.
