Passing a sort variable to a form

[Digsys]

I open a form based on a query and do not have any sort criteria specified in the query as these will change and are specified on the form. I use the openform method, but do not know how to inform the underlying query as to the sort field to use. This must be a common problem and I would appreciate some help, Thanks

 

[kewo99]

You might try to use the OpenArgs form property.
Pass the field(s) using the openargs argument of the openform action and then set the Orderby property of the form or append the fields to an Order By clause of the form's recordsource property.

Ken

 

[Digsys]

Thanks kew099. I am not sure of the procedure. The form has a subform based on a query and it displays the results of the query as a datasheet. I tested the possibility of using the ORDERBY property of the SUBform by simply entering the fieldname that I want to sort on in the ORDERBY property of the subform, it does not seem to do anything. I could try appending the new sort field to the ORDERBY clause of the underlying Query, but I do not know how to do this. Can you please provide some pointers, Thanks

 

[ReluctantDataGuy]

This sounds wierd, but when you have a field in the "Order By" property as you've done, you have to set the form's "OrderByOn" property also.

Oddly, you can go through all the properties of the form and you will not find any such thing as "OrderByOn".

The only way to set it is in code.

So you have to have at least 2 lines of code:

Me.OrderBy = "[MyFieldName]
Me.OrderByOn = True

For more info, look in help for OrderByOn.

Hope that helps.

 

[Digsys]

Hey reluctantdataguy your advice is great. I have the ORDERBYON set in the open event for the subform and if I now also set ORDERBY = "Name" it all works and the sort is by name. I Have an input form that a user can enter the word Name into a text box and the last step is to assign this value to the ORDERBY in the open event. I cannot seem to get this right as I have tried different combinations of quotes and ampersands.
eg ORDERBY = "[Forms]![InputForm]![Sortby]
where Sortby is the name of the textbox on the Input Form
Can you help on this please?.

 

[ReluctantDataGuy]

Hmmm.. tried to send a response but it seems to have gone into limbo. Here's the quick and dirty:

Create a combo box called cboSortBy
Set its RowSourceType to Field List
Set its RowSource to the same table or query the form is based on.

In cboSortBy's OnChange event, put:

Me.OrderBy = cboSortBy
Me.OrderByOn = True

Remove everything else related to sorting that you've done before.

Give it a try. It worked great for me.