Sorting Records In Subform

Discussion in 'Microsoft Access VBA Modules' started by MikeC, May 2, 2004.

  1. MikeC

    MikeC Guest

    I need to sort records in a subform using VBA and have
    discovered (at least I think) that there is no way to set
    the OrderBy/OrderByOn properties on a subform. I know
    that I can change the underlying query, but I don't want
    to do that.

    As a solution, I'm planning to set focus to a control on
    the subform and then use RunCommand to sort the records.
    Does anyone know of a better way to do this or is this a
    common method?

    Thanks for any useful ideas you may have.
     
    MikeC, May 2, 2004
    #1
    1. Advertisements

  2. MikeC

    Van T. Dinh Guest

    Yes, you can. The code should be something like:

    Forms!MainForm!SubformControl.Form.OrderBy = "YourOrrderingField(s)"
    Forms!MainForm!SubformControl.Form.OrderByOn = True

    If you are running the ordering in the context of the MainForm, you can use
    "Me" instead of "Forms!MainForm".

    Note that the name of the Subform*Control* may be different from the name of
    the Form being used as the Subform (more accurate, being used as the
    SourceObject of the SubformControl).
     
    Van T. Dinh, May 2, 2004
    #2
    1. Advertisements

  3. MikeC

    MikeC Guest

    Thanks Van.

    Believe it or not, that was the first thing I tried, but I
    kept getting error messages indicating that the object
    wasn't open, etc.

    After reading your confirmation that it *does* work, I
    regained confidence and altered my approach.

    This time I first *set focus* to the first page of the tab
    control on which the subform is located. I did not
    realize that I needed to set focus first, but I can see
    now that VBA does not expose the OrderBy/OrderByOn
    properties of the subform if it is located on a tab
    control page unless the page already has focus.

    The RunCommand technique also worked, but I *much* prefer
    to control objects by setting their properties.

    Thanks for your help.
     
    MikeC, May 2, 2004
    #3
  4. MikeC

    Van T. Dinh Guest

    I didn't think the TabControl would affect the sorting or the Subform on a
    TabPage.

    The only exception is when I use the TabControl but not the TabPages (so I
    actually use only little tabs at the top) and I dynamically assign the
    SourceObject of a single SubformControl.
     
    Van T. Dinh, May 3, 2004
    #4
  5. MikeC

    BarberSmurf

    Joined:
    Oct 29, 2018
    Likes Received:
    0
    Location:
    Vietnam
    I can get access to and change the properties named "Orderby" and "OrderByOn" of the subform by using the following statements (please remove the square brackets):

    Me.[name of subform control].Form.OrderByOn = True
    Me.[name of subform control].Form.OrderBy = "Chapter_Number"
    Me.[name of subform control].Requery

    Please note that "Chapter_Number" is NOT the field name. Instead, it is the name of the object (e.g. textbox) in the subform (or it locates inside the subform control).
     
    BarberSmurf, Oct 29, 2018
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.