VB code is changing values and have no idea why

[RBE]

I am using this code to create a running total in MS Access 2000. It works great. However it only works once. After the query has been opened once It will keep adding the intcost to the gtotal starting from where it left off when the query was closed. Can you tell me how to make this a static code,where it will start with 0 every time the query is used. I have this query feeding another query that is feeding a report and the report may be run more then once a day, before access is closed and resets the query

Option Compare Database
Option Explicit
Global gTotal As Variant


Public Function Running_Total(intCost As Variant)
gTotal = gTotal + intCost
Running_Total = gTotal
End Function
You can always say tomarrow, but when you say yesterday, today is easier.

RBE

Our prayers go out to the WTC victims

 

[fenris]

Option Compare Database
Option Explicit


Public Function Running_Total(intCost As Variant)
dim gTotal As Variant '<=changed

gTotal = gTotal + intCost
Running_Total = gTotal
End Function


try the above changes and see if that does the trick.Any particular reason why you are using a variant? Variants should only be used when you have no other choice.

Troy Williams B.Eng.
fenris@hotmail.com

 

[fenris]

Disregard the code changes I made, My brain was not working properly!


Option Compare Database
Option Explicit
private gTotal As Variant

Public sub Running_Total(intCost As Variant)

gTotal = gTotal + intCost
Running_Total = gTotal
End sub

public sub resetTotal()
gtotal = 0
end sub


The modifications above will accomplish what you want with a minimum of changes. All you have to do is call the resetTotal subroutine before you start totaling the values.


Troy Williams B.Eng.
fenris@hotmail.com

 

[MichaelRed]

The following allows you to avoid having the additional routine. If you can include an additional BOOLEAN field in the source where the FIRST record is TRUE and hte remainder are false, you can just place this in the query as a calculated field. Otherwise, you still need to make a seperate call to the routine with the value of intCost as Zero and the boolean as true, as in

MyDummyVal = RunTot(0, True)

Public Sub RunTot(intCost As Variant, Optional ResetTot as Boolean = False) as Variant

[tab]If (ResetTot = True) Then
[tab][tab]GTotal = 0
[tab]End If

[tab]Static GTotal as Variant

[tab]gTotal = gTotal + intCost
[tab]Running_Total = gTotal

End sub

MichaelRed
m.red@att.net

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

 

[WilMead]

Find the place(s) in your code that know(s) that the query is opening and reset your related accumulator(s).

Excellent methods to do so are noted above. The optional reset flag is a nice touch. It is most usefull when you want to initialize and accumulate in one step, or initialize to a value other than zero.


Wil Mead
wmead@optonline.net