We help IT Professionals succeed at work.
Get Started

Define Global Variables with a For-To loop

Last Modified: 2016-02-11
I have a form in which there are 24 fields that can be filled with a numeric value.  They are arranged in rows and columns.  There are 3 rows reflecting TIme Zones. There are 8 columns, representing the 8 users.  The purpose is to distribute x number of records, of any values, from the different time zones to any or all of the 8 users.

I have a command button that will sum up all of the separate users' values and display the values in the total row.  To do this, I can identify the various fields by concatenating the text parts of the field names with the counter in the For-To loop.  The code for this follows:
For intTZCount = 1 To 8
        If Not IsNull(Me("txtUser" & intTZCount & "TZ1")) Then
            intTZRow1 = intTZRow1 + Me("txtUser" & intTZCount & "TZ1")
        End If
        If Not IsNull(Me("txtUser" & intTZCount & "TZ2")) Then
            intTZRow2 = intTZRow2 + Me("txtUser" & intTZCount & "TZ2")
        End If
        If Not IsNull(Me("txtUser" & intTZCount & "TZ3")) Then
            intTZRow3 = intTZRow3 + Me("txtUser" & intTZCount & "TZ3")
        End If

    Next intTZCount

This works like a charm.

The new task at hand is:
Save all of the form values.  Once saved, the values can be changed in the form.  Then, have the ability to recall the saved values and replace the current form values with the saved values.  In other words, in effect, take a snapshot of the form and retrieve it at will.

In the Global module, I created Public Variables that correspond to each of the fields in the form (24 total).  So it is setup like this:
gstrUser1TZ1           =  txtUser1TZ1
gstrUser1TZ2           =  txtUser1TZ2
gstrUser1TZ3           =  txtUser1TZ3
gstrUser2TZ1           =  txtUser2TZ1
gstrUser2TZ2           =  txtUser2TZ2
gstrUser2TZ3           =  txtUser2TZ3
gstrUser8TZ1           =  txtUserTZ1
gstrUser8TZ2           =  txtUserTZ2
gstrUser8TZ3           =  txtUserTZ3

Is there a way to have a For-To loop to build the Global Variables, similar to the method used in the code above to reference the field names in the form?  Something like:

For Counter = 1 to 8
      "gstrUser" & Counter & "TZ1" = Me("txtUser" & Counter & "TZ1")
       "gstrUser" & Counter & "TZ2" = Me("txtUser" & Counter & "TZ2")
       "gstrUser" & Counter & "TZ3" = Me("txtUser" & Counter & "TZ3")
Next Counter

Basically, I am trying to identify a variable by concatenating strings and variables.  It fails each time I write the code.
Is this possible to do?
Is there a more graceful way to accomplish this?
Obviously, I could list all 24 Global variables and assign the corresponding field value that way, but it seems using the For-To loop is a more efficient way to do it.

Thanks for any inut...
Watch Question
President / Owner
Most Valuable Expert 2017
This problem has been solved!
Unlock 1 Answer and 12 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE