Sort Property Example

This example demonstrates the Sort property by changing its value and creating a new Recordset. The SortOutput function is required for this procedure to run.

Sub SortX()

   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset
   Dim rstSortEmployees As Recordset

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("Employees", _
      dbOpenDynaset)

   With rstEmployees
      SortOutput "Original Recordset:", rstEmployees
      .Sort = "LastName, FirstName"
      ' Print report showing Sort property and record order.
      SortOutput _
         "Recordset after changing Sort property:", _
         rstEmployees
      ' Open new Recordset from current one.
      Set rstSortEmployees = .OpenRecordset
      ' Print report showing Sort property and record order.
      SortOutput "New Recordset:", rstSortEmployees
      rstSortEmployees.Close
      .Close
   End With

   dbsNorthwind.Close

End Sub

Function SortOutput(strTemp As String, _
   rstTemp As Recordset)

   With rstTemp
      Debug.Print strTemp
      Debug.Print "  Sort = " & _
         IIf(.Sort <> "", .Sort, "[Empty]")
      .MoveFirst

      ' Enumerate Recordset.
      Do While Not .EOF
         Debug.Print "    " & !LastName & _
            ", " & !FirstName
         .MoveNext
      Loop

   End With

End Function

Note When you know the data you want to select, it's usually more efficient to create a Recordset with an SQL statement. This example shows how you can create just one Recordset and obtain the same results as in the preceding example.

Sub SortX2()

   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   ' Open a Recordset from an SQL statement that specifies a 
   ' sort order.
   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("SELECT * " & _
      "FROM Employees ORDER BY LastName, FirstName", _
      dbOpenDynaset)

   dbsNorthwind.Close

End Sub