Procedure is executed in reverse

[TTThio]

Hi guys,

there's something confusing happens in my app, I have no clue at all:

txtOne txtTwo txtSumTwo txtTotal
1000 0 0 1000
1500 500 500 2000

txtONe = control source: txt1 in the underlying query
txtTwo = control source: txt2 in the underlying query
txtSumTwo = control source : sum([txtTwo])

Sub txtTwo_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
me.refresh

If isnull([txtTwo]) or txtTwo = 0 then
txtTotal = txtOne
Else
txtTotal = txtOne + txtSumTwo
End if
End Sub

I try to set a break, and step into the code, it does follow the right track.
Inside the code, when I hoover on top of each variable,
(insertion point in the record 2),
txtTwo(= 500)
so it goes to Else statement
txtTotal (=1500)=txtOne(=1500) + txtSumTwo(=500)
Stepping out of the code and back to the form, yes....txtTotal shows 2000

But, if I try to change again, after update, txtTotal calculation doesn't work again (txtTotal = txtOne)

Isn't it supposed to work just like when you step into the code?

I try to test out how the procedure really works without stepping into the code by displaying a msgbox.

I set in the txtTwo_afterupdate()
docmd.runcommand accmdsaverecord
me.refresh
If...Then... (as above)
msgbox txtTotal

I notice, after tabbing out from txtTwo, the message box is displayed, then txtTwo is blinking meaning it's being refreshed.

From this, seems like the procedure is executed in reverse,

msgbox txtTotal
me.refresh

How could it happen? I'm totally lost....

Any idea?

Thanks ahead,
Tin Tin

 

[JoeMiller]

All you are doing is adding txtOne and txtTwo to come up with a total. I'm assuming you are using the if statement to look for a null or a zero in txtTwo because it's not always occupied. So if that's the case you don't need any code at all, just set the ControlSource of txtTotal to:

=Nz(txtOne) + Nz(txtTwo)

Nz looks at the thing in parenthesis and if it's Null returns a Zero so that you can use it in a math equation (in your instance). So that's all you need it would appear from here. Am I missing something?
Joe Miller
joe.miller@flotech.net

 

[TTThio]

Hi Joe,

nice to hear from you again. And congrats for being elected as last two-weeks' expert.

About the app,

I cannot do txtOne + txtTwo because txtThree is actually a cummulative total of txtOne applicable on each record. I try to explain in better way.

Before, this is how I do:
txtOne txtTwo(prev value) txtSumTwo txtTotal
1000 0 1000 1000
1500 1000 1000 2500
2000 1500 1000 4500

I didn't do me.refresh, and then in the Else statement
txtTotal = txtOne + txtTwo + txtSumTwo

As you see, txtSumTwo is actually showing one same value in each record as result of summing txtTwo (as there's no refresh, values shown is not updated to current record actual value = 2500). I don't know though why txtTotal of record1 and record2 is not affected as if making txtTotal's control source = txtOne + txtTwo + txtSumTwo (txtTotal value will always change everytime I have new record. It will show 2000,3500,4500, etc.

That's why I do the calculation in the code instead of in the control source.

As I find the most current value of txtSumTwo as displayed in the screen doesn't affect txtTotal calculation of previous records, then I do me.refresh (to display the most updated value),

txtOne txtTwo txtSumTwo txtTotal
1000 0 2500 1000
1500 1000 2500 1500
2000 1500 2500 2000

and change the Else statement into
txtTotal = txtOne + txtSumTwo

But then, txtTotal keeps equal to txtOne.
If I change back to txtTotal = txtOne + txtTwo + txtSumTwo
because of the me.refresh txtTotal value result is just equal to txtOne + txtTwo. It doesn't count txtSumTwo value.

And it keeps display msgbox first then refresh.

I really need help in understanding how it works....

Tin Tin

 

[JoeMiller]

Is this what the data should be coming out like?

[tt]
txtOne txtTwo txtSumTwo txtTotal
1000 0 1500 1000
1500 1000 3500 1500
2000 1500 5500 2000
[/tt]

So txtSumTwo is actually a running sum of each line's total and txtTotal is a total for the singular line item itself?


Joe Miller
joe.miller@flotech.net

 

[TTThio]

It's supposed to be:


txtOne txtTwo txtSumTwo txtTotal
1000 0 0 ("" + 0) 1000 ("" + 1000)
1500 1000 1000 (0 + 1000) 2500 (1000 + 1500)
2000 1500 2500 (1000+ 1500) 4500 (1000+1500+2000)

txtSumTwo = sum([txtTwo])
txtTotal = cummulative total of txtOne

 

[TTThio]

Joe,

thanks for trying to help me with the problem.

The good news is, I just found a way to solve it.
And I like to share it here, just in case someone else encounters the same problem.

I learned that txtTotal = txtOne + txtSumTwo cannot be executed because txtSumTwo = sum([txtTwo]). So, even though the screen displayed a number, it's somehow considered as zero.

So, what makes it work then:

Sub txtTwo_AfterUpdate()
Dim A as integer
me.refresh (or other code to save the txtTwo value
after update in query/table)

A = Dsum("txtTwo", "query/table&quot
txtTotal = txtOne + A

End Sub

Once I read in the 'Golden Bible of Mastering Access' that the limitation of Access is that you cannot perform calculation on domain aggregated function, exp. sum ([calculated field]) where [calculated field] = sum([field])
I didn't understand how come it also doesn't work out when performing
[calculated fieldX] = [field1] + [calculated fieldY]

Well, probably I've done something wrong with it.
Anyway, now it works.

Thanks God, and thanks for giving some ideas.
And if Gordon read this, thanks to you too for the "save always after update" suggestion.

Tin Tin