OrderBy not working

[Euler]

Hello,

This should be a simple problem.
I have a continuous form that I want to be able to open sorted by its primary key (Autonumber, long) ascending.


I have the underlying table sorted by the primary key ascending.
I have the "order by" property in the form's property sheet set to the string name of the primary key and the "order by on load" property set to "Yes".
For good measure, I also have the VBA instructions Me.OrderBy = "PK" and Me.OrderByOn = True in the form's OnLoad event.
Nevertheless, the form opens sorted by the primary key in descending order.

Any ideas would be appreciated.

[JoeM]

Here is an article which shows how to sort records in various Access objects (including Forms): https://support.office.com/en-sg/art...f-0fd8015008a8

Quite frankly, I find the easiest way to do this is to sort the records in a Query, and then use the Query as the Record Source of the Form.
Typically, I hardly ever use a Table as the Record Source of Forms or Reports. I use Queries (based on the Table), which allows me to easily change things, add things (especially calculated fields), add criteria, etc.

It is pretty easy to do. Just create a Query based on your Table, adding all the fields, sorting, criteria you want. Then save the query.
Then open your Form, go to Properties, go to the Data tab, and change the Record Source property to use your query instead of your table.

Note that if you tried adding some sort options directly on your Form, you may need to remove them so that they don't try to "overwrite" your sort options in your query.

[Euler]

Yes, I've tried setting the recordsource to a query based on the table (i.e:

SELECT SET_Order_Items_Services.* FROM SET_Order_Items_Services ORDER BY SET_Order_Items_Services.PK;

but it still orders by the primary key in descending order.

[JoeM]

but it still orders by the primary key in descending order.

Note that if you tried adding some sort options directly on your Form, you may need to remove them so that they don't try to "overwrite" your sort options in your query.

You need to remove it from all places, Form Properties, VBA, etc.

To prove that this works, try creating a new simple Form based on your sorted Query (can let the Autoform do it, if you like), and see what order it is sorted in.

[Euler]

I took your suggestion and removed the field name from the form's Order By property and also removed my VBA code from the form's OnLoad event. No difference. Using the query with the Order By statement, it still sorts by the primary key descending.

I don't know if this matters but this form is used as a subform but the main form doesn't tell it how to sort anywhere.

[JoeM]

It sounds to me like you have something still affecting the sorting on your existing form that you just haven't located.

What happens if you try my suggestion to create that Form again from scratch?
Does it then sort in the right order?

[Euler]

I created a brand-new form with the same result. Sorry.

[JoeM]

Can you post the SQL code of your query?
And let us know which field you are trying to sort by?

Note that you have the ability to upload your database to this site. That might go a much longer way in diagnosing the issue, as then we will be able to see all the components. If you do that, just be sure to remove any sensitive/private information.

Also, I am unable to download files from my current location, but could do so tonight.

[Euler]

The form's recordsource SQL statement is:

SELECT SET_Order_Items_Services.* FROM SET_Order_Items_Services ORDER BY SET_Order_Items_Services.PK;

Where PK is the autonumbered primary key.

[JoeM]

Don't think I have any more suggestions without being able to see it.

[Euler]

I'll try to put something together and upload it. Meanwhile I found an interesting posting:

https://social.msdn.microsoft.com/Fo...orum=accessdev

I don't use a datasheet but there seems to be some inherent problem somewhere.

[orange]

Euler,

What happens when you run your query in the query window SQL view?

SELECT SET_Order_Items_Services.* FROM SET_Order_Items_Services ORDER BY SET_Order_Items_Services.PK;

You could have some corruption somwhere. You could try a compact and repair. No harm in my view.

[Euler]

The query works properly when run as a stand-alone query. It sorts ascendingly. It just seems to be a problem when opening the subform that it doesn't behave the way it should.

[JoeM]

It sounds like you may be experiencing the issue mentioned in that link you posted.