Command button sort in subform

[Guest]

I have a form frmManageClients based on a query qryManageClients. I also have
a subform sfrmARLedger based on a query filter_sfrmARLedger_All. I have
linked the form to the subform with Link Child Fields prClient and Link
Master Fields dqClient. I have an option group on the main form to filter
records in the main form and another option group on the main form to filter
records in the subform. All works fine to this point. Then I added command
buttons to the main form to sort records the records displayed in the subform
based on multiple fields using the following code example:

strSQLSort = "prProject, [Invoice Date], [Invoice Number]"
Me!sfrmARLedger.Form.OrderBy = strSQLSort
DoCmd.RunCommand acCmdSave
Me!sfrmARLedger.Requery

The result is that the sort works sometimes and sometimes not at all (it
seems to depend on if I have opened the form and entered an OrderBy property
manually - but I could be mistaken). And if the sort does work, it only works
until I try to scroll through the records in the subform. As soon as I select
the subform, the sort goes back to whatever it was when the main form was
opened.

Any help would be appreciated. Thanks in advance.

 

[Marshall Barton]

I have a form frmManageClients based on a query qryManageClients. I also have
a subform sfrmARLedger based on a query filter_sfrmARLedger_All. I have
linked the form to the subform with Link Child Fields prClient and Link
Master Fields dqClient. I have an option group on the main form to filter
records in the main form and another option group on the main form to filter
records in the subform. All works fine to this point. Then I added command
buttons to the main form to sort records the records displayed in the subform
based on multiple fields using the following code example:

strSQLSort = "prProject, [Invoice Date], [Invoice Number]"
Me!sfrmARLedger.Form.OrderBy = strSQLSort
DoCmd.RunCommand acCmdSave
Me!sfrmARLedger.Requery

Why are you saving the form's design???

You should not need to Requery the subform control.

You definitely need to make sure that the subform's
FilterByOn property is set to True.

I think this is all you need:

strSQLSort = "prProject, [Invoice Date], [Invoice Number]"
Me!sfrmARLedger.Form.OrderBy = strSQLSort
Me!sfrmARLedger.Form.OrderByOn = True

 

[Guest]

Marshall,
Thank you for your help and time.

I am making the assumption that the Allow Filters property set to Yes in the
subform design is the same as where you refer to the FilterByOn property set
to True? If not, should this be set with code in the subform's OnOpen event?

I used 'DoCmd.RunCommand acCmdSave' hoping that would overwrite my default
OrderBy.Value that was in the form design originally...just grasping at
straws. The Requery command was again just grasping at straws.

I pasted the second and third lines of code you recommended in place of mine
leaving only the first line 'strSQLSort = "prProject, [Invoice Date],
[Invoice Number]"'. I should tell you there are 3 such command buttons and
naturally I have assigned 3 different strSQLSort strings. The one I have
tested and am still getting the same results is one where I sort by 'DaysDQ
DESC, [Invoice Number]' - I want the 'Days Delinquent' to be shown in
descending order, followed by Invoice Number in ascending order. So I looked
closely to see how the sort order was changing, because it was definately
changing, and I found that when I sort by 'DaysDQ DESC' and the results fit
in the subform window, it works fine. But if there are more records than will
fit in the subform window, it sorts first by Project Number (ascending) and
then by DaysDQ (descending). I know of nothing that would cause the Project
Number to be the primary sort.
Any ideas?

I have a form frmManageClients based on a query qryManageClients. I also have
a subform sfrmARLedger based on a query filter_sfrmARLedger_All. I have
linked the form to the subform with Link Child Fields prClient and Link
Master Fields dqClient. I have an option group on the main form to filter
records in the main form and another option group on the main form to filter
records in the subform. All works fine to this point. Then I added command
buttons to the main form to sort records the records displayed in the subform
based on multiple fields using the following code example:

strSQLSort = "prProject, [Invoice Date], [Invoice Number]"
Me!sfrmARLedger.Form.OrderBy = strSQLSort
DoCmd.RunCommand acCmdSave
Me!sfrmARLedger.Requery

Why are you saving the form's design???

You should not need to Requery the subform control.

You definitely need to make sure that the subform's
FilterByOn property is set to True.

I think this is all you need:

strSQLSort = "prProject, [Invoice Date], [Invoice Number]"
Me!sfrmARLedger.Form.OrderBy = strSQLSort
Me!sfrmARLedger.Form.OrderByOn = True

 

[Marshall Barton]

No, the AllowFilters property is not related to the
OrderByOn (or the FilterOn) property. If the three lines I
posted earlier don't take care of it, then I give up.

I hate to say this, but I have had so much trouble with the
Filter property doing strange things (especially when a
subform was involved) that I stopped using it years ago.
The alternative is to set the form's record source query.
--
Marsh
MVP [MS Access]

Marshall,
Thank you for your help and time.

I am making the assumption that the Allow Filters property set to Yes in the
subform design is the same as where you refer to the FilterByOn property set
to True? If not, should this be set with code in the subform's OnOpen event?

I used 'DoCmd.RunCommand acCmdSave' hoping that would overwrite my default
OrderBy.Value that was in the form design originally...just grasping at
straws. The Requery command was again just grasping at straws.

I pasted the second and third lines of code you recommended in place of mine
leaving only the first line 'strSQLSort = "prProject, [Invoice Date],
[Invoice Number]"'. I should tell you there are 3 such command buttons and
naturally I have assigned 3 different strSQLSort strings. The one I have
tested and am still getting the same results is one where I sort by 'DaysDQ
DESC, [Invoice Number]' - I want the 'Days Delinquent' to be shown in
descending order, followed by Invoice Number in ascending order. So I looked
closely to see how the sort order was changing, because it was definately
changing, and I found that when I sort by 'DaysDQ DESC' and the results fit
in the subform window, it works fine. But if there are more records than will
fit in the subform window, it sorts first by Project Number (ascending) and
then by DaysDQ (descending). I know of nothing that would cause the Project
Number to be the primary sort.
Any ideas?

I have a form frmManageClients based on a query qryManageClients. I also have
a subform sfrmARLedger based on a query filter_sfrmARLedger_All. I have
linked the form to the subform with Link Child Fields prClient and Link
Master Fields dqClient. I have an option group on the main form to filter
records in the main form and another option group on the main form to filter
records in the subform. All works fine to this point. Then I added command
buttons to the main form to sort records the records displayed in the subform
based on multiple fields using the following code example:

strSQLSort = "prProject, [Invoice Date], [Invoice Number]"
Me!sfrmARLedger.Form.OrderBy = strSQLSort
DoCmd.RunCommand acCmdSave
Me!sfrmARLedger.Requery

Why are you saving the form's design???

You should not need to Requery the subform control.

You definitely need to make sure that the subform's
FilterByOn property is set to True.

I think this is all you need:

strSQLSort = "prProject, [Invoice Date], [Invoice Number]"
Me!sfrmARLedger.Form.OrderBy = strSQLSort
Me!sfrmARLedger.Form.OrderByOn = True