Subquery problem (1 Viewer)

[medof]

Hello

I have the attached database where i used a subquery to calculate the difference between the current record and the previous one

The problem that i can't add records to the form anymore ...

Is there a way to solve that ?? and if the answer is No ... is there any alternative method to get the same result ...?

Thanks

 

[MarkK]

You can do something like . . .

 

[medof]

wow .... great

Can i use the same code for Dates ? the Value field is Date

Option Compare Database
Option Explicit

Sub Recalculate()
    Dim rst As DAO.Recordset
    Dim dff As Long
    Dim nxt As Long
    Dim ttl As Long
    Dim Name As String
    Dim tot As Long
    
    Set rst = Me.RecordsetClone
    
    With CurrentDb.OpenRecordset( _
        "SELECT * FROM Table2 ORDER BY Name, Value" _
    )
        .MoveFirst
        Do While Not .EOF
            .Edit
            If Name <> !Name Then
                Name = !Name
                ttl = !Value
                !Next = Null
                !Diff = Null
                !Total = Null
            Else
                !Next = nxt
                !Diff = !Value - nxt
                ttl = ttl + !Value
                !Total = ttl
            End If
            .Update
            nxt = !Value
            .MoveNext
        Loop
        .Close
    End With
    Me.Recordset.Requery
    
End Sub

Private Sub Form_Load()
    Recalculate
End Sub