Others

Fix Microsoft Access Form Moving to Wrong Record After Sorting Long Text

John Wilson Oct 9, 2026 869 views

Question details

The user needs to fix an issue where a Microsoft Access form jumps to an incorrect record after adding a new entry when the form is sorted by a Long Text or Memo field.

Fix Microsoft Access Form Moving to Wrong Record After Sorting Long Text

Product: Microsoft Access
Device & OS: not provided
Scenario: Adding a new record to a form that is currently sorted by a Long Text or Memo field.
Observed behavior: The form automatically moves to a different record instead of remaining on the newly created one, and the Before Update and After Update VBA events appear to trigger for different records.

Before you start

Verify the exact data type of the field you are sorting by in your table design view, and temporarily disable any active VBA macros to confirm if the issue stems from custom code or native Access behavior.

Solution 1Recommended

Sort by the First 255 Characters Using an Expression

Since sorting directly by a Memo or Long Text field is unsupported and unreliable in Access, sorting by a truncated version of the field usually resolves the jumping record issue.

Microsoft Access has historical limitations with sorting and grouping large text fields. Truncating the field to a standard Short Text length (255 characters) within the query stabilizes form navigation and prevents update anomalies.

Open Form in Design View

Open your Microsoft Access database, navigate to the form experiencing the issue, right-click its tab, and select Design View.

Access the Query Builder

Open the Property Sheet (F4), go to the Data tab, locate the 'Record Source' property, and click the ellipsis (...) button to open the Query Builder.

Add Truncation Expression

In a blank column on the query grid, add a new calculated field using the expression: SortField: Left([YourLongTextFieldName], 255).

Apply the New Sort

Set the Sort row of this new calculated column to Ascending or Descending, and ensure you remove any sorting applied directly to the original Long Text field. Save and test the form.

Sort by the First 255 Characters Using an Expression

Event handler alignment: Implementing this workaround typically fixes the issue where Before Update and After Update events fire for different records.

Solution 2

Debug Form Events and Bookmark Handling

If the form still moves unexpectedly after fixing the sort order, VBA code in the Before Update or After Update events may be interfering with the record pointer.

Free Microsoft Office alternative

Looking for a Lightweight and Reliable Office Suite?

While WPS Office does not include a database management tool like Access, it provides a powerful, free, and highly compatible alternative for your daily document, spreadsheet, and presentation needs. Enjoy seamless productivity without the heavy resource usage.

Fully compatible with Microsoft Office formats (.docx, .xlsx, .pptx).Lightweight installation with blazing fast performance and low system requirements.Familiar user interface requiring zero learning curve for Office users.Advanced PDF toolkit integrated directly into the suite.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the record counter show 6 of 6 when I just added a new record?

The record counter reflects the current sort order of the form's recordset, not the chronological order in which the records were created. If sorted alphabetically, the new record will be positioned based on its text value, which might happen to be the last position.

Why are Before Update and After Update events firing for different records in Access?

This is a known anomaly in Microsoft Access when sorting by a Memo or Long Text field. The underlying recordset shifts unexpectedly upon updating, causing the event handlers to lose track of the original record pointer. Removing the sort on the Long Text field resolves this bug.

Can I sort by a Memo or Long Text field directly in Microsoft Access?

Directly sorting by a Memo or Long Text field is generally unsupported and unreliable in Access. It is highly recommended to sort by Short Text fields or use the Left() function to extract and sort by the first 255 characters.

Office Quick Solutions