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