Sum from subform with specific records (1 Viewer)

Nightfire22255

New member
Local time
Yesterday, 16:17
Joined
Dec 21, 2020
Messages
6
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
example.png
 

Ranman256

Well-known member
Local time
Yesterday, 22:17
Joined
Apr 9, 2015
Messages
3,813
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

error reading drive A:
Local time
Today, 10:17
Joined
May 7, 2009
Messages
12,080
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:
Code:
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
 
Last edited:

Nightfire22255

New member
Local time
Yesterday, 16:17
Joined
Dec 21, 2020
Messages
6
Thanks for replying.

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

arnelgp

error reading drive A:
Local time
Today, 10:17
Joined
May 7, 2009
Messages
12,080
you put the code in the Subform's Current event.
 

Users who are viewing this thread

Top Bottom