How to sort a subform table via VBA?

[Michael R]

Hello.

I have a subform which source is a table. The table, when displayed, gets sorted by an order which is not the order I set for that table in it's design view. Is there any way to get that table displayed by an order of my preference?

Thanks.

[czarbjones]

Create Query instead of basing it on the Table and then put the sort in the Query.

Hope that helps,
Cz.

[PEB]

U can change the recordsource property of the subform using VBA

IThe reference of your subform depends where is placed your code! Where do you want to place it? In subform, in the main form or in a module?

[Michael R]

Create Query instead of basing it on the Table and then put the sort in the Query.

Thanks for the reply, I can't do that, because I need the subform to be able to open child table.

U can change the recordsource property of the subform using VBA

IThe reference of your subform depends where is placed your code! Where do you want to place it? In subform, in the main form or in a module?

I want to place the code in the form, as i'm not understand the other possibilities you've mentioned. I just need the subform to be sorted when I open the form, and the subform is a control in a form.

What would you recomend me to do?

Thanks.

[PEB]

As u describe it seems to me that you don't need to do different sorting from the initial one?

In this case i think u don't need to do any code! Simply create your query on which is based your subform and assign under the respective columns on which you want to sort the respective sort order. Once created your query go to the properties in your subform and assign this query as row source of your subform!

If u don't use a query as row source for your subform, this is no problem to get the respective table in a query and assign the sorting order to the wanted columns! Then assign the created query to Row source of your subform!

If you need to change the sorting during the work of your form in this case you need a code and this code will change just this row source property assigning the new query values!

[Michael R]

As u describe it seems to me that you don't need to do different sorting from the initial one?

In this case i think u don't need to do any code! Simply create your query on which is based your subform and assign under the respective columns on which you want to sort the respective sort order. Once created your query go to the properties in your subform and assign this query as row source of your subform!

If u don't use a query as row source for your subform, this is no problem to get the respective table in a query and assign the sorting order to the wanted columns! Then assign the created query to Row source of your subform!

If you need to change the sorting during the work of your form in this case you need a code and this code will change just this row source property assigning the new query values!

The subform automatically gets sorted by the index field of the table it uses, in spite of me changing the table sort order from its design view.

I can't use query for that table, because I need the subform table to be able to open child tables.

[PEB]

The subform automatically gets sorted by the index field of the table it uses, in spite of me changing the table sort order from its design view.

With query it will be solved... Changes in design view think can't help you!

I can't use query for that table, because I need the subform table to be able to open child tables.

I don't see where is the problem here? The query will have a behaviour just like a table if you do SELECT * FROM mytable ORDER BY A, B;

What is this child tables?

[NeoPa]

There is an Order By property in forms.
The order of the underlying dataset won't matter.

[PEB]

There is an Order By property in forms.
The order of the underlying dataset won't matter.

Yeap this is also possibility

Cheers Ade u've the fastest thousnd my frin :)