MS Access 2010 Professional Sum textboxes on form without VBA....

Rog D
Rog D used Ask the Experts™
on

In MS Access 2003 I did  have a Total Text box with the
following statment in the control source

=[12JanFcst]+[12FebFcst]+[12MarFcst]+[12AprFcst]+[12MayFcst]+[12JunFcst]+[12JulFcst]+[12AugFcst]+[12SepFcst]+[12OctFcst]+[12NovFcst]+[12DecFcst]

This would automatically sum up the values in the 12 other fieds when one of them was changed.

In Access 2010 this does not seem to work anymore.

Is there a way to do this sum when a field changes.  Do I really need VBA to do this or is there a nice trick I can do?

I have about 12 columns like this on one form.  Maybe not best design from our perspective, but the user loves it.

Thanks,

Rog
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Have you enable macros?

In Access 2007 and 2010, you have to enable macros each time you open the application, or place your application in a trusted location.  Look for  the Access security warning below the ribbon bar.
Access-Security-Warning.jpg
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well ... it *should* ... be working. Only thing I can suggest is to wrap each section in Nz() to prevent "Null Propagation" - if a value is Null


=Nz([12JanFcst],0) + Nz([12FebFcst],0) + Nz([12MarFcst],0) .... and so on.

Try that .. and see if it makes a different ...
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
And here is the popup you will see after clicking the button in the above image (on your Access)
Access-Security-Alert.jpg
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
I cannot figure out how to get to the above Trust Center window you have above.
Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
I am not getting the Security Warning button on the screen as mentioned above.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
File>>Options>>TrustCenter ...

mx
Capture1.gif
Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
I did check this, but it didn't do anything different.

So this was a 2003 MDB.

I opend it in Access 2010.  (Still MDB) extension.

I am wondering if this has something to do with it.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
By not working, what do you get, if anything ?

mx
Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
Sorry....

I mean the value does not update.  If I close the form and open it back up the value is then updated.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
So, when you change a related field, it does not recalc, correct ?

mx
Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
Yes, eventhough the control source for the Total Text box has the calculation in it.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
ok ... as a test, for 1 or 2 of the related controls, put a

Me.ReCalc

in the AfterUpdate event of those controls.  Then, change a value in those controls, and see if calc field updates ...

mx
Rog DSQL Developer / Web Development / Business Analysis
Commented:
Ok....

Thanks for all the responses to this issue.

Found out what the issue was....  

In 2003 version of the mdb the text boxes had as the control name textbox1, textbox2, textbox3...
textbox12

In 2010 Verision of the mdb it also had the control name textbox1, textbox2, textbox3...
textbox12.  But when I changed the control name to be the actual database field name the summing worked as it did in 2003 version.  So I guess something changed in 2010 on the form where the summing will work as shown above but only if you have the database field name.

Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
Thanks again for your assistance.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well, I still think something else is going on.

There are two ways to sum ... using the Sum() aggregate function, which definitely requires Field names, and using the plus sign ... which can use either ...

mx
Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
I cannot really explain it.

It was originally and MDB file used in Access 2003.  When I opend th MDB file in Access 2010 the problem appeared.  So in Access 2003 it worked just fine.

I am guessing in 2010 something worked different.  Eiter a bug was created or a bug was fixed and it WAS NOT supposed to work in 2003 the way I had it.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well there are *definitely* things working differently in A2010 ... MUCH to my dismay. It's absolutely maddening .... so, no surprise there ...

mx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial