Results 1 to 3 of 3
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Sorting Records In Ascending Or Descending Order By Clicking Label Header (Continuous Forms)

    I apologize if this has been asked already - I'm trying to figure out how to sort records in a Continuous Form in Ascending *Or* Descending Order.
    Below is an example of my existing code. One of the fields is "Date Submitted", and the header label for that column is "lblDate".
    Thus, I added the below code in the "On Click" property for that label control. This obviously sorts the records in ascending order - oldest to newest.
    Is there a way to also initiate the records being sorted in descending order, or would I need to do something along the lines of creating a second label and then have the code hide/unhide the labels accordingly? e.g.: clicking on lblDate1 would sort in ASC order; hide that label after it has been clicked, and make lblDate2 visible instead. In the "On Click" event for lblDate2, use Me.OrderBy = "[Date Submitted] DESC" and then hide lblDate2 / make lblDate1 visible.

    Code:
    Private Sub lblDate_Click()
    
    
    Me.OrderBy = "[Date Submitted] ASC"
    Me.OrderByOn = True
    
    
    End Sub


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Untested air code:

    Code:
    If Me.lblDate.Caption = "ASC"
      Me.OrderBy = "[Date Submitted] = "ASC" 
      Me.OrderByOn = True
      Me.lblDate.Caption = "DESC"
    Else
      Me.OrderBy = "[Date Submitted] DESC"
      Me.OrderByOn = True
      Me.lblDate.Caption = "ASC"
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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".
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-19-2015, 03:51 PM
  2. sorting table field in ascending order
    By picyx in forum Access
    Replies: 7
    Last Post: 11-18-2014, 02:24 PM
  3. Replies: 7
    Last Post: 03-01-2013, 01:02 PM
  4. How to order columns in ascending order?
    By darkingthereturn in forum Access
    Replies: 5
    Last Post: 06-18-2012, 05:24 AM
  5. Make TEXTBOXES sort by DESCENDING order
    By taimysho0 in forum Programming
    Replies: 1
    Last Post: 12-05-2011, 04:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums