Sorting a SubForm

[aintnorock]

I have a relatively simple relational database. One of my forms is based
on a query with a 3 parameter sort: Status / Priority / Customer.

When I open the form, the records are sorted properly.

When I go to filter the form, to get a subset of the form, e.g. filter
by Responsible Party, I would like the form to sort this way:

Reposponsible Party / Status / Priority / Customer.

However, when I do the Filter by Form, the sort gets all screwed up, so
much so that I can't even determine that it is sorted in ANY order - it
appears random.

Virtually all my forms are doing the same thing - when I filter the
form, it loses its sort.

Help!

thanks,
carl



*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Re: Sorting a SubForm

There are timing issues if the form or subform's RecordSource is a query
that uses parameters that read values from the form or subform itself. Then
if you apply a filter to the form and the subform, there are bugs in Access
where it gets confused about which has a filter applied. If you try to sort
it out by setting the OrderBy and OrderByOn properties, there are further
bugs where Access gets so confused that the entire approach is unworkable.

If you want to seedetails of the bugs and a sample database to demonstrate
them, bring up this article:
Incorrect filtering
at:

http://allenbrowne.com/bug-02.html

The simplest way around the entire problem is to programmaticall y set the
RecordSource of your subform instead of relying on the Filter and OrderBy
properties. The RecordSource can handle both the filtering (the WHERE clause
of the query statement), and sorting (the ORDER BY clause.) Typically, you
would set this in the AfterUpdate event of some control and/or the Current
event of the form or subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aintnorock " <nospam@xyz.com > wrote in message
news:gCjsf.27$C L3.2342@news.us west.net...[color=blue]
>
> I have a relatively simple relational database. One of my forms is based
> on a query with a 3 parameter sort: Status / Priority / Customer.
>
> When I open the form, the records are sorted properly.
>
> When I go to filter the form, to get a subset of the form, e.g. filter
> by Responsible Party, I would like the form to sort this way:
>
> Reposponsible Party / Status / Priority / Customer.
>
> However, when I do the Filter by Form, the sort gets all screwed up, so
> much so that I can't even determine that it is sorted in ANY order - it
> appears random.
>
> Virtually all my forms are doing the same thing - when I filter the
> form, it loses its sort.
>
> Help!
>
> thanks,
> carl[/color]