none
Subform in Datasheet mode ignores Orderby setting RRS feed

  • Question

  • AC2010: I set the subform's ORDERBY property in the calling form's CURRENT event handler.
    Sort order is wrong.

    Am I missing something ?

    Only by clicking on the subform's Datasheet column does the ordering get changed.

    There appears to be no way of getting the ActiveDatasheet property of a subform.
    re: Me.fsub.Form.ActiveDatasheet.Orderby

    ......not recognized.

    Wednesday, April 11, 2012 1:44 AM

All replies

  • Try to do the following:

    Me.frmYourSubformName.Form.OrderBy = "[YourFieldNameHere]"
    Me.frmYourSubformName.Form.OrderByOn = True
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Wednesday, April 11, 2012 2:24 AM
  • Tried that, didn't work in the Current event handler of parent form.

    I wonder where it should be placed ? What "fires" after Current ?

    Render ?

    Wednesday, April 11, 2012 12:25 PM
  • It worked for me though, so I wonder what we do differently?

    You may try the OnOpen or OnLoad event of your Form, but it will only be sorted one time, while in the Current Event it will fire every time when scrolling through records for example.


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Wednesday, April 11, 2012 2:24 PM
  • And the difference is: I am using a SPLIT FORM view for the parent form.

    Bug# 413214 that will likely never get fixed. Sorry to vent.

    Split forms are very powerful, but there are so many "gotchas".

    Wednesday, April 11, 2012 8:24 PM
  • You don't need to specify the Datasheet with SplitForm, try this:

    Me.OrderBy = "[YourFieldNameHere]"
    Me.OrderByOn = True

    That should work.

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Wednesday, April 11, 2012 11:52 PM
  • Tried using VBA, tried setting via subform ORDERBY property.....
    NOTHING WORKS.

    I wonder if I change the recordsource of the subform to a query that's sorted...would that work ?

    Nope, that doesn't do anything either.
    Only by clicking on the subform's datasheet and manually resorting it does it work.

    bug city....there is no way to address a subform's datasheet....
    Thursday, April 12, 2012 7:04 PM
  • I just want to simulate the exact situation you are trying it, so I can either confirm or try to come up with something to help.

    So you have a Main Form, then a Subform as a Split Form?

    Or Just one Form as SplitForm?


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Thursday, April 12, 2012 7:17 PM
  • Main form as split form....subform as datasheet in the detail section of main form.

    It's a super-bug because sometimes the records in the subform appear hi to low !!!

    And it gets better: now, under certain circumstances, I'm getting the dreaded "Object Invalid or No longer set" under certain conditions when I click on the datasheet portion of the main form to select a record.

    • Edited by Syswizard Thursday, April 12, 2012 11:27 PM
    Thursday, April 12, 2012 11:25 PM
  • I am not sure what you did and how you did it, but I did the following.

    Placed below code in the On Load event of the Main form (which is a split form);

    Private Sub Form_Load()
    
    Me.frmSubProd.Form.OrderBy = "[Batch]"
    Me.frmSubProd.Form.OrderByOn = True
    
    End Sub

    Then the results are as aspected, see below screenhot (wiped out the data, as it is confidential), the left datasheet of SplitForm, right the single Form and the Subform below it:


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Friday, April 13, 2012 12:37 AM
  • The query for the subform had an ORDER BY in the SQL.

    I removed that....and tried using VBA in the parent form's CURRENT event handler: that did not work.

    Then I set the subform's datasheet sort order for the column manually, then saved the form...

    AND NOW IT WORKS AND IS PERSISTENT !

    So bottomline:

    1) VBA orderby is worthless for subform's in datasheet view

    2) never order the recordsource if you intend to have a changeable order in the datasheet for a subform

    Saturday, April 14, 2012 12:15 AM
  • Syswizard,

    It is curious that it works for others but not for you. This suggests that the real issue has not been identified. Also since it works proper for others and the problem has not been replicated suggest the issue is with the design of the database and shows it is not a bug. I am glad you have a work around to your issue however, if you allow this to go unresolved it can create futher problems for you in the future. If this were my database I would study a while longer to find the cause of the unexpected behavior. I would study the dependencies and relationships to see if those could be the underlying reason for this undesired behavior.

    But in the end your satisfaction is what matters in the forum. Good Luck!


    Chris Ward

    Monday, April 16, 2012 1:19 AM
  • Sorry Chris, that's not the case. I've clearly demonstrated the following:

    1) The subform does order correctly by clicking on the column and right clicking to re-sort. So it's NOT a structural problem or data relationship problem.

    2) The VBA code to do the same as the above does not work....likely because of the below "issue" or limitation...

    3) The Datasheet property of a Subform cannot be referenced via VBA from the parent form.

    Monday, April 16, 2012 1:37 PM
  • I understand your opinion however, you can not escape the fact that it works for others therefore there exists something different in your db that if it were my db would need to be identified at the least and corrected or at least understood. As I said before this is your db and you need to be satisfied, so if you are satisfied with the way it is...I bid you adieu and take my leave.

    Chris Ward

    Monday, April 16, 2012 1:48 PM
  • Well, if what I was doing via VBA was so wrong, then Access should at least throw one of it's infamous 1004 run-time errors.

    It does not.

    Monday, April 16, 2012 3:30 PM