Sort Ascending and Descending

[johannaellamay]

Hi! I have a problem with my code. I'm trying to sort and filter a continuous form. Sort ascending and filters works perfectly fine. But my descending button doesn't work. I basically have a combo box which contains a field list. Then two buttons (asc. and desc.), then a text box for filter, a button to filter, and another button to reset filter. Here's my code:

Private Sub cmdAscending_Click()
    If IsNull(Me.cboField) Then
        MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
    Else
        Me.OrderBy = Me.cboField
        Me.OrderByOn = True
    End If
End Sub
Private Sub cmdDescending_Click()
   If IsNull(Me.cboField) Then
      MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
 Else
    Me.OrderBy = Me.cboField
   Me.OrderByOn = False
End If
End Sub
Private Sub cmdFilter_Click()
    If IsNull(Me.cboField) Then
        MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
    Else
        If IsNull(Me.txtFilter) Then
            MsgBox "Please type a text to filter.", vbOKOnly, "No text to filter."
        Else
            Me.Filter = "[" & [cboField] & "] Like '" & [txtFilter] & "*'"
            Me.FilterOn = True
        End If
    End If
End Sub

What am I doing wrong?

 

[JHB]

And where do you tell to sort "Asc" and "Desc" in your code?
I would expect something like below, (not tested):

  Me.OrderBy = Me.cboField & " Desc"
  Me.OrderByOn = False

 

[johannaellamay]

And where do you tell to sort "Asc" and "Desc" in your code?
I would expect something like below, (not tested):

  Me.OrderBy = Me.cboField & " Desc"
  Me.OrderByOn = False

That's exactly my question. How do I tell Access to sort ascending and descending? From what I've made so far, I thought when I used Me.OrderByOn = True, it will be sorting Ascending so I was looking for a way to sort Descending. Or did I get it wrong?

Do you have any suggestions for the codes for both ascending and descending granting that I have two command buttons, cmdAsc and cmdDesc that when clicked, should be able to sort Ascending and Descending, respectively?

Please help me.

 

[Gasman]

I believe JHB has shown you how. You append Desc to the order by string

See https://msdn.microsoft.com/en-us/library/office/ff844761.aspx for syntax

Google is your friend https://www.google.co.uk/search?q=a...rome.0.57j0l2.5593j0&sourceid=chrome&ie=UTF-8

http://www.pcreview.co.uk/threads/vba-sort-code-descending.3978649/

 

[jdraw]

I also think John has provided the how.


See this for an example
if you need it.
Good luck. Let us know what works.

 

[Gasman]

Also bearing that in mind, I'd be inclined to have just the one button Sort and option button control for Asc & Desc to simplify the code

 

[johannaellamay]

Hi!

Thanks for the links. I finally made it work. But I just experimented. Please let me know if I did something wrong:

Private Sub cmdAscending_Click()
    If IsNull(Me.cboField) Then
        MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
    Else
        Form.OrderBy = "[" & [cboField] & "] ASC"
        Form.OrderByOn = True
    End If
End Sub
Private Sub cmdDescending_Click()
   If IsNull(Me.cboField) Then
      MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
   Else
      Me.OrderBy = "[" & [cboField] & "] DESC"
      Me.OrderByOn = True
    End If
End Sub
Private Sub cmdFilter_Click()
    If IsNull(Me.cboField) Then
        MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
    Else
        If IsNull(Me.txtFilter) Then
            MsgBox "Please type a text to filter.", vbOKOnly, "No text to filter."
        Else
            Me.Filter = "[" & [cboField] & "] Like '" & [txtFilter] & "*'"
            Me.FilterOn = True
        End If
    End If
End Sub

Private Sub cmdRemoveSortFilter_Click()
    Me.Filter = ""
    Me.FilterOn = False
    
    Me.cboField = ""
    Me.OrderByOn = False
    
    DoCmd.RunCommand acCmdRemoveFilterSort
End Sub

 

[JHB]

... Please let me know if I did something wrong:

..
        [B][COLOR=Red]Form[/COLOR][/B].OrderBy = "[" & [cboField] & "] ASC"
        [COLOR=Red][B]Form[/B][/COLOR].OrderByOn = True
...

I would change the Form to Me, as you've in the rest of the code!

 

[johannaellamay]

I would change the Form to Me, as you've in the rest of the code!

Noted! Thank you.