How can I loop through 124 textboxes on a VBA form in MS Access, and have the values of these boxes sum up within a textbox called txtSum?

I have a VBA form that interacts with an MS Access dBase.

On my form I have 124 textboxes named q1 through q124.  I also have a textbox called 'txtSum'. How can I loop through these 124 textboxes and have their values sum up within the textbox called txtSum?

I have seen other answers on EE that address this, but I am having a bit of difficulty getting one that works…. so, I am posting a request that is specific and unique to my situation. I am aware that control arrays are not available in VBA (aarrgh!), but some posts talk about 'simulating' a control array. Can someone please assist me in creating this solution?.... it would help me incredibly. I am pretty good with VB6, VB.net, Asp.Net, but getting around this VBA control array limitation is frustrating. I am tired of writing lengthy 'linear' code over and over in VBA as a workaround.

I am a fairly decent programmer, but many thanks to those of you who really are 'experts'.... I couldn't learn without you guys....

jnh
LVL 4
jazjefAsked:
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Without any funky error handling, here you go...

Dim x as Integer, dSum as Double

For x = 1 to 124
   dSum = dSum  & Nz(Me("q" & x).Value,0)
Next

Me.txtSum.Value = dSum
0
GRayLCommented:
That won't do it Jim:

For x = 1 to 124
   dSum = dSum  + Nz(Me("q" & x).Value,0)
Next
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
GRayLCommented:
Or is that what you meant by "Without any funky error handling"?  ;-)
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
+ instead of &, you are correct.
0
jazjefAuthor Commented:
Dear JimHorn and GRayl:

I'm sorry. I have awarded the points backwards. I wanted to give JimHorn 400 being that he anwered first and provided the bulk of the answer. The 100 points for the assisted answer was for Grayl's correction of the "&" to the "+" sign. When I applied GRayl's correction to JimHorn's first code posting, the solution to my problem appeared like 'magic'. Thanks a million guys!

Now, how do I redistribute these points correctly? Anybody know?

Thanks....

0
GRayLCommented:
What if I had said nothing about the error in Jim's answer and posted 'my' solution without reference to anything else.  'Com, 50/50.
0
GRayLCommented:
Anyway, copy the address of this thread, create a new post to Community Support (click Support at the top of this page), paste in the address, and request your bidding.
0
jazjefAuthor Commented:
Yeah, that's a very good point GRayL.... in the hypothetical scenario you mention, JimHorn's solution would not have worked, and yours would have. If he's ok with a 50/50 I think it's fair... I am sure that he would most likely see your logic a clearly as I have....
0
jazjefAuthor Commented:
Bottom line: I do want to be as fair as possible, and would like both your input on a points split. Both of you have no idea how beneficial this code snippet has been to me already. It is going to save me a TON of excess coding and time....

THX again.
0
GRayLCommented:
Nah, leave it as is.  I was just having a bad day yesterday.  Going on vacation tomorrow.  That's good enough for me.
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.