Sort Property Example (MDB)

The following example uses the Sort property to set the sort order of a dynaset-type Recordset object based on an Orders table. The records in the rstSorted recordset variable will be ordered alphabetically by ship country.

Note that you first create a dynaset-type Recordset object and set its Sort property, then open a second dynaset-type Recordset object based on the first. Setting the Sort property of the first Recordset object doesn't actually affect the order of the records that it contains, so you need to create a second Recordset object in order to see the effects of the sort.

Sub SortByCountry()
    Dim dbs As Database
    Dim rstOrders As Recordset, rstSorted As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create dynaset-type Recordset object.
    Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
    ' Set sort order.
    rstOrders.Sort = "ShipCountry"
    ' Create second dynaset-type Recordset object.
    Set rstSorted = rstOrders.OpenRecordset()
    ' Perform operations with recordset.
    .
    .
    .
    rstOrders.Close
    rstSorted.Close
    Set dbs = Nothing
End Sub

Tip   In most situations, it's more efficient to create the second Recordset object with the desired conditions in one step. When you know the data you want to select, it's generally more efficient to create a Recordset object with an SQL statement. The next example shows how you can create just one Recordset object and obtain the same results as in the preceding example:

Sub CreateRecordsetWithSQL()
    Dim dbs As Database, rst As Recordset
    Dim strSelect As String

    Set dbs = CurrentDb
    strSelect = "SELECT * FROM Orders ORDER BY ShipCountry;"
    Set rst = dbs.OpenRecordset(strSelect)
    rst.MoveLast
    MsgBox "Recordset contains " & rst.RecordCount & " records."
    rst.Close
End Sub