This cat has many skins to offer, so to speak Want a way to be able to use this with any form? First you should have
Option Compare Database
Option Explicit
at the top of EVERY module otherwise you get what you deserve
under that in your form module, add Dim strSort As String
set the value of strSort on form load (could probably just as well be DESC initially). Or you could probably just use the form property sheet to set the opening sort but you still need the module level strSort variable :
Code:
Private Sub Form_Load()
strSort = "ASC"
Me.OrderByOn = True
End Sub
If you put the following in a standard module, you should be able to call it from any form in the db (see click event code for a call example):
Code:
Sub SwapSort(strFrmName As String, strFld As String, strOrder As String)
With Forms(strFrmName)
.OrderBy = strFld & " " & strOrder
.OrderByOn = True
End With
End Sub
In your label (or whatever) click event the names of the form and the field you want to sort on are passed to the above sub as well as the sort order:
Code:
If strSort = "ASC" Then
SwapSort Me.Name, "myField", "DESC"
strSort = "DESC"
Else
SwapSort Me.Name, "myField", "ASC"
strSort = "ASC"
End If
The click event also flips the value of the sort direction on each pass. What I haven't done is provided for when you're tinkering with the form and interrupt code or do anything that causes the loss of strSort value. It will be OK on a second pass or you could add a test - if it's "" make it "ASC".