Runtime error 438 and orderby controlsource problem

[lea-anne]

Hi,
I'm totally stumped on this one and hoping that someone can help. I have a database that is used in runtime mode, meaning that there are no sort options for the user. I've decided to add a couple of buttons (A-Z and Z-A) on the header of a continuous form and then call the following code in two public procedures. I want the user to be able to sort on any of the fields in the continuous form. However, it fails on the lines "frm.OrderBy = ctl.ControlSource" and in the other module "frm.Orderby = ctlControlSource & "DESC". I get a runtime error 438 object doesn't support this property or method. Any clues?

Thanks very much.

Public Function mySortDown()
Dim frm As Form
Dim ctl As Control

Set frm = Screen.ActiveForm

Set ctl = frm.ActiveControl

frm.OrderBy = ctl.ControlSource
frm.OrderByOn = True
End Function



Public Function mySortUp()
Dim frm As Form
Dim ctl As Control

Set frm = Screen.ActiveForm
Set ctl = frm.ActiveControl

frm.OrderBy = ctl.ControlSource & "DESC"
frm.OrderByOn = True
End Function

 

[Mihail]

I think that your frm.AcvtiveControl is the AZ button that you last pushed.
Do a try:
.....
Set ctl = frm.ActiveControl
MsgBox(ctl.Name)
.....

 

[lea-anne]

Thank you! That was exactly the problem! Now how do I get the control focus to go back to its previous location?

 

[Mihail]

Maybe this will help you:
http://msdn.microsoft.com/en-us/library/office/aa173205(v=office.11).aspx

If not, you should keep yourself the track:

Use a variable at form level:

Dim PreviousControl As Control

In the GotFocus events for certain controls update this variable:

Private Sub CertainControl_GotFocus()
PreviousControl = CertainControl
End Sub

Then use the value stored in PreviousControl in order to sort.

 

[lea-anne]

Thanks Mihail.
All sorted. Appreciate your help.

 

[spikepl]

See Screen.PreviousControl in the docs