RedBeard:
Now, if I right click in one of my combo boxes (Object Name) that is connected to a Lookup Table, and filter for Begins with “Book” then I should get all my records that start with “Book”, which I do. The problem is that they are not ordered by the Main Query that is sorted ascending by the Unique Code.
The fact that you see the main sort order that you expect is almost a random occurrence. There is no valid reason to expect that unless you know how it works, which seems doubtful considering some of your comments. Let me try to explain.
The sort order of the query is almost irrelevant. It may be effective on rare occasions where no other factors come into play, such as when you originally open the form.
Filtering by a specific field is not a guarantee of the results being sorted that way either. It just happens that no stronger indication is given for the sort order so Access is left to use whatever suits it best. In this case a simple sort by the filtered field is required to apply the filter and nothing tells it otherwise so it shows the data in the order that comes through. By this point the sort order of the query is entirely irrelevant and superseded. The sort order won't be 1=Filtered field; 2=Unique Code, but simply 1=Filtered field. Just as you're seeing, it would seem. Entirely to be expected in the circumstances.
Access doesn't automatically guess what order you want the records shown when an Access-level filter is applied. That is to say when a filter is applied using the interface provided by Access itself, rather than by you as the designer/programmer. To get what you want from this you'll need to add code to set the following four properties of the form :
- .Filter
-
.FilterOn
-
.OrderBy
-
.OrderByOn
Unfortunately, you will also need to design an interface that allows you to determine what the user wants in the way of a filter, which can often be somewhat less than straightforward.
NB. The
.OrderBy
&
.OrderByOn
properties are what Access uses to determine how it is you want the records sorted. If these aren't set then it will assume you don't care and it will work its magic. Then, what comes out is what's left when it's done what it needs to do.