MS Access Form Concantenation.

I have a form wilth multiple buttons Forms!frm_Vacation!Button1_R1 through Forms!frm_Vacation!Button5_R9 (Five rows of 9 buttons.)

I want to do a for loop and pull he value from each of the buttons.


How do I change the 1 on Button and the 1 on R to be a different value each time through the for loop?

for i=1 to 5
      for x=0 to 9
           if "Forms!frm_Vacation!Button" & i & "_R" & x & ".Caption" = 3 then
           end if

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Rey Obrero (Capricorn1)Commented:
try  this codes

dim ctl as control
for each ctl in me.controls
      if ctl.controltype = accommandbutton then

          debug.print "Button Name: " & & " Caption:  " & ctl.caption
      end if

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
Jeffrey CoachmanMIS LiasonCommented:
I am confused here...

1. Are you wanting to change (Increment) the button Caption (what is displayed on the button) , or the button Name (what the button is named, internally).
...So that on the first loop, the buttons display 1-9, then after the loop, they display 2-10?

2. I am not sure I understand what your "Value" variable is here:

3. What event will be triggering the loop...?

4. In other words, ...What is the purpose of this system?
It seems odd that a "Vacation" would require 45 buttons ...
Five rows of 9 buttons.
...and that those button captions (or names) would ever need to change...

So perhaps you could take a step back and explain to us your ultimate need here, ...perhaps your design is the issue, ...or perhaps there may be a simpler interface you could adopt.

rljarvisAuthor Commented:
Thanks Rey.  I'll give that a try and let you know how it comes out.

I have a form with twelve rows of 25 buttons. (Toggle1_b1 thru Toggle12_B25).  Each Row (Toggle1, etc) is a represents a Month.  Each Button on hat row (_B1. _B2, etc) represents a work date for the company.  When I open the form I populate the .Comment section with a Day extracted from a table of valid work days. (I've attatched a view).

Currently when I drive down to populate antoher button (Choice Botton) I have to do a count check to see where I am at.
if cnt=0 then Forms!frm_Vacation_Request!G_B1=Forms!frm_Vacation_Request!toggle1_B1
if cnt=1 then Forms!frm_Vacation_Request!G_B2=Forms!frm_Vacation_Request!toggle1_B1
if cnt=2 then Forms!frm_Vacation_Request!G_B3=Forms!frm_Vacation_Request!toggle1_B1
and so on till all twenty five locations have been checked.  The G_B1 buttrons display the actual date for the _B1 ".Caption" field plus th Month from the toggle? indication.  This results in a value for the appropriate field, determined by "cnt" to be "1/7" for a vacation day requested.

What I am trying to do is instead of having 25 lines for each button for each month is to use a for loop to run down through these 25 locations.  By =using something like the following line of code (whic does not work):

 Forms!frm_Vacation_Request!G_B & "for loop counter" & =Forms!frm_Vacation_Request!toggle1_B1

I hope that helps clarify what I am attemping to do.

Thanks for you ime snd help.
Determine the Perfect Price for Your IT Services

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

Jeffrey CoachmanMIS LiasonCommented:
Then see if what Rey posted will help.

I appreciate you taking the time to explain,. But I am still confused as to why all of this needs to be done.
Anytime anything this convoluted needs to be done,...typically a different interface is needed.

I am still having trouble seeing your overall goal here.

So, when you say things like:
When I open the form I populate the .Comment section with a Day extracted from a table of valid work days.
Currently when I drive down to populate antoher button (Choice Botton) I have to do a count check to see where I am at.

Without knowing what is actually happening, ...or why, ...I am still at a loss.

I was looking for a more generic, overall explanation.
For example:
"Each Button represents a day, When I click a button, my system does the following: __________________________________________________
Now I need a way to ____________________________

But again, go with what Rey posted to see if that is what you needed.

rljarvisAuthor Commented:
I have been working on what Rey posted and it works great at getting data from he contol.

What I want to do is put data back into control the same way.

My overall goal is this is a Vacation Requesting System.  I have so many days of Vacation available to me (employee) and I am choosing those date.

Step 1.  Press a Green Button under Choice 1.
Step 2. Press a Red Button associated with the Date I wish to take as Vacation.
Step 3. The system would then replace the "Caption" of the Green Button (pressed in step 1) with the date of the Red Button (pressed in Step 2 i.e. 1/7 assuming I pressed the Red Button from the 1st row (Jan) showing the 7)

The Yellow and Blue rows show my second and third choice for days I could take if my 1st (Green) choice is taken by someone with higher seniority then me.

Stp 4. When I am finioshed choosing dates I will qrite the Green, Yellow and Blue rows data to a file for later processing/reporting.
rljarvisAuthor Commented:
My button name is as follows:


In Rey's codehe ctl.Name returns just the "Toggle1_R1"

What I want my code to do is place a new "Caption" into the button.

 Forms.frm_Vacation_Request!Toggle1_R1.Caption = "1/7"

I have 25 buttons to potentially do each with a different vale ("1/7")

What I am trying or want to use Replace the Toggle1_R1 with a new name based on a for loop o do loop.

 Forms.frm_Vacation_Request! & New_Name & .Caption = "1/7"

Will not work.
Jeffrey CoachmanMIS LiasonCommented:
Then I am sure that Rey can get you going
rljarvisAuthor Commented:
Thanks for all your help guys.

Rey's process got data out, but I want to use that same process to put data in.
rljarvisAuthor Commented:
Problem Solved thanks to you two.  I was looking and making it to difficult.  Rey's code did exactly what I wanted to do.

 Dim ctl As Control
 For Each ctl In Me.Controls
       If ctl.ControlType = 122 Then

           Debug.Print "Button Name: " & ctl.Name & " Caption:  " & ctl.Caption
           ctl.Caption = "Test"
           Debug.Print "Button Name: " & ctl.Name & " Caption:  " & ctl.Caption
       End If

Button now contains the value "Test".

Thanks again.
Rey Obrero (Capricorn1)Commented:

just take note that when you close the form  and open it again, the caption will revert back to the original caption.
rljarvisAuthor Commented:
Great.  The form is person specific so that will work just fine.

Thanks again for the help.
Rey Obrero (Capricorn1)Commented:
don't forget to close this thread...
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.