Filtering by Name seems to remove Sorts...

[MatthewGrace]

I have a form in datasheet view, whose Recordsource contains both an Order Date field and a CustomerFK field. The Orderdate field is sorted ASC.

Simply right clicking the CustomerFK field and Filtering by say "3", naturally yields BOTH all orders from CustomerFK=3 AND preserves the Orderdate field sort ASC. Of course, since nobody likes to Filter by CustomerFK, but rather, the customers actual name, I use a combobox populated with the FK in the bound field and the Name in the second column. No problem.

The kicker is this: Filter by the Customer's actual name and you lose the sort on Orderdate ASC. Not so if you filter by the FK number directly. Why so?



I figure it's a common issue. I've made a super-simple db to illustrate with 2 forms, one working, one not. What is the accepted way of circumventing this?

Filtering removes sorting.zip

[June7]

I don't know that there is a circumvention. I never observed this because don't have any db with lookup alias. Don't ask me why, it's just the data I work with.

In my primary, large db I disable ribbon and right click menu. I build custom filter tools on form using VBA to construct filter string and apply to form Filter property.

[MatthewGrace]

Interesting. I have a feel it's a natural limitation of the intrinsic datasheet controls that ship with Access. I've notice a bunch of little "if's and's and bugs" with form controls even as a fairly low level Access User. I guess this is just one more.

I can either resolve it June's way, writing her own filter tools, which makes sense. The only other way I can think of is to use the ApplyFilter event to "tack on" '[DateOrdered] ASC" to the .Orderby property when it's warranted, so it doesn't get lost in the mix. Hmph.

Will mark as solved shortly, I'll let anyone else weigh in tho.