CODEWITHSUNDEEP
sortingms-accessvbams-access-2013
Ponzaro
Ponzaro

Reputation: 149

Toggle sort order with a command button

I'm using 

DoCmd.SetOrderBy

for sorting a form by a specific field. But right now it sorts ascending or descending if I use DESC keyword. How can I do a check to see what sorting is active and do the opposite? If that field is ordered ascending and I click the button, the order become descending and vice versa.

Upvotes: 1

Views: 4832

Answers (2)

HansUp
HansUp

Reputation: 97101

As @Sam suggested, check the value of the Me.OrderBy property to decide whether you want ascending or descending for the new sort order.

The following code sample assumes you want an ascending sort if there is no current sort (i.e. Me.OrderBy is an empty string).

Otherwise check whether Me.OrderBy is Like "* DESC". Note, if the current sort order is ascending, don't assume ASC will be present in Me.OrderBy. And don't assume the column name piece of Me.OrderBy will be present as [FORM].[COLUMN]. 

Dim strOrderBy As String
Dim strDirection As String

strOrderBy = Me.OrderBy
If Len(strOrderBy) = 0 Then
    strDirection = "ASC"
Else
    If strOrderBy Like "* DESC" Then
        strDirection = "ASC"
    Else
        strDirection = "DESC"
    End If ' Like 
End If ' Len(strOrderBy)
DoCmd.SetOrderBy "[YourColumnName] " & strDirection

Upvotes: 0

Sam
Sam

Reputation: 948

You can get the current sort order by doing in the form code

debug.print me.orderby

It will come out something like

[FORM].[COLUMN] Desc

So then do an if statement

if me.orderby = [FORM].[COLUMN] Desc then 
    docmd.setorderby "[COLUMN] ASC"
else
    docmd.setorderby "[COLUMN] Desc"
end if

Try that out. Changing the FORM and COLUMN to match yours of course

Upvotes: 2

Related Questions