Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using A Command Button To Sort (Access 2002)

    I would like to use a command button to sort the records in my form; If I click on the Button I want it to sort ascending; if it's already in ascending order I like it sort in descending order. I know that I can right click and get the ascending / descending Sort Order. But I would like to just have my users use command buttons. I like to use as much VB code as possible.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using A Command Button To Sort (Access 2002)

    You don't mention on which field you want to sort the form. The following will sort on the field that had the focus before the user clicked the button.

    Place a command button named cmdSort on the form. Set its caption to Sort Ascending.
    Put the following declaration at the top of the form module, below Option Compare Database and Option Explicit, but before all Subs and Functions:

    Private fSortAscending As Boolean

    Put the following code in the On Click event procedure of the command button:

    Private Sub cmdSort_Click()
    On Error GoTo ErrHandler
    Screen.PreviousControl.SetFocus
    fAscending = Not fAscending
    If fAscending = True Then
    RunCommand acCmdSortAscending
    Me.cmdSort.Caption = "Sort Descending"
    Else
    RunCommand acCmdSortDescending
    Me.cmdSort.Caption = "Sort Ascending"
    End If
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    You can comment out the MsgBox instruction if you like.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using A Command Button To Sort (Access 2002)

    Instead of using a command button, try using a label with its properties set to look like a command button. The label has a Click event but it can't get the focus, so you won't have to set the focus back to anything. I customarily use the labels in the form header and put the field name in the label's tag property. Then clicking on the label allows immediate sorting by the field it represents.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using A Command Button To Sort (Access 2002)

    Thank You; the truth is that I stated command button; however it is a label in the form header, how does it know that you want to sort on that field?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using A Command Button To Sort (Access 2002)

    Charlotte already posted a reply; atached is another example (using command buttons, but it can be made to use labels too.)
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using A Command Button To Sort (Access 2002)

    Yes she did; I just wanted know how the label know what field you want to sort. In addition; I reviewed your method on the zipped database that you sent. I must admit that your second method seems to be a bit easier; and I will most likely use this method. Once again; thanks for your help.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using A Command Button To Sort (Access 2002)

    In answer to your question, the Tag property of a control holds a string. If you store the name of the field you want to sort by, then all you need do is something like this:

    Me.OrderBy = Me!LabelWhatever.Tag
    '** insert the code to set ascending or descending
    Me.OrderByOn = True
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •