Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How to change the sort orders (by description, date, qty,...etc.) on the fly in the form with macros only?
    The sorting order change is triggered by events such as double-click a column or a field.
    Thanks.
    Armstrong

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To change the sort order, you need to set two properties of the form:
    • The OrderBy property is the name of the field you want to sort on (or a comma-separated list of field names if you want to sort on more than one field). Add DESC after the field name if you want to sort descending (default is ascending).
    • The OrderByOn property must be set to True to apply the sort order.
    Examples:

    Me.OrderBy = "LastName"
    Me.OrderByOn = True

    or

    Me.OrderBy = "OrderDate DESC, OrderAmount"
    Me.OrderByOn = True

  3. #3
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    HansV,
    Thanks a lot for your help.
    Armstrong

  4. #4
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    HansV,
    How can I implement it with macro action--SetProperty?
    Thanks.
    Armstrong

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='armsys' post='779494' date='12-Jun-2009 21:05']HansV,
    How can I implement it with macro action--SetProperty?
    Thanks.
    Armstrong[/quote]
    Why do you insist on using macros??

  6. #6
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Patt,
    It seems impossible with macros.
    Armstrong

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can probably use a macro for this, but VBA code is much easier to create and maintain.

    The new SetProperty action can only be used for a limited number of properties - see SetProperty Macro Action.

    You can use the SetValue action, but only if the database is trusted (see SetValue Macro Action)

    Action: SetValue
    Item: [Forms]![NameOfTheForm].[OrderBy]
    Expression: "NameOfTheField"

    Action: SetValue
    Item: [Forms]![NameOfTheForm].[OrderByOn]
    Expression: True

    Replace NameOfTheForm and NameOfTheField with the appropriate names.

  8. #8
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The switch of sorting orders can be done with SetValue action marco.

    Armstrong

  9. #9
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779514' date='12-Jun-2009 19:13']You can probably use a macro for this, but VBA code is much easier to create and maintain.

    The new SetProperty action can only be used for a limited number of properties - see SetProperty Macro Action.

    You can use the SetValue action, but only if the database is trusted (see SetValue Macro Action)

    Action: SetValue
    Item: [Forms]![NameOfTheForm].[OrderBy]
    Expression: "NameOfTheField"

    Action: SetValue
    Item: [Forms]![NameOfTheForm].[OrderByOn]
    Expression: True

    Replace NameOfTheForm and NameOfTheField with the appropriate names.[/quote]
    Hi HansV,
    Probably we're replying almost at the same time. Before my reply, I didn't find your post.
    I discovered SetValue action macro thru trial and error. And I cherished the learning experience.
    Your post is the most thorough on the subject. For instance, most books won't mention the limitation of SetProperty.
    So far, I like coding macros, especially the SetValue action macro.

    Would you be very kind enough to show me the actual VBA code for the similar function--say, sorting by ProductDesc?
    Thanks for your help.

    Armstrong

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='armsys' post='779608' date='13-Jun-2009 13:02']Hi HansV,
    Probably we're replying almost at the same time. Before my reply, I didn't find your post.
    I discovered SetValue action macro thru trial and error. And I cherished the learning experience.
    Your post is the most thorough on the subject. For instance, most books won't mention the limitation of SetProperty.
    So far, I like coding macros, especially the SetValue action macro.

    Would you be very kind enough to show me the actual VBA code for the similar function--say, sorting by ProductDesc?
    Thanks for your help.

    Armstrong[/quote]
    What you can do is to convert macros into VBA code.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='armsys' post='779608' date='13-Jun-2009 04:02']Would you be very kind enough to show me the actual VBA code for the similar function--say, sorting by ProductDesc?[/quote]
    See post #2 (the first reply) in this topic.

  12. #12
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779616' date='13-Jun-2009 15:14']See post #2 (the first reply) in this topic.[/quote]

    After using your VBA code, all my macros corrupt. That's why I ask for your help.
    Anyway I'll figure out myself. Thanks.
    Armstrong
    Attached Images Attached Images

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think running VBA code can corrupt macros...

  14. #14
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779646' date='13-Jun-2009 20:39']I don't think running VBA code can corrupt macros...[/quote]
    HansV,
    Thanks for your feedback. Don't worry about it. Anyway I solved the scary loss of macros by trial and error. Still I don't have a cue how I solved it. The problem is caused by my ignorance in VBA. Once I master the VBA, I'll post the code here.
    Armstrong

  15. #15
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What does me. stand for?

    Me.OrderBy = "LastName"
    Me.OrderByOn = True

    Armstrong

Page 1 of 2 12 LastLast

Posting Permissions

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