Double click Sorting 1

[BeallDon]

I am trying to get a Control to sort on dbl click either Ascending or Descending depending on the current state of the records - which would be in one of three conditions: if unsorted then sort as Asc; if sorted as Asc then re-sort as Desc; if sorted as Desc then re-sort as Asc.

This will be a Public Function in a module that can be called from any control in any form from OnDblClick.

Any suggestions?

Thanks in advance.

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BeallDon]

Well i currently have:

Public Function SortAsc()
DoCmd.RunCommand acCmdSortAscending
End Function

Public Function SortDesc()
DoCmd.RunCommand acCmdSortDescending
End Function

and had "Call SortAsc" in OnClick and "Call SortDesc" in OnDblClick, but then i got to thinking that i would prefer to have both in the OnDblClick but i don't know how to distinguish how the records are currently sorted in order to determine which way to go in an IF statement. Such as:

Public Function SortAll()
if (control is currently sorted in Descending order) then
DoCmd.RunCommand acCmdSortAscending
else
DoCmd.RunCommand acCmdSortDescending
end if

I'm looking for what to put in (control is currently sorted in Descending order).

Thanks

 

[BeallDon]

anyone have any suggestions?

 

[MajP]

Public Sub sortByControl(theControl As Access.Control)
  Dim strOrderBy As String
  Dim frm As Access.Form
  Dim strCntrlSource As String
  Set frm = Forms(CurrentObjectName)
  strOrderBy = frm.OrderBy
  strCntrlSource = theControl.ControlSource
  
  If strOrderBy = strCntrlSource Then
    strOrderBy = strCntrlSource & " DESC"
  Else
    strOrderBy = strCntrlSource
  End If
 
  frm.OrderBy = strOrderBy
  frm.OrderByOn = True

End Sub

use it like this

Private Sub strFirstName_DblClick(Cancel As Integer)
sortByControl ActiveControl
End Sub

Private Sub strLastName_DblClick(Cancel As Integer)
sortByControl ActiveControl
End Sub

 

[MajP]

I think I misunderstood what you were asking. When you said any control in any form, I thought you were referring to the bound control. So my double click event is tied to the control that I want to sort by, may not be what you are looking for. You probably want to have a single button that you can toggle. In that case call the procedure with something like.

Public Function sortByControl(theControl As Access.Control) As String
  On Error GoTo errLabel:
  Dim strOrderBy As String
  Dim frm As Access.Form
  Dim strCntrlSource As String
  Set frm = Forms(CurrentObjectName)
  strOrderBy = frm.OrderBy
  
  Select Case theControl.ControlType
     Case acTextBox, acComboBox, acListBox
     strCntrlSource = theControl.ControlSource
     If strOrderBy = strCntrlSource Then
       strOrderBy = strCntrlSource & " DESC"
       sortByControl = "DESC"
     Else
       strOrderBy = strCntrlSource
       sortByControl = "ASC"
     End If
     frm.OrderBy = strOrderBy
     frm.OrderByOn = True
  
     Case Else
       MsgBox "Click in a field to sort."
     End Select
     Exit Function
errLabel:
  MsgBox Err.Number & " " & Err.Description
End Function

I modified this into a function so that it returns the sort direction. Now with a single command button I sort on any field. With the return value you can toggle the command button.

Private Sub Command80_Click()
Screen.PreviousControl.SetFocus
Command80.Caption = sortByControl(ActiveControl)
End Sub

 

[joebb3]

MajP or anyone,

I used your idea, but simplified it a bit... Only problem is... Its not working.

Well.. Not true. The data I am sorting is on a subform.

If I load the subform by itself, the code works wonderfully!

However loaded AS A subform it breaks. I understand why... I just can't wrap my mind around the code to fix it.

Here is what I have:

Public Function sortByControl(theControl) As String
  On Error GoTo errLabel:
  Dim frm As Access.Form
  Set frm = Forms(CurrentObjectName)
        strOrderBy = theControl
        frm.OrderBy = strOrderBy
        frm.OrderByOn = True
  Exit Function
errLabel:
  MsgBox Err.Number & " " & Err.Description
End Function

I call the function from a text label on the subform with an onClick event like:

Private Sub btnLastAsc_Click()
sortByControl "staffLast Asc"
end sub

The parent form name is "frmNewUsers"
The subform is "frmNewUserssub"

I hope you can make heads or tails of that and help.

Thanx,
Joe

 

[MajP]

Now that I look at this I probably could have done this a little smarter.

I pass in a control object to the subroutine. A control has a Parent property which is the form.
So instead of
Set frm = Forms(CurrentObjectName)
use
set frm = theControl.parent

I think the previous code is setting frm to the main form not the subform.

However, your code is not similar to what I wrote. I work with a control object and you are passing a string.

If you want to go with what you got then you can not use the fix I just suggested.

Public Function sortByControl(theControl as string, frm as access.form) As String
  On Error GoTo errLabel:
        frm.OrderBy = theControl
        frm.OrderByOn = True
  Exit Function
errLabel:
  MsgBox Err.Number & " " & Err.Description
End Function

and call pass it the form as well

Private Sub btnLastAsc_Click()
  sortByControl "staffLast Asc" , me
end sub

This assumes that btnLastAsc is on the subform.
if the btnLastAsc is on the mainform then do not pass me but pass the subform by something like
me.yoursubformcontrolname.form

 

[joebb3]

MajP,

What can I say. You are a god.

Thanx, it worked like a charm.

Joe