Total sum in Subform After filter

[nashfaq]

Hello Team

i have a subfrom, that filters the list and then shows their payments.
i want the total at the end of sub form that my form filters.

the code i wrote to filter as below, but i am confused how to get their sum that changes as the form filters.

please help.

regards
Nasir

Private Sub btnSearch_TJ_Click()
Dim SQL As String


SQL = "SELECT qryTjWadaListFamily1_1.Code, qryTjWadaListFamily1_1.[Person Name], qryTjWadaListFamily1_1.F_H_Name, qryTjWadaListFamily1_1.[Wada Threek Jadeed], qryTjWadaListFamily1_1.[Family Code], qryTjWadaListFamily1_1.[Payment ] ,qryTjWadaListFamily1_1.[Class],qryTjWadaListFamily1_1.[BAL]" _
& "FROM qryTjWadaListFamily1_1 " _
& "WHERE

 LIKE '*" & Me.txtKeywords & "*' " _
         & " OR [Person Name] LIKE '*" & Me.txtKeywords & "*' " _
         & "ORDER BY qryTjWadaListFamily1_1.[Code] "
        
  '' Me.lstCustomers.RowSource = SQL
  ''  Me.lstCustomers.Requery
        
 Me.subCustomerList_TJ.Form.RecordSource = SQL
 Me.subCustomerList_TJ.Form.Requery
    
       
End Sub

 

[June7]

Are you using Sum() expression in textbox in subform header or footer section?

 

[nashfaq]

Yes I am using.. But I show total of all table..
Instead of filtered data

 

[June7]

Sum() can only calculate on records retrieved and should recalculate after filtering. Post the expression or provide db for analysis.

 

[nashfaq]

I just add one more line

Me.subCustomerList_TJ.Form.Filter on

And now I am getting sum after filtering...

Thanks for help

 

[June7]

Interesting, I would have thought the Requery would have handled that. Glad you have a solution.

Instead of setting form RecordSource, I just set the Filter and FilterOn properties.