I have added a button to my main form "Clients" that should sort data on a subform "Contacts" according to field "Group Type" A-Z. Can anyone help me out with the code that should go in "On Click" event for button that will move focus to subform field and sort that field alphabetically?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Sort Button 2
- Thread starter cstuart79
- Start date
I use many 'list' forms, all of which are sortable and filterable.
I use generic functions for this. Every sorting button (used as the column label) is named like "cmdSort_[myFieldName]".
The onclick event is set to "=fSort()", which is in the form module:
The function gfCommon_ListSort codes for:
- sort first by ASC, another click sort DESC
- specific forms sort more than one column
- or else, sort by only the clicked column button
The function is in a module 'basCommon':
I have filtering text boxes as well (at bottom of columns) that allow for operators >, <, To, and Null, which work on basically the same principle, but obviously the code is a bit more involved.
HTH.
Max Hugen
Australia
I use generic functions for this. Every sorting button (used as the column label) is named like "cmdSort_[myFieldName]".
The onclick event is set to "=fSort()", which is in the form module:
Code:
Function fSort()
On Error Resume Next
Call gfCommon_ListSort(Me)
End FunctionThe function gfCommon_ListSort codes for:
- sort first by ASC, another click sort DESC
- specific forms sort more than one column
- or else, sort by only the clicked column button
The function is in a module 'basCommon':
Code:
Option Compare Database: Option Explicit: Const cMODULE$ = "basCommon"
Public Function gfCommon_ListSort%(frm As Form)
'
' 2009-07-27 Sort the specified list
'
On Error GoTo Err_gfCommon_ListSort: Const cPROC$ = "gfCommon_ListSort"
Dim ctl As Control
Dim fldName$, S$, sort_direction$
Const cSORT_PREFIX = "cmdSort_"
Set ctl = Screen.ActiveControl
fldName = Mid(ctl.Name, Len(cSORT_PREFIX) + 1)
' The Tag property is used to determine the sort direction
sort_direction = IIf(ctl.tag = "ASC", "DESC", "ASC")
ctl.tag = sort_direction
Select Case frm.Name
Case "tracking_list"
Select Case fldName
Case "csi"
S = "" & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "space_name"
S = "" & _
"space_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "element_name"
S = "" & _
"element_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "product_name"
S = "" & _
"product_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "product_size"
S = "" & _
"product_size " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "tracking_desc"
S = "" & _
"tracking_desc " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction
Case "source"
S = "" & _
"source " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "um"
S = "" & _
"um " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "bp_name"
S = "" & _
"bp_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "phase"
S = "" & _
"phase " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
End Select
Case "sov_list"
Select Case fldName
Case "bp_name"
S = "" & _
"bp_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "csi"
S = "" & _
"csi " & sort_direction & ", " & _
"bp_name " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "space_name"
S = "" & _
"space_name " & sort_direction & ", " & _
"bp_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "element_name"
S = "" & _
"element_name " & sort_direction & ", " & _
"bp_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "product_name"
S = "" & _
"product_name " & sort_direction & ", " & _
"bp_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_size " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "product_size"
S = "" & _
"product_size " & sort_direction & ", " & _
"bp_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"tracking_desc " & sort_direction
Case "tracking_desc"
S = "" & _
"tracking_desc " & sort_direction & ", " & _
"bp_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"space_name " & sort_direction & ", " & _
"element_name " & sort_direction & ", " & _
"product_name " & sort_direction & ", " & _
"product_size " & sort_direction
End Select
Case "sov_list_change"
Select Case fldName
Case "bp_name"
S = "" & _
"bp_name " & sort_direction & ", " & _
"csi " & sort_direction & ", " & _
"tracking_name " & sort_direction
Case "csi"
S = "" & _
"csi " & sort_direction & ", " & _
"bp_name " & sort_direction & ", " & _
"tracking_name " & sort_direction
Case "tracking_name"
S = "" & _
"tracking_name " & sort_direction & ", " & _
"bp_name " & sort_direction & ", " & _
"csi " & sort_direction
End Select
Case "sovList_list"
Select Case fldName
Case "vendor_name"
S = "" & _
"vendor_name " & sort_direction & ", " & _
"sov_date " & sort_direction & ", " & _
"document_type_code " & sort_direction & ", " & _
"change_number " & sort_direction
Case "sov_date"
S = "" & _
"sov_date " & sort_direction & ", " & _
"vendor_name " & sort_direction & ", " & _
"document_type_code " & sort_direction & ", " & _
"change_number " & sort_direction
Case "document_type_code"
S = "" & _
"document_type_code " & sort_direction & ", " & _
"vendor_name " & sort_direction & ", " & _
"sov_date " & sort_direction & ", " & _
"change_number " & sort_direction
Case Else
S = fldName & " " & sort_direction
End Select
Case "invoiceList_list"
' note different sorting in this form
Select Case fldName
Case "vendor_name"
S = "" & _
"vendor_name " & sort_direction & ", " & _
"invoice_date ASC"
Case "invoice_date"
S = "" & _
"invoice_date " & sort_direction & ", " & _
"vendor_name ASC"
Case Else
S = fldName & " " & sort_direction
End Select
Case Else
S = fldName & " " & sort_direction
End Select
frm.OrderBy = S
frm.OrderByOn = True
frm!txtPlaceHolder.SetFocus
' Reset Tags for other controls.
Set ctl = Nothing
For Each ctl In frm.Controls
If Left(ctl.Name, Len(cSORT_PREFIX)) = cSORT_PREFIX And Mid(ctl.Name, Len(cSORT_PREFIX) + 1) <> fldName Then ctl.tag = ""
Next
Exit_gfCommon_ListSort:
Set ctl = Nothing
Exit Function
Err_gfCommon_ListSort:
MsgBox cMODULE & vbCrLf & cPROC & vbCrLf & vbCrLf & Err & ": " & Err.Description
Resume Exit_gfCommon_ListSort
End FunctionI have filtering text boxes as well (at bottom of columns) that allow for operators >, <, To, and Null, which work on basically the same principle, but obviously the code is a bit more involved.
HTH.
Max Hugen
Australia
- Status
Similar threads
- Locked
- Question