CODEWITHSUNDEEP
ms-access
Sphere
Sphere

Reputation: 29

SQL SUM statement in VBA function

My database has three tables, Holder, Product (which is an account) and Transaction. I have set up a form from the Holder table that has a subform from the Product table and the Product subform has the Transaction table as a subform. On the Holder portion of the form, I have put an Outstanding unbound text field that should display the total amount and tax fields of transactions from the transaction table that have not been paid (indicated by a checkbox on the Transaction table). I have set the control source of the unbound text box to =calcOutstanding() and written the following function for the form.

Public Function calcOutstanding()

Dim db As Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT SUM(tblTransaction.TxAmount + tblTransaction.TxTax) As Outstanding" _
    & "FROM tblTransaction" _
    & "INNER JOIN tblProduct ON tblTransaction.fkProductID = tblProduct.ProductID" _
    & "INNER JOIN tblHolder ON tblProduct.fkHolderID = tblHolder.HolderID" _
    & "WHERE tblTransaction.TxPaid = False" _
    & "AND tlbHolder.HolderID = Me.HolderID;"

DoCmd.RunSQL strSQL

calcOutstanding = Outstanding

End Function

The field now just shows #Error. What am I doing wrong?

Upvotes: 0

Views: 14042

Answers (3)

Krish
Krish

Reputation: 5917

I doubt you need an external function to do this. MS Access allows you to reference fields from subform simply by Me.Subform!FieldName.Value

This means, you could simply access the subform fields that are related to your current record. Even perform IIF(condition, truevalue, falsevalue) on that field

read more about accessing forms and subforms here: http://access.mvps.org/access/forms/frm0031.htm

EDIT: in your third subform (tbl_transaction), create a new unbound TextBox called (txt_outstanding) and assign this expression

=IIF([txPaid]=false, sum(txAMount +TxTax),0)

now you can access this field in your parent form something similar to this:

me.txt_someTextbox.value = nz(me.tbltransactionsubform!txt_outstanding.value,"")

Upvotes: 0

VBlades
VBlades

Reputation: 2251

There's a bunch wrong with your approach:

  1. DoCmd.RunSQL is just for action queries (INSERT, UPDATE, DELETE).
  2. You cannot return a value from DoCmd.RunSQL like you are attempting to and push it into a variable.
  3. Your concatenation for the where clause is incorrect.
  4. As HansUp mentioned, Access is very picky about parentheses in JOINs in its SQL.

Assuming the SQL is correct, this code lives on the parent form, and you dno't get multiple rows back in your query, maybe something like this would work:

Public Function calcOutstanding() As Currency

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    Set db = CurrentDb

    strSQL = "SELECT SUM(tblTransaction.TxAmount + tblTransaction.TxTax) As Outstanding " _
        & "FROM (tblTransaction " _
        & "INNER JOIN tblProduct ON tblTransaction.fkProductID = tblProduct.ProductID) " _
        & "INNER JOIN tblHolder ON tblProduct.fkHolderID = tblHolder.HolderID " _
        & "WHERE tblTransaction.TxPaid = False " _
        & "AND tlbHolder.HolderID = " & Me.HolderID

    Set rst = db.OpenRecordset(strSQL, dbForwardOnly)

    calcOutstanding = rst![Outstanding]

    Set rst = Nothing
    set db = Nothing

End Function

Notice the concatenation in the WHERE clause to get the value from the form's data source (otherwise the SQL couldn't reconcile Me.HolderID within the scope of the SQL itself). Also, we push the returning dataset into a recordset and read from that. Something along these lines should work, I think. (Not in front of Access now, so sorry if any non-compiling statements.)

EDIT: Added the function return type as integer for specificity's sake. EDIT 2: Added the function return type as currencyfor specificity's sake. Doh.

Upvotes: 2

Security Aficionado
Security Aficionado

Reputation: 486

Right off the bat, I see a problem in the code you posted:

strSQL = "SELECT SUM(tblTransaction.TxAmount + tblTransaction.TxTax) As Outstanding" _
& "FROM tblTransaction" _
& "INNER JOIN tblProduct ON tblTransaction.fkProductID = tblProduct.ProductID" _
& "INNER JOIN tblHolder ON tblProduct.fkHolderID = tblHolder.HolderID" _
& "WHERE tblTransaction.TxPaid = False" _
& "AND tlbHolder.HolderID = Me.HolderID;"

There should be a space either at the end of each line Outstanding " _ or at the beginning of each line like " FROM tblTransaction otherwise your string will read OutstandingFROM tblTransaction when parsed, which will give you an error.

Upvotes: 0

Related Questions