reading sequence through a recordset

[Leathem]

I am using the following code to read a series of records from a table and use the names included in each record to print a form.

Private Sub Command0_Click()
    Dim rst As DAO.Recordset
    Dim printout As Integer
    printout = 0
    Const MemberQuery As String = "SELECT StudentName FROM Enrollments"
   ' Clear the form
   Forms.DistributeLetters.StudentName = ""
' Get a recordset using the query
    Set rst = CurrentDb.OpenRecordset(MemberQuery, dbOpenSnapshot)
    ' Move through the recordset looking at each record
    With rst
        Do While Not .EOF
            Forms.DistributeLetters.StudentName = ![StudentName]
            ' temporary message to track sequence
            MsgBox (Forms.DistributeLetters.StudentName)
            'DoCmd.OpenReport ("Enrolled Students Letter")
            printout = printout + 1
            If printout Mod 25 = 0 Then MsgBox ("Check Printer Queue")
            .MoveNext
        Loop
    End With
    
    rst.Close
    Set rst = Nothing
exit_emailerror:
    Exit Sub
    
emailerror:
    MsgBox "There was an error in the subroutine"
    Resume exit_emailerror
End Sub

The record in "Enrollments" begins with a StudentID followed by the StudentName and then a number of other fields. The Enrollments table is sorted alphabetically by StudentName. However the reading part of the loop goes through the names in a very odd sequence: it starts with a name that begins with Q, then follows the StudentID sequence for a while, then switches to some non-sequential StudentID and again goes on for a while in StudentID sequence, etc. I don't know where it is getting the StudentID information and the jumping around seems to make no sense, but in any case I'd really like it to go through the list alphabetically.

 

[Uncle Gizmo]

Have you tried adding an "order by" statement to your query?

 

[boblarson]

FYI Leathem:

Tables do not store records in any meaningful way. Think of a big bucket of fish. They all are in the bucket but if someone pulls one out they can all slide into a different position and such. Such is with data in Access tables. There is no order unless you provide order. Now you can't provide order by using the sort feature on a table because you probably weren't aware that when you look at a table you aren't really looking at the table. You are looking at a QUERY (albeit a system query) that DISPLAYS the contents of the table. So, to view or work with data in a specific order you have to apply that order outside of the table - in the query (or report if using a report because they don't follow a query's sort order much so you have to set their sorting and grouping specifically). But a query used as a recordsource for a form, recordset object, or a query itself needs to have the order applied in that query.

 

[Leathem]

Bob and Uncle Gizmo,
Many thanks to both of you for the quick response and accurate diagnosis. Following your advice I inserted "ORDER BY StudentName" in the string, and now it works fine. And Bob, thanks for the FYI on the file structure in tables. I wasn't aware of the lack of order in the way the data were stored. One learns something new each time!

Leathem