Why is my form opening in alphabetical order?

[Access_Novice]

In the attached database, I am working with the table called "Parents" and the form called "Parents."



The table is sorted by parentID (an AutoNumber which is formatted as '000') so I presume the first record would be parentID 001 which corresponds with Dolores Burke.

However, when I open the 'Parent' form, record 1 of 57 is not Dolores Burke. Instead it is Donnell Arnold with a parent ID of 025. Why is this the case? At one point I did sort by lastname but I have since removed that sort. The table is now sorted by the primary key which is ParentID.



Also, how can I get the form to open to a particular record?

[rpeare]

set the sort order of the form to whatever field(s) you want. If you don't define it you're going to get inconsistent results, and if you haven't changed the sort from the way you originally had it it's going to continue sorting them in the 'old' order until you change it.

In your case you have, in the ORDER BY property of your form

[Parent].[LastName] change that to [Parent].[ParentID] and you're set to go

[June7]

Don't rely on autonumber ID to coincide with the record number on form. Autonumber sequence can have gaps (aborted data entry) and autonumber doesn't even guarantee it will be a positive value, although I've never seen negative generated.

Opening to a particular record and still have all other records available for navigating will require code.

[Access_Novice]

set the sort order of the form to whatever field(s) you want. If you don't define it you're going to get inconsistent results, and if you haven't changed the sort from the way you originally had it it's going to continue sorting them in the 'old' order until you change it.

In your case you have, in the ORDER BY property of your form

[Parent].[LastName] change that to [Parent].[ParentID] and you're set to go

That was it. Thank you very much.

[Access_Novice]

Don't rely on autonumber ID to coincide with the record number on form. Autonumber sequence can have gaps (aborted data entry) and autonumber doesn't even guarantee it will be a positive value, although I've never seen negative generated.

Opening to a particular record and still have all other records available for navigating will require code.

Good point about the AutoNumber. Thank you sir.

[June7]

Recent thread on topic of opening form to specific record https://www.accessforums.net/program...ord-40003.html

[ssanfu]

The table is sorted by parentID

Just wanted to point out that tables can not be "sorted" or have an inherent sort order. A table is a "bit bucket". Records are usually displayed in the order entered.... but that is not always the case. Don't rely on a table to have any type of order.

My rule of thumb is to use a query as the record source for a form. That way you can always set/control the sort order.