Records sort in form

[peter2012]

I've got a from that I want to sort and keep sorted by the ID of the table concerned.

So I have an on open sort command that does that (ORDER BY ID DESC).

But when I add a new record the form goes back to the original ORDER BY ID ASC, except that the new record goes on the beginning, so the new order is 455, 1, 2, 3, etc.

The resulting entry in the underlying table is sorted in the right place.

Makes no difference to the weird sorting referred to above how I sort the underlying table and query.

Does anyone know how to keep the form ID order sorted ORDER BY DESC, following the initial sort on opening the form. Tried a few things but so far none have worked.

Peter

 

[Simon_MT]

Try: A Query with (ORDER BY ID DESC) behind the Form. i.e. Always sort on ID Desc.

Simon

 

[missinglinq]

I've got a from that I want to sort and keep sorted by the ID of the table concerned.

So I have an on open sort command that does that (ORDER BY ID DESC).

If the first statement is true, why are sorting using code in the OnOpen event instead of simply setting the Form up to sort when it loads? Then you'd simply have to do a Requery after adding a Record.

Or are Simon and I missing something?

Linq ;0)>

 

[peter2012]

Simon and Ling, thanks

Yes in the form's property sheet in the data tag (in A 2010) I have an "order by on load" of ID DESC. And the query behind the form (and the table that the query is based on are both sorted as ID DESC, though this does not make a difference to the problem first identified above.

Not sure how or where to do a requery on exit. Is there a place on the property sheet for that? I have been using some VBA but an having difficulty using it to perform SQL queries.

Ling I would be grateful if you could explain how to go about doing what you suggest.

Peter

 

[Pat Hartman]

The suggestion was to add an order by clause to the form's RecordSource query.

When records are added to a table, they are physically added at the end and that's the view you see in a form. It makes no difference where the key would logically sort. The new record row is always the last visible row regardless of the form's sort order. As you add new records, the new record pointer just moves down to a new row. The order of the original rows doesn't change and most people don't want it to. Most find it disconcerting to have the rows keep resorting and the record pointer moving back to the first visible row. But if you want to try it so see how it works, add a requery to the form's AfterInsert event.
Me.Requery
I don't think you'll like it.

When a database is compacted, Access reorders all the rows in every table to put them physically in primary key sequence. You don't need to worry about physical sequence. All you need is an order by clause in every query to control how the records sort in a form or report.