Change sort order on a sub-form in VBA

Posted on 2008-11-06
Last Modified: 2013-11-25
I have a form that users copy/paste directly into.  The source data comes from another application that spits out in Excel.  The user then highligyhts the area needed and copies this directly into the sub-form (for various reasons, cannot upload).  So, now the data is in the sub-form, but I need a way for the user to be able to sort via a command button by Branch Number either ascendig or descending. Since there is no query or an open event, I cannot use these so must resort to a button.  I got as far as the OrderBy, but that does not work.  I simply want the data on the sub-form sorted either direction when the user hits the sort button.
Private Sub cmdBranchSort_Click()


End Sub

Open in new window

Question by:ssmith94015
    LVL 75

    Accepted Solution

    Private Sub cmdBranchSort_Click()

        Me.sfmAMLItems.Form.OrderBy = "SomeFieldName"
        Me.sfmAMLItems.Form.OrderByOn = True

    End Sub
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)

    Me.sfmAMLItems.Form.OrderBy = "SomeFieldName DESC"

    You either need two buttons (Asc or Desc) ... or ... retain the previous state and toggle it.  I would suggest two buttons.


    Author Comment

    DatabaseMX works but now that the data is in ascending, would I set the OrderByOn = False?  Think I will go try that and see what happens.

    Author Comment

    Works, used the toggle as I am cramped for space.  As it toggles, the caption changes so the user knows which direction he is going.

    Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now