Changing the OrderBy of a subform programatically

[ojeffery]

I have a Form with a (Continuous) Subform that lists the results of a query. There's a header within the Subform that has several elements you can click on to programatticaly change the OrderBy of the Subform then refresh it. An example of the code I use is here:

Private Sub CompanySort_Click()
If (Me.OrderBy = "Company") Then
Me.OrderBy = "Company DESC"
Else
Me.OrderBy = "Company"
End If
Me.Refresh
End Sub

This works fine when the Form (which is named "Results Subform") is run by itself, but doesn't work when run as part of the parent Form ("Results"). The name of the Subform control is "Report subform", can't remember why it's different now...

What am I doing wrong? I'm sure it's simple, but I'm stumped, any help is much appreciated.

[TheSmileyCoder]

Im not really sure why that is happening.

First off, are you sure the code is running? Have you tried stepping through it?

[ojeffery]

Thanks for your reply. Yes, code appears to be running - I've tried putting msgboxes after each significant bit of code to check, which still come up. It works fine when the "Results Subform" is open as a form in itself, but not when it's a subform. Could it be that "me" is referring to the parent rather than the subform?

[sierra7]

Instead of Me.Refresh try;

Me.OrderByOn = True

Works for me!
S7

[TheSmileyCoder]

I would try to start with testing Sierra7's suggestion.

To refer to the parent form you use Me.Parent.

[ojeffery]

Brilliant, that works! Thanks to sierra7 and TheSmileyCoder!

[NeoPa]

Setting Me.OrderByOn = True certainly works when it wasn't previously set, but if you find it doesn't when changing from one sort order to another (In other words it works only for the first change - Help doesn't specify and I don't have time to set up a test myself) then a .Requery call should do it for you.