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?
 
GRayLConnect With a Mentor Commented:
That won't do it Jim:

For x = 1 to 124
   dSum = dSum  + Nz(Me("q" & x).Value,0)
Next
0
 
Jim HornConnect With a Mentor Microsoft 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:
Or is that what you meant by "Without any funky error handling"?  ;-)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
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.

All Courses

From novice to tech pro — start learning today.