Sorting Records In Subform

[MikeC]

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.

 

[Van T. Dinh]

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).

 

[MikeC]

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.

 

[Van T. Dinh]

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.

 

[BarberSmurf]

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.

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).