Sorting continuous forms

Sort continuous forms like a pro - with one click! 💥

Who doesn't know them - the small, fine sorting arrows that can be found everywhere:

In File Explorer, in Excel, on websites, in almost every modern program. Only in Access? Not a chance!

 

👉 What Access can't do, we'll make ourselves! 💪

Take a look here - Access can look this cool:


🔧 What do we need for this?

✅ The main function ToggleSort() controls everything: switch sorting direction, set icons
✅ The wrapper function SortColumn() calls the main function and passes all parameters
✅ A few CommandButtons as column headers
✅ Three icons to display the sorting direction

🤔 Why two functions?

It's quite simple:

Instead of building a separate click event for each column heading (button), we use a central function in the form. When clicking, we only pass the field name to SortColumn() and it takes care of the rest. 


Sequence (debug):

1. click on the heading button
2. SortColumn(“field name”) is called
3. this passes the form, field name, ActivControl to ToggleSort()
4. ToggleSort() then sorts the desired column (ASC/DESC) in the corresponding form and changes the icon accordingly.



🧠 The functions

 🛠️ 1. The main function: ToggleSort() 

Public Sub ToggleSort(frm As Form, columnName As String, sortControl As Control)

Dim currentSort  As String          ' current sort of the form
Dim newSort      As String          ' sorting string to handover to the form
Dim sortDir      As String          ' sorting direction (ASC/DESC)
Dim sortPicture  As String          ' name of the picture (MySysResources) for the sorting commandButton
Dim ctrl         As Control         ' to loop thru the commandbuttons

    Application.Echo False

    ' get current sorting
    currentSort = Nz(frm.OrderBy, "")

    

    ' Standard: Sort in ascending order if no sorting is available
    sortDir = "ASC"
    sortPicture = "sort_ASC"

    ' Check whether it is already sorted
    If InStr(1, currentSort, "[" & columnName & "] DESC", vbTextCompare) > 0 Then
        ' New sort order: ascending
        sortDir = "ASC"
        sortPicture = "sort_ASC"

    ElseIf InStr(1, currentSort, "[" & columnName & "] ASC", vbTextCompare) > 0 Then
    ' New sort order: descending
        sortDir = "DESC"
        sortPicture = "sort_DESC"

    ElseIf InStr(1, currentSort, "[" & columnName & "]", vbTextCompare) > 0 Then
        ' If only column name without direction: assume descending
        sortDir = "DESC"
        sortPicture = "sort_DESC"

    End If

    ' Set new sort order
    newSort = "[" & columnName & "] " & sortDir
    frm.OrderBy = newSort
    frm.OrderByOn = True

    ' Reset icons for the commandButtons (goes thru all controls in the form)
    For Each ctrl In frm.Controls

        If ctrl.ControlType = acCommandButton And Left(ctrl.Name, 8) = "cmdSort_" Then  ' only commandButtons which starts with cmdSort_
            ctrl.Picture = "sort_X"
        End If

    Next ctrl

    ' Set icon for the active control
    sortControl.Picture = sortPicture

    Application.Echo True

End Sub



🧰 2. The wrapper-function: SortColumn()

Private Function SortColumn(sColumn As String)

    Call ToggleSort(Me, sColumn, Me.ActiveControl)

End Function


👀 The icons from where? Never heard of MSysResources?

Then it's time.

Simply show system objects (right-click → Navigation options) - and there it is, the hidden icon goldmine. By the way, you can swap the icons - make your Access design unique!

▶️ sort_X → neutral
🔼 sort_ASC → ascending
🔽 sort_DESC → descending


🔘 Why buttons instead of labels for the headings?

It's quite simple:

✅ Icons 
✅ Mouseover effects 
✅ Change the cursor symbol

Labels can't do all that. And we want a bit of UX magic, don't we?


🚀 Conclusion

With minimal effort, you can finally bring convenient sorting to your Access forms. Implement once - use everywhere. And without ugly VBA clutter. Try it out and let Access shine again! ✨

 

🧙‍♂️ The best thing about it?

✅ Implement only once,
✅ Sort as many columns as you like,
✅ Completely maintainable, 
✅ ...and Access suddenly no longer looks like 2003 😅

 

🔥 And now it's your turn!

Let's take ACCESS to the next level together. Because one thing is clear:

Standard was yesterday. Today, ACCESS is sexy. 🔥

 

💬 Do you have any questions, ideas, improvements or want to show off your version? Feel free to comment!


This small extension not only makes your Access interface more intuitive, but also a whole lot “sexier”.

You can find the detailed description and all notes directly in the code - well documented as usual.



    DOWNLOAD   

Version: 1.0


If you find it helpful and recognize its value, I would appreciate a small donation to reflect the effort and benefit it provides you.
PayPal A little donation