Version 1.3           Approx 1 MB (zipped)                 First Published 30 Jan 2024                 Last Updated 10 Mar 2024


This is the sixth article in my series showing how functionality can be added to continuous forms.

You can sort continuous forms by specified columns using the right click context menu. For example:


However there is no visual indication when a sort has been applied.

This article shows how this feature can be added by clicking on the column headers.

The example app includes two forms with the same column sort functionality but different appearance. Both also allow filtering for each column.
Both forms use buttons instead of labels for column headers so that images can be added to the header of the sorted column as a visual guide.

The second form uses transparent buttons to imitate the standard appearance of continuous forms
However similar code can also be used with header labels without the images



Download

Click to download the example app:   ContinuousFormsSort_v1.3     ACCDB file - approx 0.9 MB (zipped)



Using the example app

The example app opens to a startup form:


Click Form with Sort Buttons. A continuous form opens with buttons as column headers:


By default, the data is sorted by Surname then Forename - both sorts are ascending (A->Z).
The sorted columns are highlighted in YELLOW and the sort information is also displayed in YELLOW in the form header.

Click any column header to sort by that column. The column header appearance changes with bold red text, a red border and a sort symbol.
The updated sort information is now displayed in the form header.


Each form header button runs a function GetOrderBy from its property sheet On Click event:

CODE:

Private OldControlSort As String
Private NewControlSort As String

'--------------------------------------------------------------

Private Function GetOrderBy(FldName As String)

On Error GoTo Err_Handler

      Application.Echo False

      strSelect = "SELECT PupilID, Surname, Forename, Gender, DateOfBirth, YearGroup, TutorGroup FROM Students"
      strWhere = Me.Filter

      ' Add up/down image to command buttons when used for sorting
      ' Up/down image with left aligned text

      NewControlSort = Screen.ActiveControl.Name

      'setup sort order
      Select Case FldName

      Case "Surname"
            If strOrderBy <> "Surname, Forename" Then
                  strOrderBy = "Surname, Forename"
            Else
                  strOrderBy = "Surname DESC, Forename"
            End If

      Case "Forename"
            If strOrderBy <> "Forename, Surname" Then
                  strOrderBy = "Forename, Surname"
            Else
                  strOrderBy = "Forename DESC, Surname"
            End If

      Case Else
            If strOrderBy <> "" & FldName & ", " & "Surname, Forename" Then
                  strOrderBy = "" & FldName & ", " & "Surname, Forename"
            Else
                  strOrderBy = "" & FldName & " DESC, " & "Surname, Forename"
            End If

      End Select

      'update form record source
      GetRecordSource

      'enable clear sort button
      cmdClearSort.Enabled = True

      'Highlight sorted columns as pale yellow
      For Each ctl In Me.Detail.Controls
            If ctl.ControlType = acTextBox And ctl.BackColor <> colPaleGreen Then ctl.BackColor = vbWhite
            If strOrderBy Like "*" & ctl.Name & "*" Then ctl.BackColor = colPaleYellow
      Next

      'clear existing sort column header format
      If Nz(OldControlSort, vbNullString) <> vbNullString Then
            Me.Controls(OldControlSort).Picture = vbNullString
            Me.Controls(OldControlSort).ForeColor = ColDarkGrey
            Me.Controls(OldControlSort).FontWeight = 400       'Normal
            Me.Controls(OldControlSort).BorderWidth = 1
            Me.Controls(OldControlSort).BorderColor = ColMidBlue
      End If

      'add picture to new sort column
      If strOrderBy Like "*DESC*" Then
            If Me.Controls(NewControlSort).Tag = "S" Then Me.Controls(NewControlSort).Picture = "up"
      Else
            If Me.Controls(NewControlSort).Tag = "S" Then Me.Controls(NewControlSort).Picture = "down"
      End If

      OldControlSort = Me.Controls(NewControlSort).Name

      'format sort column header
      If Me.Controls(NewControlSort).Tag = "S" Then
            Me.Controls(NewControlSort).ForeColor = ColMidRed
            Me.Controls(NewControlSort).FontWeight = 700       'Bold
            Me.Controls(NewControlSort).BorderWidth = 2
            Me.Controls(NewControlSort).BorderColor = ColMidRed
      End If

      'Setup sort list string using record source ORDER BY clause
      strSortBy = Nz(Mid(Me.RecordSource, InStr(Me.RecordSource, "ORDER BY") + 8), "")
      If Right(strSortBy, 1) = ";" Then strSortBy = Left(strSortBy, Len(strSortBy) - 1)

      Me.LblSortBy.Caption = "Sort Order: " & strSortBy

      Application.Echo True

Exit_Handler:
      Exit Function

Err_Handler:
      If Err = 438 Then Resume Next
      MsgBox "Error " & Err.Number & " in GetOrderBy procedure : " & Err.Description
      Resume Exit_Handler

End Function

'--------------------------------------------------------------

Private Sub GetRecordSource()

On Error GoTo Err_Handler

      strFormName = "[" & Me.Name & "]."
      strWhere = Replace(Me.Filter, strFormName, "")

      If strWhere <> "" Then
            strRecordSource = strSelect & " WHERE " & strWhere & " ORDER BY " & strOrderBy & ";"
            cmdClearFilter.Enabled = True
      Else
            strRecordSource = strSelect & " ORDER BY " & strOrderBy & ";"
      End If

      Me.RecordSource = strRecordSource

Exit_Handler:
      Exit Sub

Err_Handler:
      MsgBox "Error " & Err.Number & " in GetRecordSource procedure : " & Err.Description
      Resume Exit_Handler

End Sub

Click a different button to sort by that column. Click the same button again to reverse the sort order

The up/down filter images are stored in the image gallery:


The screenshot below shows reverse sorting by Forename together with filtering on four columns.


To filter a column, do one of the following:
a)   right click on a control to open the advanced filter menu
b)   double click on a field control to open a multiselect filter menu

Filtered columns are highlighted in GREEN. The filter criteria are also displayed in the form header.

For more information on filtering in continuous forms, see my article: Highlight Filtered Columns

The second form has transparent buttons to imitate the labels used in standard continuous forms. It works in exactly the same way for both sorting and filtering.


The screenshot below shows the form with reverse sorting on the Forename field and filtering on three other fields.


Click the Clear Sort or Clear Filters buttons to restore the original form data.



For another example using this approach, see my Student Target Grade Explorer app in this article: Multiple Group and Filter




More Continuous Form Examples

The following articles provide further examples of functionality added to continuous forms:

•   Highlight Current Record
•   Highlight Selected Control or Column
•   Highlight Filtered Columns
•   Add Multiselect Filter   (2 pages)
•   Paint Me Transparent       (Selectively Hide Controls)
•   Hide duplicate values in a column   (as for reports)
•   Hide & Restore Selected Columns   (2 pages)
•   Freeze Columns   (3 pages)
•   Move and Resize Columns   (2 pages)



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 10 Mar 2024

Return to Example Databases Page

Return to Top