{"id":578,"date":"2013-02-11T02:03:27","date_gmt":"2013-02-11T02:03:27","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=578"},"modified":"2016-07-08T22:43:20","modified_gmt":"2016-07-09T02:43:20","slug":"sort-using-labels","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=578","title":{"rendered":"Sort using Labels…"},"content":{"rendered":"

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

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

To *activate* the Sorting we’re going to use the On_Click event of the LABEL.\u00a0 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.\u00a0 We start off with the fields unsorted (Figure 1)…<\/p>\n

\"Figure

Figure 1<\/p><\/div>\n

Insert the code on the LABELs On_Click event…<\/p>\n

Private Sub lblDateModified_Click<\/strong>()<\/p>\n

\r\nMe.txtDateModified.SetFocus\r\n\r\nIf Me.lblDateModified.ForeColor = RGB(0, 0, 0) Then\r\n   DoCmd.RunCommand acCmdSortDescending\r\n   Me.lblDateModified.ForeColor = RGB(102, 0, 204)\r\n   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified " & Chr(118)\r\n   Me.lblImportDate.ForeColor = RGB(0, 0, 0)\r\n   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date"\r\nElse\r\n   DoCmd.RunCommand acCmdSortAscending\r\n   Me.lblDateModified.ForeColor = RGB(0, 0, 0)\r\n   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified " & Chr(94)\r\n   Me.lblImportDate.ForeColor = RGB(0, 0, 0)\r\n   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date"\r\nEnd If\r\n<\/pre>\n
End Sub<\/p>\n
The first Click Sorts ascending (Figure 2), leaving the TEXT black…<\/p>\n
\"SortAscending\"
Figure 2<\/p><\/div>\n
The second Click sorts descending (Figure 3), turning the TEXT purple…<\/p>\n
\"SortDescending\"
Figure 3<\/p><\/div>\n
 <\/p>\n
…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.\u00a0 To return the Date Imported\u00a0to it’s\u00a0UNsorted state, double-click the LABEL.\u00a0 But before you do that, don’t forget to insert the code on the LABELs DoubleClick event…<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
\"Figure
Figure 1<\/p><\/div>\n
Private Sub lblDateModified_DblClick<\/strong>(Cancel As Integer)<\/p>\n
\r\n   Me.OrderByOn = False\r\n   Me.OrderBy = vbNullString\r\n   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified"\r\n   Me.lblDateModified.ForeColor = RGB(0, 0, 0)\r\n   Me.txtDateModified.SetFocus\r\n<\/pre>\n
End Sub<\/p>\n
And here’s the code for the lblImportDate’s (Figure 4) Click and Double_Click events…<\/p>\n
\"SortAscendingImportDate\"
Figure 4<\/p><\/div>\n
Private Sub lblImportDate_Click<\/strong>()<\/p>\n
\r\n   Me.txtImportDate.SetFocus\r\n\r\nIf Me.lblImportDate.ForeColor = RGB(0, 0, 0) Then\r\n   DoCmd.RunCommand acCmdSortDescending\r\n   Me.lblImportDate.ForeColor = RGB(102, 0, 204)\r\n   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date " & Chr(118)\r\n   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified"\r\n   Me.lblDateModified.ForeColor = RGB(0, 0, 0)\r\nElse\r\n   DoCmd.RunCommand acCmdSortAscending\r\n   Me.lblImportDate.ForeColor = RGB(0, 0, 0)\r\n   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date " & Chr(94)\r\n   Me.lblDateModified.Caption = "Date" & vbCrLf & "Modified"\r\n   Me.lblDateModified.ForeColor = RGB(0, 0, 0)\r\nEnd If\r\n<\/pre>\n
End Sub<\/p>\n
Private Sub lblImportDate_DblClick<\/strong>(Cancel As Integer)<\/p>\n
\r\n   Me.OrderByOn = False\r\n   Me.OrderBy = vbNullString\r\n   Me.lblImportDate.ForeColor = RGB(0, 0, 0)\r\n   Me.lblImportDate.Caption = "Import" & vbCrLf & "Date"\r\n   Me.txtImportDate.SetFocus\r\n<\/pre>\n
End Sub<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n
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 […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,63],"tags":[64,31,21],"class_list":["post-578","post","type-post","status-publish","format-standard","hentry","category-access-tips","category-database-design","tag-access-tips","tag-forms","tag-vba","odd"],"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/578","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=578"}],"version-history":[{"count":42,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/578\/revisions"}],"predecessor-version":[{"id":1272,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/578\/revisions\/1272"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=578"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=578"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=578"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}