Sort using Labels…

On continuous forms Users always want to Sort and while they could do it from the Toolbar (Access 2003 or lower) or the Ribbon (access 2007 or higher), here’s another option…

The two fields we’re going to use are Date Modified and Import Date.  As you can see, I changed the LABEL back color to a pale yellow.  This is the color I use when the *Sorting* option is available for a specific field/column.  I will also change the TEXT color when sorting which, effectively, acts as my trigger.  (I also only apply this sorting option to continuous forms.)

To *activate* the Sorting we’re going to use the On_Click event of the LABEL.  You will notice that while sorting on one field you still need to control the other field(s)/column(s) you’re srting on just in case your User jumps to sort another field/column without *releasing* the initial field/column.  We start off with the fields unsorted (Figure 1)…

Figure 1

Insert the code on the LABELs On_Click event…

Private Sub lblDateModified_Click()

Me.txtDateModified.SetFocus

If Me.lblDateModified.ForeColor = RGB(0, 0, 0) Then
   DoCmd.RunCommand acCmdSortDescending
   Me.lblDateModified.ForeColor = RGB(102, 0, 204)
   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified " & Chr(118)
   Me.lblImportDate.ForeColor = RGB(0, 0, 0)
   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date"
Else
   DoCmd.RunCommand acCmdSortAscending
   Me.lblDateModified.ForeColor = RGB(0, 0, 0)
   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified " & Chr(94)
   Me.lblImportDate.ForeColor = RGB(0, 0, 0)
   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date"
End If

End Sub

The first Click Sorts ascending (Figure 2), leaving the TEXT black…

Figure 2

The second Click sorts descending (Figure 3), turning the TEXT purple…

Figure 3

…and because each Click is a *trigger*, so to speak, they will go back and forth (ascending and descending) with each Click with the Import Date remaining UNsorted.  To return the Date Imported to it’s UNsorted state, double-click the LABEL.  But before you do that, don’t forget to insert the code on the LABELs DoubleClick event…

Figure 1

Private Sub lblDateModified_DblClick(Cancel As Integer)

   Me.OrderByOn = False
   Me.OrderBy = vbNullString
   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified"
   Me.lblDateModified.ForeColor = RGB(0, 0, 0)
   Me.txtDateModified.SetFocus

End Sub

And here’s the code for the lblImportDate’s (Figure 4) Click and Double_Click events…

Figure 4

Private Sub lblImportDate_Click()

   Me.txtImportDate.SetFocus

If Me.lblImportDate.ForeColor = RGB(0, 0, 0) Then
   DoCmd.RunCommand acCmdSortDescending
   Me.lblImportDate.ForeColor = RGB(102, 0, 204)
   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date " & Chr(118)
   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified"
   Me.lblDateModified.ForeColor = RGB(0, 0, 0)
Else
   DoCmd.RunCommand acCmdSortAscending
   Me.lblImportDate.ForeColor = RGB(0, 0, 0)
   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date " & Chr(94)
   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified"
   Me.lblDateModified.ForeColor = RGB(0, 0, 0)
End If

End Sub

Private Sub lblImportDate_DblClick(Cancel As Integer)

   Me.OrderByOn = False
   Me.OrderBy = vbNullString
   Me.lblImportDate.ForeColor = RGB(0, 0, 0)
   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date"
   Me.txtImportDate.SetFocus

End Sub

 1,663 total views,  2 views today