Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Visual Basic code to Sort (A2K)

    I want to put a cmd button on my form to allow the user to 'sort ascending' the table on the form field that has the focus.
    The process works great if I use the Records, Sort, Sort Ascending selections from the Menu bar but I can not make it work
    using code. I will appreciate any suggestions. Thank you.

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

    Re: Visual Basic code to Sort (A2K)

    The problem is that when you click the command button, the button gets the focus, so there is no field that has the focus any more,

    As an alternative, you can put a combo box on the form with Row Source Type set to List of Fields and Row Source set to the name of the table or query that acts as record source of the form. In the AfterUpdate event of the combo box, you can set the OrderBy property of the form to the selected field, and the OrderByOn property to True

    If you don't want to use a combo box, you must use a keyboard shortcut or a toolbar button that reads the Control Source of the ActiveControl, and orders the form by that field. You can set the KeyPreview property of the form to True, and handle keystrokes at the form level instead of for each control separately.

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visual Basic code to Sort (A2K)

    Thank you very much your suggestion was very helpful. I had to make some modifications but I did get it to work as I wanted. Again thank you. It is wonderful to have experts that are willing to help us 'not so expert'. Have a wonderful day. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Visual Basic code to Sort (A2K)

    In attachment you find an example using a menu button, which calls a macro (which calls a function) sorting your form on the active control. I created two buttons, for ascending & descending sort order respectively.
    You still need to convert it from Access 97 to 2K but that shouldn't raise any problems (!?). It's the first time I put an attachment on this forum. I hopes it works...
    Greetings,
    Hasse
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Visual Basic code to Sort (A2K)

    In case anyone should be interested in the code (stored in the module), which, I presume, is moreless what HansV suggested:

    'Sort active form ascending using active control
    Public Function ffrmSortAsc()
    Dim frm As Form, ctl As Control
    Set frm = Screen.ActiveForm
    Set ctl = frm.ActiveControl
    frm.OrderBy = ctl.ControlSource
    frm.OrderByOn = True
    End Function

    'Sort active form descending using active control
    Public Function ffrmSortDesc()
    Dim frm As Form, ctl As Control
    Set frm = Screen.ActiveForm
    Set ctl = frm.ActiveControl
    frm.OrderBy = ctl.ControlSource & " DESC"
    frm.OrderByOn = True
    End Function

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Visual Basic code to Sort (A2K)

    Hey, Ifwatson,
    I'm happy I could, for a change, help someone else too :-).
    Please notice that I changed my posts: I cut the code of the second function procedure, which factually wasn't needed at all.
    Just one last question, out of curiosity: why do you want to sort using code instead of the available Access sort buttons?
    Hasse

  7. #7
    New Lounger
    Join Date
    Apr 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visual Basic code to Sort (A2K)

    Hasse,
    The reason for the command button is that my users are NOT very savvy with Access and by putting everything they need to do on the screen it makes them have to think even less. I have been coding Access inhouse applications for about 10 years. Before Access I created applications with dBase. If I can be nosey, are you an application programmer?
    Thank you again.

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

    Re: Visual Basic code to Sort (A2K)

    It's quite common to provide custom methods for users to sort data because the built in menus are not usually available in finished applications. One technique I use is the click event of the label for the column. In that event, I set the OrderBy property of the form to the associated field and then set the OrderBy on. Users don't have to know about anything except that clicking the label will sort by that column. I usually set it up so that the sort toggles. If they click the label again, the sort reverses itself between ascending and descending. Many users have never figured out what the sort buttons are for even if they are visible. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Visual Basic code to Sort (A2K)

    Hey, Ifwatson,
    honestly, 'programmer' might still be a bridge too far in my case. For example, I think I still lack some automatisms skilled IT-people have,... But I'm able to do moreless what I want in VBA. Access is more a hobby to me, but as I'm having the opportunity to amuse myself with it (he he) at my job as well, I-m a bit a professional as well (though I think most of us can say that :-).

    Thanks both for telling why 'doing on your own' what Access also offers, is sometimes unavoidable.

    And by the way, Charlotte, thanks for your tip. Often, many ways lead to Rome but this is a neat suggestion, especially when you use continuous forms.

    Hasse

Posting Permissions

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