Code to sort table

[sroot]

I have a database that shows how many minutes pass between an employees scans works well... the problem is that they can start multiple ones at the same time. So i need the table to sort by the start and stop times so it can accurately measure the gap between scan times. Any thoughts on what code i can use for this? Here is the code it uses to get the gap times.This isn't a database i made its one i got stuck fixing so any help would be great. Thanks!

Function TimeBetween()
Dim Previous As Date
Dim Current As Date
Dim vCount As Integer
Dim vPrevious, vCurrent As String


    DoCmd.OpenQuery "qryTranHistSort"
    Set db = CurrentDb
    Set vrecset = db.OpenRecordset("tblTranHistSorted")
    vCount = 0
    With vrecset
    If (Not .BOF Or Not .EOF) Then
        .MoveFirst
        Do
            vCount = vCount + 1
            Form_frm_Main.statusbox = "Processing Record # " & vCount
            Form_frm_Main.Repaint
            vPrevious = vrecset("StopTime")
            Previous = CDate(vPrevious)
            .MoveNext
            If (Not .EOF) Then
            vCurrent = vrecset("StartTime")
            Current = CDate(vCurrent)
            .MovePrevious
            .Edit
            ![Gap] = DateDiff("n", Previous, Current)
            .Update
            .MoveNext
            End If
        Loop Until .EOF
    End If
    End With
    db.Close
End Function

 

[GinaWhipp]

Hmm, stop... use a query, that's what they are for...

 

[DavidAtWork]

The only comment I could make is that even though you start the process by running a query ... DoCmd.OpenQuery "qryTranHistSort", you don't state the details of how this query works but unless this query creates a new set of ID's, it doesn't guarantee that when you open the recordset ...
Set vrecset = db.OpenRecordset("tblTranHistSorted")
that "tblTranHistSorted" will be sorted how you expect. Access will open tables, usually sorted by the PK
A better method would be to run the "qryTranHistSort" as before, then define a SQL string as your record source and set the sort order
i.e.

strSQL = "SELECT tblTranHistSorted.* FROM tblTranHistSorted " _
& "ORDER BY tblTranHistSorted.StartTime"

The use

Set vrecset = db.OpenRecordset(strSQL)

David