# Define Global Variables with a For-To loop

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:
GLOBAL VARIABLE  =  FORM FIELD
gstrUser1TZ1           =  txtUser1TZ1
gstrUser1TZ2           =  txtUser1TZ2
gstrUser1TZ3           =  txtUser1TZ3
gstrUser2TZ1           =  txtUser2TZ1
gstrUser2TZ2           =  txtUser2TZ2
gstrUser2TZ3           =  txtUser2TZ3
.....
gstrUser8TZ1           =  txtUserTZ1
gstrUser8TZ2           =  txtUserTZ2
gstrUser8TZ3           =  txtUserTZ3

QUESTION:
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...
Microsoft AccessVisual Basic ClassicMicrosoft OfficeVBA

Last Comment
PatHartman

8/22/2022 - Mon
Jim Dettman (EE MVE)

Use a global array.  You can then use you loop count as the subscript.

Jim.
PatHartman

You might consider normalizing your schema.  What happens if you have to add a ninth user?  What about a fourth time zone?  What if you only need 6 users for one time zone?
beyondt

Jim:
I am not too familial with arrays.  Would the modified code using arrays look something like this:

ArrayUser = Array(1 to 8)
ArrayTZ = Array(1 to 3)

For TZCounter 1 to 3
For UserCounter = 1 to 8
"gstrUser" & ArrayUser(UserCounter) & ArrayTZ(TZCounter) = Me("txtUser" & UserCounter & "TZ" & TZCounter)
Next UserCounter
UserCounter =1
Next TZCounter

Pat:
The client has determined that 8 users will be the max.  They are making calls in the US, so the time zones will not be more than 3.  As it work now, any number of users can have any or all time zone values assigned to them.  Likewise, any number of time zone values can be assigned to any or all users.  Each value in the User/TIme zone field can be unique.

Thanks again!
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
PatHartman

The client has determined that 8 users will be the max.  -- It doesn't matter what the client says is the max.  Once you have more than one, you have many and that means a separate table.

They are making calls in the US, so the time zones will not be more than 3.  -- I guess Hawaii and Alaska don't count.

At least think about doing it the right way.
beyondt

I kind of understand the array example.  I am confuses as to how the global variable, "gstrUserxTZx" would be generated.
My plan is for when the user clicks a "Load Saved" button, the Global variables will be designated the values of the form's respective text boxes.  Doesn't the text strings "gstrUser" and "TZ" need to be included (concatenated) in the For loop?  (i.e., the value of the global variable, "gstrUser8TZ1" would be entered in the form's "txtUser8TZ1" text box.
Thanks for your patience with this.

Pat: Good points.  I'll look at this again and see about modifying the structure.
Jim Dettman (EE MVE)

<<I kind of understand the array example.  I am confuses as to how the global variable, "gstrUserxTZx" would be generated.  >>

You won't generate a global variable; it must be stated explicitly in the code.  Variables can't be generated on the fly like that.

You could use a collection, which is more flexible in that it can create additional items in the collection, but that is also a little more complicated and is over kill for what you need and in the end, is not all that much different from a dynamic array (at least for your purposes, which is to simply store a value).

What you'll do is Dim an array that's as large as you possibly could ever use, or if you don't like that, use a dynamic array that you can REDIM() and it would grow as needed.

But the name of the variable won't change, so there the syntax will look like I posted.

Jim.
beyondt

OK!  It works like a charm!
The only change I made was to change the array from Integer to Variant because some of the fields al blank.
Here's the finished code:
Global Module:
Public CallArray(8, 3) As Variant

Save the values:
For UserCounter = 1 To 8
For TZCounter = 1 To 3
CallArray(UserCounter, TZCounter) = Me("txtUser" & UserCounter & "TZ" & TZCounter)
Next TZCounter
Next UserCounter

Retrieve the values:
For UserCounter = 1 To 8
For TZCounter = 1 To 3
Me("txtUser" & UserCounter & "TZ" & TZCounter) = CallArray(UserCounter, TZCounter)
Next TZCounter
Next UserCounter

Thanks, Jim.  Not only did I get the code to work with your help, but I have a much better understanding of arrays.  I can see many places in my code where they would be useful.

Thanks again!
beyondt

Jim was VERY helpful with clear answers to follow-up questions.  He not only helped with the specific task I asked about, but also helped me understand better the concept of using arrays.
THANKS, JIM!
Jim Dettman (EE MVE)

I would add however that Pat was trying to be helpful in that many times, people struggle with solutions  simply because Access was designed to support relational databases.   When you don't follow relational rules, things can become more difficult.

I'm not saying you can't break the rules now and then, but then you have to have the understanding of what that means.    I'm not saying either that you are in this case.

What your doing is often done with unbound forms, or when you need to process that data  in a specific way.

But when we see someone heading in a direction, it's more or less "due diligence" on our part to make sure they understand where their heading.  Often they don't and they can be saved from a lot of grief later on, but sometimes they do know where their going and just want the answer.

Hard to know though without saying something<g>

Jim.
beyondt