How do I specify a pre-defined index in Order By?

[mmaule]

How do I specify a pre-defined index in Order By?


When creating a table, I created a multi-field index. Now, in a form using that table, I want to use that index but am having trouble specifing it in the Order By property field.

[ItsMe]

Sounds like you have a Look Up Field in a table. These are generally frowned upon because of their limitations. One limitation is changing the order of the Value List. The order is determined by the order items are added to the list.

[mmaule]

When I created the table, I created a foreign Key field (of the parrent table) and a primary key field. I selected Index and created a multi-field index using those fields, with the name SPU_ndx. Now I want to use the index in an associated form.
I know I can specify [foreign Key field] & [primary key field] in the forms' Order By property, but is there a way to indicate that I want to use the index SPU_ndx, in the forms' Order By property?

[ItsMe]

It has been a while since I used it and I am having trouble finding an example. I believe you can do it something like this

Me.OrderBy "SPU_ndx; [foreign Key field]; [primary key field]"
or maybe
Me.OrderBy "SPU_ndx, [foreign Key field], [primary key field]"

also, you should be able to use the Desc keyword if you do not wish to sort Asc
Me.OrderBy "SPU_ndx, [foreign Key field], [primary key field] DESC"


Add the fields to your statement in the order of sort priority.

I usually avoid properties that are available to the user via intrinsic tools, eg Filter, Order By, etc.
I would not want a user to override my code by clicking the toolbar somewhere.

I would try to find a solution by sorting via the Form's RecordSource. Maybe add some SQL to a query object and use that SQL string as the Form's RecordSource.

[mmaule]

Thanks. I guess I'll just have to specify the fields to order by, given my lack of experience and time limitation.
Thanks again.