Put a text box on your main form.
Call it txtTimeTotal
In the control source of txtTimeTotal put this:
=GetTimeTotal(Me!ID)
Change the Me!ID to reflect whatever the name of your ID field is on the mainform
Now copy and paste everything between the lines below, into a new module, changing the red text to match your fields and tables.

===============
Function GetTimeTotal(lngID) As String
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String, strHours As String, strMinutes As String
    Dim intLeft As Integer, intRight As Integer, intValue As Integer, intTotal As Integer, intHours As Integer, intMinutes
    
    strSQL = "SELECT YourTimeField FROM YourSubformTableName WHERE(((id) = " & lngID & "));"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    With rs
        If .RecordCount > 0 Then
            .MoveFirst
            Do Until .EOF
                intLeft = Left(!test, 2)
                intRight = Right(!test, 2)
                intLeft = intLeft * 60
                intValue = intLeft + intRight
                intTotal = intTotal + intValue
                .MoveNext
            Loop
        End If
        .CLOSE
    End With
    
    intHours = intTotal / 60
    intMinutes = intTotal Mod 60
    
    If intHours < 10 Then
        strHours = "0" & intHours
    Else
        strHours = intHours
    End If
    
    If intMinutes < 10 Then
        strMinutes = "0" & intMinutes
    Else
        strMinutes = intMinutes
    End If
    
    GetTimeTotal = strHours & ":" & strMinutes
End Function
===============

Save the module as anything you want.
If all of the names were changed correctly, you should be able to open the form and the txtTimeTotal text box should calculate the time for that particular id.

Jim Lunde
compugeeks@hotmail.com
CompuGEEKS
Custom Application Development

Hi Jimmy,

Many thanks for the reply and taking the time to help with the above code. I am sorry that I have not replied until now but I have only just had a chance to try it due to work commitments.

I am sorry to say that the dreaded "kenny" factor has kicked in (what ever I try I end up breaking) and I cant get it to work. Forgive me if I go into detail, but Im sure you need all the facts.

Firstly. I created the text box on my main form exactly as you said to but when I look at it, the box displays "#Name?" so it would appear that it cant find its control source.

secondly, I cut and pasted your code into a new Modual and change the Ctl references as you said to to reflect the correct information. So where am I going wrong ?

I have placed the exact code from my modual at the end of this post, in case I have changed something that I shouldn't have by mastake.

Just out of interest, am I correct in assuming that the modual queries the underlieing table for its data based around the uniqueID I give to each Record Group, If this is so then the details I have entered are correct.

For example.
The table containing the data Is called [TblTracks]. The Ctl/field that contains the Length data is called [Length] (short time format) and the UniqueID ctl/field is called [RecordingID].

And finally, in case it counts, the form that I have placed the [txtTimeTotal] on is called [FrmDVCDAC] and I have named the modual [basGetTotalTime].
Also, when I type the line =GetTimeTotal(Me!RecordID) into the text boxes Contol Source, access changes it to: =GetTimeTotal([Me]![RecordingID]).


Any further suggestions or advice would be gratefully accepted.

***Code Start***
Function GetTimeTotal(lngID) As String
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String, strHours As String, strMinutes As String
    Dim intLeft As Integer, intRight As Integer, intValue As Integer, intTotal As Integer, intHours As Integer, intMinutes
    '[Forms]![FrmDECDACC]![SubFrmDED1].Form![RecordingID].ControlSource = "= [Forms]![FrmDECDACC]![SubFrmDED1].Form![Length]"
    strSQL = "SELECT Length FROM Tracks WHERE(((RecordingID) = " & lngID & "));"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    With rs
        If .RecordCount > 0 Then
            .MoveFirst
            Do Until .EOF
                intLeft = Left(!test, 2)
                intRight = Right(!test, 2)
                intLeft = intLeft * 60
                intValue = intLeft + intRight
                intTotal = intTotal + intValue
                .MoveNext
            Loop
        End If
        .Close
    End With
    
    intHours = intTotal / 60
    intMinutes = intTotal Mod 60
    
    If intHours < 10 Then
        strHours = "0" & intHours
    Else
        strHours = intHours
    End If
    
    If intMinutes < 10 Then
        strMinutes = "0" & intMinutes
    Else
        strMinutes = intMinutes
    End If
    
    GetTimeTotal = strHours & ":" & strMinutes
End Function
***Code End***