ACC2000: How Microsoft Access Saves the Sort Order of a Form Depends on the Version

In versions of Microsoft Access later than 2.0, the sort order that you apply to a field in a form is saved when you save the form or datasheet, and is reapplied automatically when you reopen the form or base a new form or report on that form. This behavior is different from that of Microsoft Access 2.0, wherein the specified sort order is not reapplied when you reopen the form.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. In Microsoft Access versions later than 2.0, the OrderBy property of a form is updated with the control name when you save the form. If you want to change this functionality, you must change the OrderBy property in the Open event of the form. To do so, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

NOTE: The OrderBy property setting for the form is not saved if you open the form in Design view and then switch to Form view to change the settings. You must open the form in Form view and then make the necessary changes.

1. Start Microsoft Access and open the sample database Northwinds.mdb or the sample project NorthwindCS.adp.
2. Open the Employees form, and then note the order in which the records appear.
3. Ensure that the First Name field has the focus, and then click the Sort Ascending tool on the Form View toolbar.
4. Close the form, reopen it, and note that the records are still in ascending order by first name.
5. Open the form in Design view, and then in the property sheet for the form, click the Event tab.
6. Create the following event procedure for the form's On Open property:

   Private Sub Form_Open(Cancel As Integer)
   
       Me.OrderBy = ""
   
   End Sub
   					

7. Close the module. Save and close the form.
8. Reopen the form, and then change the sort order for one of the fields by clicking the Sort Ascending or Sort Descending button on the Form View toolbar. Close the form.
9. Reopen the form and note that the sort order change that you made before you closed the form has not been reapplied.

For more information about sorting data in forms, click Microsoft Access Help on the Help menu, type sort records in a table, query, or form in the Office Assistant or the Answer Wizard, and then click Search to view the topic.