DateDiff to calculate time elapsed between current and other records.

Hi

Am using an adodb recordset to pass values from a set of masked edit boxes into a sql table.  The purpose of this exercise is to log tasks and their duration in a time recording program.  One of the edit boxes therefore passes to a 'duration' field.  However if a user does not enter a duration, I would like the program to automatically calculate what the duration is.

I assume i use the DateDiff function, but don't know how to go about picking up values from records other than the current one.

I have tried declaring a 'duration' string in the DateDiff expression and picking up values for it by moving between records, but get 'invalid procedure call or argument' error.

heres the code:

Private Sub cmdUpdate_Click()
    'The code for date and time can be input as date and time and then joined
    'in a string to bring both items together, that string is then added to the
    'recordset as the whole date and time.
    Dim ShortDate As String
    Dim ShortTime As String
    Dim DateTime As String
    Dim DurCalc As String
    Dim NextDur As String


    'in my database table i have fields start_date_time, cost_centre, task_code, duration
   
    DateTime = Format(ShortDate, "dd/mm/yyyy") & " " & Format(ShortTime, "hh:mm:ss")
    rec.Fields(1) = DateTime
    rec.Fields(2) = Format(mebCCentre.Text, ">")
    rec.Fields(3) = Format(mebTask.Text, ">")
    If rec.EOF = True Then
        rec.Fields(4) = Format(mebDur.Text, "0")
    Else
        rec.MoveNext
        NextDur = rec.Fields(1)
        rec.MovePrevious
        rec.Fields(4) = DateDiff("ssss", rec.Fields(1), NextDur)
    End If
       
End Sub