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. 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
    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
  4. MikeC

    Van T. Dinh Guest

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

    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
  5. MikeC


    Oct 29, 2018
    Likes Received:
    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
    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.