Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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?

Posted on 2006-07-20
10
Medium Priority
?
436 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:jazjef
  • 5
  • 3
  • 2
10 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1600 total points
ID: 17149470
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
 
LVL 44

Accepted Solution

by:
GRayL earned 400 total points
ID: 17149662
That won't do it Jim:

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

Expert Comment

by:GRayL
ID: 17149674
Or is that what you meant by "Without any funky error handling"?  ;-)
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 66

Expert Comment

by:Jim Horn
ID: 17149689
+ instead of &, you are correct.
0
 
LVL 4

Author Comment

by:jazjef
ID: 17161708
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17161724
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17161737
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
 
LVL 4

Author Comment

by:jazjef
ID: 17162033
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
 
LVL 4

Author Comment

by:jazjef
ID: 17162044
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17163463
Nah, leave it as is.  I was just having a bad day yesterday.  Going on vacation tomorrow.  That's good enough for me.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question