Order By not working when record source set by vba

I had a continuous form in my database which was bound to 1 query and
filtered it based on combo box values.
All the fields (apart from the combos) where locked and disabled to make
them view only.
The form also had labels for each of the columns and if you clicked it, vba
code would order the form by that column's data, click another label and it
would orderby that column instead.

All was fine until i decided that it would be more efficent if the form used
different queries for each of the criteria instead of holding all the records
in memory and filtering them.
That feature works fine, the vba code changes the record source according to
the option selected, but the Orderby feature no longer works.
If I put the form into design view and back again it retains the recordset
it had previously and the orderby feature works.

What Am i missing? Clearly when the record source is set by VBA something is
upsetting the orderby.

Any help would be appreciated.

Example of what i use to sort by the REL_Staus field is

Me.OrderBy = "REL_status"

and changing the record source is

Me.RecordSource = "REL_ByUser_Running"


Thanks

Mat

BTW - This was written for access 2003, but does exactly the same in 2007

"Mat Child" wrote:

I had a continuous form in my database which was bound to 1 query and
filtered it based on combo box values.
All the fields (apart from the combos) where locked and disabled to make
them view only.
The form also had labels for each of the columns and if you clicked it, vba
code would order the form by that column's data, click another label and it
would orderby that column instead.

All was fine until i decided that it would be more efficent if the form used
different queries for each of the criteria instead of holding all the records
in memory and filtering them.
That feature works fine, the vba code changes the record source according to
the option selected, but the Orderby feature no longer works.
If I put the form into design view and back again it retains the recordset
it had previously and the orderby feature works.

What Am i missing? Clearly when the record source is set by VBA something is
upsetting the orderby.

Any help would be appreciated.

Example of what i use to sort by the REL_Staus field is

Me.OrderBy = "REL_status"

and changing the record source is

Me.RecordSource = "REL_ByUser_Running"


Thanks

Mat

On Sat, 13 Feb 2010 00:31:01 -0800, Mat Child
wrote:

What Am i missing? Clearly when the record source is set by VBA something is
upsetting the orderby.

Any help would be appreciated.

Example of what i use to sort by the REL_Staus field is

Me.OrderBy = "REL_status"

What you're missing is that you must *also* set the form's OrderByOn option to
True:

Me.OrderByOn = True

This property is off by default.
--

John W. Vinson [MVP]

Brilliant, that was the missing link

Thank you very much

Mat

"John W. Vinson" wrote:

On Sat, 13 Feb 2010 00:31:01 -0800, Mat Child
wrote:

What Am i missing? Clearly when the record source is set by VBA something is
upsetting the orderby.

Any help would be appreciated.

Example of what i use to sort by the REL_Staus field is

Me.OrderBy = "REL_status"

What you're missing is that you must *also* set the form's OrderByOn option to
True:

Me.OrderByOn = True

This property is off by default.
--

John W. Vinson [MVP]
.