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


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
LVL 8
Rog DManager Inforamtion SystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 ...
0
Dale FyeCommented:
And here is the popup you will see after clicking the button in the above image (on your Access)
Access-Security-Alert.jpg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rog DManager Inforamtion SystemsAuthor Commented:
I cannot figure out how to get to the above Trust Center window you have above.
0
Rog DManager Inforamtion SystemsAuthor Commented:
I am not getting the Security Warning button on the screen as mentioned above.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
File>>Options>>TrustCenter ...

mx
Capture1.gif
0
Rog DManager Inforamtion SystemsAuthor 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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
By not working, what do you get, if anything ?

mx
0
Rog DManager Inforamtion SystemsAuthor Commented:
Sorry....

I mean the value does not update.  If I close the form and open it back up the value is then updated.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So, when you change a related field, it does not recalc, correct ?

mx
0
Rog DManager Inforamtion SystemsAuthor Commented:
Yes, eventhough the control source for the Total Text box has the calculation in it.

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
Rog DManager Inforamtion SystemsAuthor 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.

0
Rog DManager Inforamtion SystemsAuthor Commented:
Thanks again for your assistance.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
Rog DManager Inforamtion SystemsAuthor 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.

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well there are *definitely* things working differently in A2010 ... MUCH to my dismay. It's absolutely maddening .... so, no surprise there ...

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.