Validation of field based on entry in previous recordset (1 Viewer)

Hello,
I am trying to find the most efficen way to validate the entry in a field based on the previous recordset.

The idea is to log time and activity.

The form as a header form with the date and a subform in datasheet view.
I want to make sure that the subform taes time (for the specific date) only in ascending values.
Since SQL does not consider a recordset order I assured that the udnerlying table has a autonumber field (this will give me the order of recordsets).

Further I want to make sure that the activity entered is lmited based in the previous activity.

For example:

5:45 getting up
5:55 brushing teeth

BUT NOT:
5:45 brushing teeth
5:55 getting up

My though is to have a VBA code that runs on the "before update" property of the time field and compare the the time of the previous recordset if the date is the same.
Though I am concerend about the timet his mgiht take?

For the activity I can only think of a similiar code, but then VBA hardcoding what can come after what.

Any better ideas on this topic?

OK,
I solved a part of the riddle myself.

My idea was that data is entered in sequential time.
The problem is what happens when you miss a line.

Therefore I created VBA code that does the ranking, thereore you can enter times in arbitraily order:

Dim rs As ADODB.Recordset
Dim strSQL As String
Dim CurrentOEEHD As Integer
CurrentOEEHD = [ID]
strSQL = "SELECT * from TABLE Where CRITERIA =" & FilterOnForm & " Order by Time;"
Set rs = New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

With rs
.MoveFirst
x = 1
Do Until .EOF
![Ranking] = x
.MoveNext
x = x + 1
Loop
End With
rs.Close
Set rs = Nothing

Click to expand...

From here on out I can do the cehck from one recordset to another easily.