• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Toggle Sort Button in Access 2003 Form

Need code to toggle between sorting ascending and descending...the code below is only sorting ascending
Private Sub Command72_Click()
    
    If Me.OrderBy = "4-Week Total Cost" Then
        Me.OrderBy = "4-Week Total Cost desc"
    Else
        Me.OrderBy = "4-Week Total Cost"
    End If
    Me.OrderByOn = True
 
End Sub

Open in new window

0
jbradford777
Asked:
jbradford777
1 Solution
 
oleggoldCommented:
try
Private Sub Command72_Click()
    
    If Me.OrderBy = "4-Week Total Cost" Then
        Me.OrderBy = "4-Week Total Cost desc"
    Else
        Me.OrderBy = "4-Week Total Cost asc"
    End If
    Me.OrderByOn = True
 
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
jbradford777,

1. change the caption of your command72 button to ASC
save the changes

2. try this codes

Private Sub Command72_Click()
    if me.Command72.caption="ASC" then
         Me.OrderBy = "4-Week Total Cost"
         me.Command72.caption="DESC"
         else
          Me.OrderBy = "4-Week Total Cost desc"
         me.Command72.caption="ASC"
  end if
      Me.OrderByOn = True
 
End Sub
0
 
jbradford777Author Commented:
AWESOME!!!  THANK YOU SO MUCH!
0
 
LambertHeenanCommented:
The reason you are having trouble is that your field name has spaces in it. So when you first open the form OrderBy is a blank string "".

Then when this line executes the first time you click the button,

Me.OrderBy = "4-Week Total Cost"

the form gets sorted ascending, but Access sets OrderBy to "[4-Week Total Cost]", automatically inserting the square brackets. As a result the expression

Me.OrderBy = "4-Week Total Cost"

is always false, so your code never sorts descending. Change your code to this...

    If Me.OrderBy = "[4-Week Total Cost]" Then
        Me.OrderBy = "[4-Week Total Cost] desc"
    Else
        Me.OrderBy = "[4-Week Total Cost]"
    End If
    Me.OrderByOn = True


and it will work.

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now