The records not sorted by the record ID field in ascending order in a continuous form after adding new records (3 Viewers)

[wendywu]

I have a continuous form that the record scource of the form is a table sorted by the ID field in ascending order, If I enter new record in the entry form, the records are not sorted by ID field of a record in ascending order as you can see:

So all records should be in the order of entry :

What should i do?

Thanks in advance

 

[The_Doc_Man]

I have a question. When this apparent sorting error occurs, did you keep that form open? Normally, when you make a change to the underlying recordset, you do a requery, which should re-establish the order of records in the query driving this form. Is there a way for you to know in the code of the form that something has been updated, and thus give you reason to do a .Refresh or .Requery at that time?

There is also a feature found in the Files >> Options menu (I think the next step is >> Current Database) that relates to a time interval after which open queries automatically refresh themselves, but there are ways to prevent that.

 

[DHookom]

What do you mean by “that is by default sorted by the ID field”? Are you using an ORDER BY somewhere that sorts by ID? I would never assume records will display in any reliable order without some setting or SQL order.

 

[wendywu]

What do you mean by “that is by default sorted by the ID field”? Are you using an ORDER BY somewhere that sorts by ID? I would never assume records will display in any reliable order without some setting or SQL order.

The recordset source of the form is bound to a table .I design the form as a data entry form for the table. I have set the default value property of the ID field in the table as ascending.

 

[Edgar_]

Your form has an OrderBy property where you can specify the field or fields to order by.
If your form is using a query string as source, you can add the OrderBy clause too.

 

[Gasman]

Normally when you add records, they are added to the end of the table?
You appear to be adding them at the top?
Are you doing a requery/refresh after adding records?

What happens if you close and reopen the form? Is that the second image?

 

[wendywu]

I have a question. When this apparent sorting error occurs, did you keep that form open? Normally, when you make a change to the underlying recordset, you do a requery, which should re-establish the order of records in the query driving this form. Is there a way for you to know in the code of the form that something has been updated, and thus give you reason to do a .Refresh or .Requery at that time?

There is also a feature found in the Files >> Options menu (I think the next step is >> Current Database) that relates to a time interval after which open queries automatically refresh themselves, but there are ways to prevent that.

I have a question. When this apparent sorting error occurs, did you keep that form open? Normally, when you make a change to the underlying recordset, you do a requery, which should re-establish the order of records in the query driving this form. Is there a way for you to know in the code of the form that something has been updated, and thus give you reason to do a .Refresh or .Requery at that time?

There is also a feature found in the Files >> Options menu (I think the next step is >> Current Database) that relates to a time interval after which open queries automatically refresh themselves, but there are ways to prevent that.

I design this form as a data entry form. The record source of this form is a table.
Everything was normal when entering the data from the first to the sixth entry.
When the 7th record is added, the newly added record will be displayed in front of the 1st record in this form, and the same applies to the following records.
Now I have deleted all the original data in the data table,
Now the form displays normally.

Now I have deleted all the records on the data table. The ID of the first record is 25, because I have deleted all the previous records.
Now when I enter a new record in the form, the new records are displayed in ascending order according to the ID field as you can see.

Thank you very much!

 

[The_Doc_Man]

That sounds like you merely put off the problem. From your description, however, I don't understand the behavior.

From your description in post #7, the first six records entered normally but the seventh record went in the wrong place. Did you enter those seven records in a single session? Or did you close the form after six records and then re-open it to add the seventh record? Having the record "jump around" like that would imply that your sort specificationi is not being honored. Which means either that you have no effective sort order OR you are sorting on the wrong field.

 

[Ken Sheridan]

The following is an example of code which requeries a form after a row is updated. As the Requery method reloads the form's recordset, the code then navigates back to the newly updated row.

Private Sub Form_AfterUpdate()

    Dim lngID As Long
    
    ' assign current row's primary key value to a variable
    lngID = Me.TransactionID
    Me.Requery
    
    ' navigate back to newly inserted or updated row
    ' by synchronizing form's bookmark with that of
    ' row in form's RecordsetClone which matches the 
    ' value of the variable
    With Me.RecordsetClone
        .FindFirst "TransactionID = " & lngID
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
            
    Me.TransactionDescription.Requery
    
End Sub

 

[GPGeorge]

I agree. Whatever you did here doesn't address the real, underlying problem.

Records in tables have no inherent sort order. To impose a sort order in a form -- such as the one in your screenshot -- you have to designate one yourself when you bind the form to a recordsource.

If you do not assign a sort order, Access will use a default of its choosing. The default is normally the values of the AutoNumbers assigned as a Primary Key. And that is where your problem probably originates.

You see records with Primary Keys 25 through 31 because that is the sequence in which you entered them in one session. It is largely coincidental that this is the same order you want to display them. In other words, the default sort order is the same as the order of those primary keys.

That is not guaranteed to be the case in the future. In fact, that is probably why you saw the out of order sequence originally.

You need to select a value for the sort order applied to the form's recordsource which does guarantee you'll see the records in the sequence you want. Perhaps a date field would be a good choice.

And, if you need to resort the records after entering new ones, Ken's procedure will handle that for you.

 

[Pat Hartman]

Let me emphasize what several others have said. Tables and queries are UNORDERED sets. The ONLY way to ensure a specific sort order is to use a query with a sort . If you bind your form to a table, the data is NOT in any specific order. If you previously sorted the data while the form was open, then Access may fool you and remember the prior manual sort command. Without being able to read Chinese, I can't tell if the data is ordered by some field other than the ID. But if it is, that is your answer. You previously sorted the form's data and answered yes to the save question when you closed the form so your manual sort command was retained.

 

[wendywu]

Your form has an OrderBy property where you can specify the field or fields to order by.
If your form is using a query string as source, you can add the OrderBy clause too.

Yes.Thank you very much.

 

[wendywu]

Normally when you add records, they are added to the end of the table?
You appear to be adding them at the top?
Are you doing a requery/refresh after adding records?

View attachment 120550

What happens if you close and reopen the form? Is that the second image?
View attachment 120552

Yes.Thank you very much.