VBA afterupdate help (1 Viewer)

WarriorMCB

Registered User.
Local time
Today, 10:47
Joined
Jul 20, 2004
Messages
27
Hello,

I currently creating a DB to sum fields and place them in a db field. Which is working kinda fine until..

Private Sub VIP_Pcs_AfterUpdate()
Me.Total_Pcs.Value = Me.PcsTot
Me.Tax_Amt.Value = Me.TaxSum
Me.Bling_Total.Value = Me.BlgTot
Me.Refresh
End Sub

I have 3 other afterupdate fields which do the same thing after an enter is made. However, it doesn't always update in those fields unless I re-enter a vaue in a field that does work. I'd like it to update/refresh or whatever it needs to do in order for the given field to update (add/take) the sum after each entry. I hoped this is clear.

Thank you
 

Isaac

Lifelong Learner
Local time
Today, 07:47
Joined
Mar 14, 2017
Messages
8,738
Maybe? It has to do with the fine lines in between a control's Value and Text properties.
Your code, such as = Me.TaxSum, is doing what is called relying on a Default Property. That is, what you really are grabbing is the .Value property of TaxSum. To be explicit, you could code Me.TaxSum.Value, but since .Value is the default property, you don't technically need to.
However, depending on whether or not the control really has updated, which can vary depending on what has or loses focus at any given time.

Personally, I steer people away from relying overly on default properties. Because 1) usually they don't even realize they are doing it to begin with, 2) this habit just requires you to memorize another set of rules that there is no need to memorize if you specify the full thing. 3) the large amount of copy/paste internet code relies on .Value default of Control, which leads people to miss realizing that they actually need to consider whether they want .Value, .Text, .OldValue, etc. In your case it's possible that one of the controls involved in the code actually hasn't run its Update event yet, and thus, you need to refer to its .Text property--or, preferably, set the focus explicitly on something else, which will update it, and then .Value is fine.

In your case, one solution might be to move Me.Refresh to the beginning, rather than the end, of your code. This may cause the actual Value of all controls to update to = their Text, which is what your subsequent code requires(assumess).

This might not be the problem, but I like to call out the default property topic either way. Of course, my advice seems trivial in this one case, but in reality, thousands of things in code will have default properties and methods, (not just form controls) so you might as well know the path you are going down, habit-wise.
 

WarriorMCB

Registered User.
Local time
Today, 10:47
Joined
Jul 20, 2004
Messages
27
I'm sorry I have no idea on what you said. I don't really know vba code at all, just fudging along.
What I need is this. Fields 1 thru 4 say hold a value, this is then is multiplied by 5, field 5 multiplied by 25, and field 6 as is. These values are added then need to be put into field 7. Then there are the tax and shipping values which need to be auto calc'd and added into fields 8-9. I do not want to add these totals myself, hence why it pulls the sum from a hidden textbox which has the sum formula in it.
How can I do this on a form/query etc. please and thank you
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:47
Joined
Feb 19, 2002
Messages
42,984
I have 3 other afterupdate fields which do the same thing after an enter is made. However, it doesn't always update in those fields unless I re-enter a vaue in a field that does work. I'd like it to update/refresh or whatever it needs to do in order for the given field to update (add/take) the sum after each entry. I hoped this is clear.
This is just one of the problems associated with storing calculated fields. It is far better to add the calculation to the query so the form always shows the current value and not save the calculated value at all.

When you put the calculations in the query, make sure to use NZ() to avoid issues with nulls. You would bind a control to the calculated field. It will not be updateable. Access will take care of that for you.
 

Users who are viewing this thread

Top Bottom