Just so you know your Date and Time columns can be one column. A DateTime type field is a double that already stores both date and time. Also note that you shouldn't use the words Order, Date, or Time as field names as they are reserved words.
The following code should loop through each row of the table and update as desired. The code assumes that date and time are one field named [OrderDate] and that your order # field is called [OrderNumber]
Code:
Sub GenerateBatchNumbers()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim count As Long
Dim prevOrderNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT OrderNumber, Batch FROM DataTable ORDER BY OrderNumber, OrderDate")
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
count = 1
prevOrderNumber = rs!OrderNumber
Do While Not rs.EOF
If rs!OrderNumber <> prevOrderNumber Then
count = 1
prevOrderNumber = rs!OrderNumber
End If
If rs!batch <> count Or IsNull(rs!batch) Then
rs.Edit
rs!batch = count
rs.Update
End If
count = count + 1
rs.MoveNext
Loop
End If
rs.Close
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox "Error generating batch numbers"
Resume ExitHandler
End Sub
You're essentially creating a calculated field. We really don't like those. What is this being used for?