Sorting Records In Subform

  • Thread starter Thread starter MikeC
  • Start date Start date
M

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.
 
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).
 
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.
 
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.
 
MikeC said:
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.
Click to expand...

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