Sum from subform with specific records

[Nightfire22255]

Hi all,

Need some guidance. I have a subform setup. In my main form, I have a textbox that gives me a total of a whole column from the subform. The way I did that was through having a control in the subform that calculates the total. Then I basically showed that control source on my main form. Easy.

What I'm trying to do is get a total with specific records or filters simultaneously with my other total textbox.

This picture will show you what I want to happen. Specifically, I want to only sum the records when the boolean result is true.

Thanks

 

[Ranman256]

txtTotReimbur =Dsum("[Amt]","qsSubFormQry","[Reimburse]=true")
txtTotMission =Dsum("[Amt]","qsSubFormQry","[mission]=true")

where the query in the subform ,is used in the DSUM,
but the Dsum filters on the 1 column.

 

[arnelgp]

for Total Reimbursable:

=Sum([Reimburse]*[Quote To]*-1)

for Total Mission:

=Sum([Mission]*[Quote To]*-1)

//Edit: the code above is for continuous form.

add code to the current event of the subform:

Private Sub Form_Current()
    Dim dblReimbursable As Double
    Dim dblMission As Double
    With Me.RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            dblReimbursable = dblReimbursable + (![Reimbursable] * ![Quote To] * -1)
            dblMission = dblMission + (![Mission] * ![Quote To] * -1)
            .MoveNext
        Loop
    End With
    Me.Parent![txtReimbursable] = dblReimbursable
    Me.Parent![txtMission] = dblMission
End Sub

 

[Nightfire22255]

Thanks for replying.

I tried your code. I got 7951 runtime error. Did I forget something?

 

[arnelgp]

you put the code in the Subform's Current event.