Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sort in a sub form (XP)

    I am hoping this is one of those easy questions (and maybe the answer is just "no")-- I would like data in a subform to display with the most current date at the top of the sub form (datasheet view) I know it can be done in reports; but fail to find the way to do it on a form.

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

    Re: sort in a sub form (XP)

    There are two ways on a form. One is to sort the data source (assuming it's a query or select statement) on the date field descending, which will make the latest record appear at the top. The other is to set the subform's OrderBy property to do the same thing and set OrderByOn = True.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort in a sub form (XP)

    the subform is based on a table --not a query--but will try the other suggestion;' I simply did not know how-- thank you

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort in a sub form (XP)

    was able to set OrderBy property--but do not know how to find OrderByOn--- could you help me?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: sort in a sub form (XP)

    The OrderByOn property is not shown in the properties box. You have to set its value in code.

    So in the On Open event you could put



    me.OrderBy = "NameofDateField Desc"

    Me.OrderByOn = true

    Where you replace NameofDateField with teh actual name.
    Regards
    John



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

    Re: sort in a sub form (XP)

    If you're setting it from the properties window, that's all you need. If you're setting it in code, you can set the OrderBy property to a string specifying the sorting information but then you have to turn it on by setting OrderByOn = True.
    Charlotte

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

    Re: sort in a sub form (XP)

    As an alternative, you can create a query based on the table, and specify the sort order in the query design grid. Then set the record source of the subform to the name of the query instead of the table.
    This solution does not require any code.

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort in a sub form (XP)

    Let me just check-- if I set this in the properties window I dont need the extra bit about being true-- well, I did set it in the properties window and it did not change the order--so, I will try both the other suggestions-- I know that I can change the whole form and have it based on a query instead of a table, but was hoping that one of the other ideas would work-- thank you all for taking so much of your time to help me when I am in a jam-- I have 6 or 8 reference books that I check before I contact you; but sometimes the answer is still not clear until you explain it-- I sure wish there was a class available in my area that was beyond basics.

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

    Re: sort in a sub form (XP)

    Creating a query isn't much work, but if you want to avoid it, you need a bit of code. Just setting the Order By property of a form in design view will not always work. To ensure that the sort order is applied, set OrderByOn to True in the On Open or On Load event of the form; if you do this, you might as well set OrderBy there too. This corresponds to John Hutchison's suggestion:

    Private Sub Form_Load()
    Me.OrderBy = "DateField DESC"
    Me.OrderByOn = True
    End If

    where DateField is the name of the field you want to sort on; DESC indicates that the field is sorted from high to low.

  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort in a sub form (XP)

    just adding the OrderByOn=true to the on open did not help (I already had the sort part in the property window) (but did NOT have this done-)- however it did not work either-- now will try your suggestion-- this looks like a macro-- it should be entered at on open right? I will try that and if still not successful, will just give up and base the subform on a query-- I hate to do that because I am not learning anything--but it is getting way too close to Christmas to work too long on a problem
    Thank you all for your help

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

    Re: sort in a sub form (XP)

    You can create a VBA event procedure for the On Open event or for the On Load event of the form - it doesn't matter which:

    - Open the form in design view.
    - Activate the Event tab of the Properties window.
    - Click in the On Open (or On Load) event.
    - Select [Event Procedure] from the dropdown list.
    - Click the ... to the right of the dropdown arrow.
    - The first and last line of the event procedure will already be created for you.
    - Press the Tab key, then enter Me.OrderBy = "FieldName DESC" where FieldName is the date field.
    - Press the Enter key, then enter Me.OrderByOn = True
    - Close the Visual Basic Editor (Alt+F4 or Alt+Q)
    - Close and save the form.

  12. #12
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort in a sub form (XP)

    Obviously, I am doing something wrong as none of those solutions has worked *sigh* I know this should be easy and it is so frustrating when I cant make it work. Will just give in and do the query--maybe after Christmas, you guys will try again to help me do it right.

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

    Re: sort in a sub form (XP)

    Feel free to come back when you have time.

  14. #14
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort in a sub form (XP)

    just so that you don't think I am a total nut case-- the VBA procedure worked-- have no idea what I did wrong the first time-- but I used it in th e copy of the database that is on home computer and it worked beautifully-- so it is something I have done at the office--will keep trying --thank you

Posting Permissions

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