Access desktop disables edits in a form when the form property, Order by on load, is set to a value

[WCStarks]

Is this a bug in Access? If the Order by on Load property to a form is set to a value (field(s)), Access disables editing the form data. Remove the Order by on load value, and Access enables editing the form data. What gives? This surely, cannot be the intended functionality?

[ssanfu]

If the Order by on Load property to a form is set to a value (field(s)),

The "Order by on Load" property can only be "Yes" or "No".

In A2010, I created a form based on a table, set the "Order by" property to "Amount" and was still able to change the amounts.


I have never used the FORM "Order by" and "Order by on Load" properties
I prefer to have a query for the form record source. The query has the sort order set. Much better and easier, IMO....

[davegri]

I couldn't replicate the problem in AC2016 either.

[WCStarks]

I meant Order By and Order By on Load as separate properties.

After I made the original post, I went to the associated query and did the order by in there instead of in the form and of course, it works. That doesn't take away from the fact that Access is doing a crazy thing, by disabling edits when one does the order by in the form. What is the point in even having that form property, if it is going to so severely impact functionality of the form. This issue should be escalated to some who can deal with it.

[WCStarks]

I have Access 2016, the latest update, and I can't avoid it.

[HiTechCoach]

Does the issue happen when you order by a single field?

Are the field(s) you are ordering by included the record source/query's output?

It would be helpful if you posted what you have in the Order By property that causes the issue.

Also, are you using VBA code at runtime (during execution) to set the Order By properties? If yes, it would be helpful to see the code you are using.

[Micron]

I seem to recall reading that post 2007, if the field being sorted on was not updatable, the form became not updatable when sorted on that field. IIRC, a row source using Group By or Union (can't recall about DISTINCT, DISTINCT ROW or value lists) would cause the behaviour if the form was a data sheet. Not always when something like this changes is it a bug. Sometimes it's a fix. For this one, I don't profess to know which it is, but would have to figure it has something to do with the possibility that the fixed list can't be synced with the results in some cases.

Edit: we all seem to be assuming there are no Trusted Location issues - i.e. the only thing changed was the Access version and not the pc or it's file system or the location of the db. A non trusted location will also prevent the sort.

[WCStarks]

Does the issue happen when you order by a single field?
Are the field(s) you are ordering by included the record source/query's output?
It would be helpful if you posted what you have in the Order By property that causes the issue.
Also, are you using VBA code at run time (during execution) to set the Order By properties? If yes, it would be helpful to see the code you are using.

The sub-form is in sheet view mode. See attachment. The name field is from a different table. As a calculated field it cannot be be edited. The name field concatenates the given name and surname from the member table. The remainder of the sub-form is from the transaction table. There is a simple query to gather the data from the two tables.

I originally did an order by in the form on the surname and given name, and discovered it no longer allowed any field to be edited. Thinking it may be because I ordered by a calculated field, I tried sorting on other fields from the transaction table and found the same results. It didn't seem to matter what field is used to order by in the sub-form, the result is the same.

The transaction table, itself, has a default order by Begin Date. I have another similar transaction sub-form, which shows all transactions for a member instead of for an event. It has no calculated fields. All fields are from the source table. As I was responding to this post, I, just now, tried setting the form order by property to Activity in this form, and found I could still edit it. So it appears that the issue may only manifest itself, if the form includes a calculated field, whether the order by is on that field or not.

I am not using VBA code. Using the query to set up the order by is a better practice anyway, given Access' odd behavior.

[WCStarks]

There are no trusted location issues. See my reply to Hitechcoach for more details.

[WCStarks]

The issue involves having a calculated field in the form. See my reply to Hitechcoach for more details.