Option Compare Database

Function SumDiFooterForm(FormKu As Form, ViewQueryKu As String, FieldToSumKu As String, ApplyTypeFilterKu As Integer) As Long

' revised 01
' made by Budi Laksamana
' 23 May 2007

' put unbound box inside the datasheet
' samples event procedure :

'Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
'    DataAdummy = SumDiFooterForm(Me, "ViewDtTRANSAKSI", "QTYIN", ApplyType)
'End Sub

'Private Sub Form_Load()
'    Me.Filter = adFilterNone
'    DataAdummy = SumDiFooterForm(Me, "ViewDtTRANSAKSI", "QTYIN", 0)
'End Sub

' then put box at the footer, equal to DataAdummy

Dim rsKu As ADODB.Recordset
Set rsKu = New ADODB.Recordset
rsKu.CursorType = adOpenStatic
rsKu.CursorLocation = adUseClient

Dim strSqlKu As String
Dim Filterku As String
Dim resultKu As Long
Dim Aku As String

On Error GoTo Err_SumDiFooterForm
    
    ' delete all formname from the filter string
    Filterku = Replace(FormKu.Filter, ViewQueryKu & ".", "")
    ' delete all parentheses(open bracket) from the filter string
    Filterku = Replace([Filterku], "((", "")
    ' delete all parentheses(closed bracket) from the filter string
    Aku = ")"
    Filterku = Replace([Filterku], Aku, "")
    ' delete all parentheses(open bracket) from the filter string, if any
    Filterku = Replace([Filterku], "(", "")
    ' replace aposthrope "" from string criteria in the filter string with quote '
    Aku = """"
    Filterku = Replace([Filterku], Aku, "'")

If ApplyTypeFilterKu = 1 Then
strSqlKu = "SELECT Sum(" & FieldToSumKu & ") As Result from " & ViewQueryKu & " Where " & Filterku
Else
strSqlKu = "SELECT Sum(" & FieldToSumKu & ") As Result from " & ViewQueryKu
End If

rsKu.source = strSqlKu
rsKu.ActiveConnection = CurrentProject.AccessConnection
rsKu.Open
resultKu = rsKu!result
rsKu.Close

Set rsKu = Nothing

Bye_SumDiFooterForm:
    SumDiFooterForm = resultKu    ' returning the result to the calling function
   Exit Function

Err_SumDiFooterForm:
   Resume Bye_SumDiFooterForm

End Function