CODEWITHSUNDEEP
ms-accesseventssubform
Richard Bates
Richard Bates

Reputation: 31

Subform calulation error when pasting multiple records in subform

I do a calculation on all three Events on a subform to update the main form:

Private Sub Form_AfterDelConfirm(Status As Integer)
  Me.Parent.UpdateStunden
End Sub

Private Sub Form_AfterInsert()
  Me.Parent.UpdateStunden
End Sub

Private Sub Form_AfterUpdate() 
  Me.Parent.UpdateStunden
End Sub

Public Sub UpdateStunden(Optional BeforeUpdateEvent As Boolean = False)
On Error GoTo ErrorHandler
Dim rst As Recordset
Dim sql As String
Dim NewStunden As Variant

    If Me.NewRecord Then Exit Sub

    sql = _
    "SELECT Sum(Stunden) AS SumStunden " & _
    "FROM Tätigkeiten " & _
    "WHERE Tätigkeitsdatum = #" & Format(Me!Tätigkeitsdatum, "yyyy-mm-dd") & "#;"

    Set rst = CurrentDb().OpenRecordset(sql, dbOpenSnapshot)

    If Not rst.EOF Or Not rst.BOF Then
        NewStunden = rst!SumStunden
        If Nz(NewStunden) <> Nz(Me.Stunden) Or IsNull(Me.Stunden) Then
            Me.Stunden = NewStunden
        End If
    End If

ExitPoint:
    On Error Resume Next
    If Me.Dirty And Not BeforeUpdateEvent Then Me.Dirty = False
    rst.Close
    Set rst = Nothing
    Exit Sub

ErrorHandler:
    Select Case Err
        Case Else:  LogNTEvent Now & "Error: " & Err & ": " & Err.Description & ": UpdateStunden", EVENTLOG_ERROR_TYPE, 1000, "Error: " & Err.Number
    End Select
    Resume ExitPoint
End Sub

This normally works fine.

But there is a bug if I copy and paste more than one record into the subform. When I copy and paste multiple records from one subform to the same subform but on another main-form record the calculation is wrong.

This should work as Standard because I use it everywhere.

We Need an After Paste Event!

Does anyone know how to do this?

Regards Richard

Upvotes: 1

Views: 216

Answers (2)

Richard Bates
Richard Bates

Reputation: 31

Just set the Control-Source of the Textbox-Control on the main form to this:

=DomSumme("Stunden";"Tätigkeiten";"Tätigkeitsdatum = #" & Format([Tätigkeitsdatum];"jjjj-mm-tt") & "#")

And then requery after all three Events:

Private Sub Form_AfterDelConfirm(Status As Integer)
    Me.Parent!Test.Requery
End Sub

Private Sub Form_AfterInsert()
    Me.Parent!Test.Requery
End Sub

Private Sub Form_AfterUpdate()
    Me.Parent!Test.Requery
End Sub

N.B. German Office 2010

Upvotes: 1

Richard Bates
Richard Bates

Reputation: 31

Just set the main form value to null from the subform:

Private Sub Form_AfterDelConfirm(Status As Integer)
    Me.Parent.Stunden = Null
End Sub

Private Sub Form_AfterInsert()
    Me.Parent.Stunden = Null
End Sub

Private Sub Form_AfterUpdate() 
    Me.Parent.Stunden = Null 
End Sub

Then use the timer:

Private Sub Form_Timer()
    If IsNull(Me.Stunden) Then UpdateStunden
End Sub

Private Sub Stunden_AfterUpdate()
    Me.Dirty = False
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    UpdateStunden True
End Sub

Public Sub UpdateStunden(Optional BeforeUpdateEvent As Boolean = False)
On Error GoTo ErrorHandler
Dim rst As Recordset
Dim sql As String
Dim NewStunden As Variant

    If Me.NewRecord Then Exit Sub

    sql = _
    "SELECT Sum(Stunden) AS SumStunden " & _
    "FROM Tätigkeiten " & _
    "WHERE Tätigkeitsdatum = #" & Format(Me!Tätigkeitsdatum, "yyyy-mm-dd") & "#;"

    Set rst = CurrentDb().OpenRecordset(sql, dbOpenSnapshot)

    If Not rst.EOF Or Not rst.BOF Then
        NewStunden = rst!SumStunden
        If Nz(NewStunden) <> Nz(Me.Stunden) Or IsNull(Me.Stunden) Then
            Me.Stunden = NewStunden
        End If
    End If

ExitPoint:
    On Error Resume Next
    If Me.Dirty And Not BeforeUpdateEvent Then Me.Dirty = False
    rst.Close
    Set rst = Nothing
    Exit Sub

ErrorHandler:
    Select Case Err
        Case Else:  LogNTEvent Now & "Error: " & Err & ": " & Err.Description & ": UpdateStunden", EVENTLOG_ERROR_TYPE, 1000, "Error: " & Err.Number
    End Select
    Resume ExitPoint
End Sub

Upvotes: 1

Related Questions