Pass calculated balance value from one record to the next?

[gusbrunston]

Hi-
I use a subform to display the transactions in client accounts. Continuous forms, one line per form. I've been asked to display a running balance at the end of each line (record). I think I want to accumulate the balance, beginning with the client's first transaction, displaying that balance on each record. i.e.,

Date        ID#      Cr      Dr    Balance

01/01/01   1202  100.00             100.00
01/12/01  47776           25.00      75.00
02/15/01  48102           12.00      63.00
02/18/01   1350  350.00             413.00

The sub form displays the transactions in one client's account. Each line is a record in the table "Transactions". Trouble is, I don't know how to calculate the accumulated (Variable?) "Balance". (Hate to admit that...) I could use any help I could get. Thanks in advance.

Gus Brunston
An old PICKer
padregus@home.com

 

[jjonesal]

I think you might be able to add a new field to your subform that uses the DSUM function (documented well in help files). This will allow you to get a running sum over a particular group of records.... (in this case, where your data is <= the current record's date).... Of course since you have two columns (DR, CR), you may have to create a calculated column (not displayed), that will actually give you one column to work on with DSUM.

newamt: iif(CR=&quot;&quot;,DR,CR*-1)
This will give you your amounts including signs, then you can use DSUM on this field...

Hope this helps.... J. Jones
jjones@cybrtyme.com

 

[gusbrunston]

THANK YOU J!
...for pointing me to DSum. I created an unbound text box and DSum did exactly what I needed to accomplish in the sub form. It works perfectly when I view the sub form independently. BUT, when I view it from within the main form, it gives me: &quot;#Error&quot;! Does it matter that the sub form's control source is a different query than the main form? Any ideas?
I will post Control Source I entered (which is largely dependent on MS help in their support area) in case it might be helpful. (Notice the real name for CR is ReceiptAmount, and for DR it's PaymentAmount.)

=DSum(&quot;[DepositAmount]&quot;,&quot;qryTransactions&quot;,&quot;[TransactionID] <= Forms![subfrmPropertyTransactions]![TransactionID]&quot;)-DSum(&quot;[PaymentAmount]&quot;,&quot;qryTransactions&quot;,&quot;[TransactionID] <= Forms![subfrmPropertyTransactions]![TransactionID]&quot;)

Thanks again!


Gus Brunston
An old PICKer
padregus@home.com

 

[jjonesal]

Can you include DSUM in an underlying query rather than on the form? Then, base the subform on the query....See if that changes the symptoms any.... J. Jones
jjones@cybrtyme.com

 

[MichaelRed]

Actually, you can avoid the overhead of the domain aggregate Function(e.g. DSum). In the query for the subform, add a calculated field

TransNet: = Nz([Cr], 0), - Nz([Dr], 0)

On the subform, add a field at the &quot;end&quot; of the query grid with TransNet as the control source and set it's running sum property.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Sorry, ther is a &quot;typo&quot;

TransNet: = Nz([Cr], 0), - Nz([Dr], 0)

should be

TransNet: = Nz([Cr], 0) - Nz([Dr], 0)

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[gusbrunston]

Thanks, Michael, for the TransNet suggestion.
I did what you suggested, up to the point of setting the running sum property, which I can't find. I thought that property was only for reports?
s-)
Gus Brunston
An old PICKer
padregus@home.com

 

[MichaelRed]

OOpssssss, Sorry. Leap of faith ... lands in MUD!.

Still, should be able to &quot;fool&quot; mother nature.

In the form module create a variable:

[tab]Dim RunSum as Currency

in the subform detail event, Do the math:

RunSum = RunSum + [TransNet]

And assign the results to the textbox
(whatever you called it on the form).

I'm not doing a lot of Ms. Access at the moment, so there are a couple of things that 'slip' my mind (?senior moments? hours? days? ???LONGER INTERVALS??????).

To use this approach, you will need to re-set the RnuSum var under certain conditions, so it might be better to do the run sum calc as a small module in the query (make sure you place the RunSum calc &quot;AFTER&quot; the TransNet calc.

The following two procedures should do it (works fo me with a small set of values in a Form). The first does the actual running sum, the second re-initalizes the accumulator to 0 on activation. You may want/need to change how / where the initalization is done.

I THINK (senior &quot;period&quot;?) it would just be:

Public Function SumOfTrans(TransAmt As Currency, Optional Reset As Boolean = False) As Currency

    Static SumOf As Currency

    If (Reset) Then
        SumOf = 0
    End If

    SumOf = SumOf + TransAmt

    SumOfTrans = SumOf

End Function

'_________________________________________________________

Private Sub Report_Activate()

    'Just to Reset the Running Sum Calculation.
    Junque = SumOfTrans(0, True)

End Sub
[code]

 MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[gusbrunston]

Thanks, J. and Michael, for your response to my posting. I've discovered another problem with my &quot;Balance&quot;ing Act:

While I can display the appropriate records pertinent to each client, the (calculated) Balance (I finally used DSum) figure is based on ALL the records in the table, and so the Balance displayed for any client is not correct.

I'm reading, experimenting with SQL view of the query, hoping to learn enough so that by WHEREing or HAVING or GROUP BYing or by a combination of expressions I can select only those records that pertain to the current client. If this trolley won't take me to Toonerville, I'll need a transfer...

|-0 Gus Brunston
An old PICKer
padregus@home.com

 

[MichaelRed]

Oh, well, sorry my advice wasn't useful. What I've posted works - at least for the simple case. DSum will ALSO work, but you need to define the domain properly. First of all, I REALLY dislike the domain aggregate functions provided by Ms. in Access. My belief here is that it is just Ms's way of providing a lazy person the query results which are at least as easy to build directly. Wecondly, they are SlLlLlLoooooOOOOOOooooo! Here, I think Ms. is just being realistically CAREFUL. After all, the User was to &quot;987^&*(^%&@#(&#(*&quot; lazy to verify the domain (by simply building the query!), so &quot;she&quot; (Ms. Access) needs to be doubly careful!

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[gusbrunston]

Dear Michael,
Please don't misunderstand me...your postings have been and continue to be useful! It's just that I've reached a point where the learning curve seems awfully steep. e.g., I'm not even sure what a &quot;domain&quot; is (except in a LAN). I've printed your last posting, and as soon as I have the necessary skill to use it, I want very much to avoid the overhead of the domain aggregate functions. I'm giving the next few hours to the 70 or so pages in my Access reference book devoted to advance use of queries (SQL). When I finish that maybe I'll know a little more about what I'm doing. Thankfully, my client is my wife, and she's quite patient and understanding about delays in enhancements to her program!
Thanks again,
Gus Gus Brunston
An old PICKer
padregus@home.com