Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sort records in subform alpabetically on exit (Acc

    How can I sort records alphabetically in a subform? What I would like is every time I put in a new product, the suborm would sort alpabetically as soon as I exit that record. Currently my subform will sort like this only after getting out of the whole order. I tried Requery but that obviously wasn't what I wanted.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort records in subform alpabetically on exit

    Hi again

    Is the sub form running from a query where the records are sorted by product?

    If so you could use this piece of code to attach to the subform:

    Private Sub sbfOrder_Details_Exit(Cancel As Integer)
    Me.Refresh
    End Sub


    When you exit the subform and focus on the main form, it automatically sorts itself
    Jerry

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

    Re: Sort records in subform alpabetically on exit (Acc

    Make sure that the record source of the subform is a query that is sorted the way you want.
    Requery the subform in the After Insert event of the subform:

    Private Sub Form_AfterInsert()
    Me.Requery
    End Sub

    But keep in mind that this will take away the focus from the record you just inserted.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sort records in subform alpabetically on exit

    Thanks, Jezza!
    That's getting closer to what I want. I haven't tried it yet but I will. Yes, it is running from a query sorted alphabetically by product.

    I'd like it to sort as soon as I get out of the record in the subform, before the focus is on the main form.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sort records in subform alpabetically on exit

    I tried that code HansV, and it does what I want mostly. Like you said, it takes the focus away from the current record. I know almost nothing about code. Is there more code that could be included in that (AfterInsert) that would move the focus to the next record instead of sending it back to the first record?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort records in subform alpabetically on exit

    OK then as an addition to Hans answer, attach this to the subform in subform design view

    Private Sub Form_AfterInsert()
    Me.Requery
    Me.Refresh
    DoCmd.GoToRecord , , acNewRec
    End Sub

    When you click out of the new record it will automatically insert the cursor to the new record instead of losing focus.
    Jerry

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

    Re: Sort records in subform alpabetically on exit

    Try this:

    Private Sub Form_AfterInsert()
    Me.Requery
    RunCommand acCmdRecordsGoToNew
    End Sub

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sort records in subform alpabetically on exit

    That's exactly what I need! I tried both Jezza's and HansV's codes and they both have the same end result. Can you explain the difference between "RunCommand" and "DoCmd"?

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

    Re: Sort records in subform alpabetically on exit

    There is no fundamental difference.

    DoCmd lets you include macro commands in VBA code, and RunCommand lets you execute menu options in VBA code. There is some overlap between the two, such as the instructions to move to other records.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort records in subform alpabetically on exit

    Weldon

    Nice link here that explains it: www.blueclaw-db.com
    Jerry

  11. #11
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sort records in subform alpabetically on exit

    Thanks. If you get tired of my questions, just don't answer them, I'm learning! I put that same code in "AfterUpdate" of the Subform properties, then tried it in the "AfterUpdate" of the [ProductName] field and it did the same thing. Why does it need to be in the subform "After Insert"?

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

    Re: Sort records in subform alpabetically on exit

    After Insert only fires when you have created a new record.

    After Update of the (sub)form fires when you have modified an existing record or created a new record. This event is preferable above After Insert if the user may modify the product name in already existing records.

    After Update of the ProductName field fires when you have modified the value of ProductName. If your subform has other controls apart from ProductName, I wouldn't use this event, for it would move the focus to a new record as soon as the user selects a product, making it difficult to enter a value in other fields.

    PS You can ask as many questions as you like. No doubt others will benefit from them and from the replies too!

  13. #13
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sort records in subform alpabetically on exit

    Appreciate that info!

    Merci beaucoup!

Posting Permissions

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