The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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...
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...
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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?
Thanks for the comments...
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:
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!
Yes and no. It would look like this:
Dim MyArray(8, 3) As Integer ' Where (user, time zone)
For UserCounter = 1 to 8
For TZCounter 1 to 3
MyArray(UserCounter, TZCounter) = Me("txtUser" & UserCounter & "TZ" & TZCounter)
Next TZCounter
Next UserCounter
This is a "Fixed" array in terms of size. It's also possible to declare a dynamic array and re-size it as needed, but start with this.
Jim.
Dim MyArray(8, 3) As Integer ' Where (user, time zone)
For UserCounter = 1 to 8
For TZCounter 1 to 3
MyArray(UserCounter, TZCounter) = Me("txtUser" & UserCounter & "TZ" & TZCounter)
Next TZCounter
Next UserCounter
This is a "Fixed" array in terms of size. It's also possible to declare a dynamic array and re-size it as needed, but start with this.
Jim.
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
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.
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.
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.
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.
<<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.
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.
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!
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!
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!
THANKS, JIM!
Your quite welcome.
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.
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.
I certainly hope that I did not give the impression that Pat's comments were not appreciated. I have great respect for anyone offering input on any question I post. I have witnessed over the many years I have used Experts Exchange that this is one of the few support blog sites where people go out of their way to help and educate others, without the typical personal comments and judgments found on most sites. And in that spirit, Pat's comments are very spot-on with relation to my post. In my relatively simple question, both Pat and Jim recognized and understood my level of expertise, and offered advise that not only addressed my specific question, but also pointed out potential issues that are common pitfalls when designing and developing an Access database application. And again, for that, I am extremely grateful to both Jim and Pat for their input.
Regarding the database I am working on. I first rolled it out to the client in 2005. It is their telemarketing database on which their company relies. Over the years, many, many new features have been requested, and I am pleased to say that all have been added and are working as designed.
Since VBA programming is far from my primary area of expertise or revenue, this particular database has been a fantastic learning experience for me. I constantly pick up bits and pieces of "proper programing techniques" and try to implement them where and when I can. Since this database is a working, production database on which my client's revenue depends (and which has countless features that I never would have guessed I could have programed to work as well at they do), I am extremely cautious about making major changes in the fundamental structure of the database. Doing so would risk unforeseen problems (as always seems to be the case with each new feature that is added...until resolved), and the time and expense for both the client and me would be too great.
Again, many thanks to both Jim and Pat for their input on this post. They not only answered my question, but expanded my level of understanding VBA programing and database development.
(Sorry for the long-winded post...)
Regarding the database I am working on. I first rolled it out to the client in 2005. It is their telemarketing database on which their company relies. Over the years, many, many new features have been requested, and I am pleased to say that all have been added and are working as designed.
Since VBA programming is far from my primary area of expertise or revenue, this particular database has been a fantastic learning experience for me. I constantly pick up bits and pieces of "proper programing techniques" and try to implement them where and when I can. Since this database is a working, production database on which my client's revenue depends (and which has countless features that I never would have guessed I could have programed to work as well at they do), I am extremely cautious about making major changes in the fundamental structure of the database. Doing so would risk unforeseen problems (as always seems to be the case with each new feature that is added...until resolved), and the time and expense for both the client and me would be too great.
Again, many thanks to both Jim and Pat for their input on this post. They not only answered my question, but expanded my level of understanding VBA programing and database development.
(Sorry for the long-winded post...)
Jim did an excellent job of helping you solve the technological hurdle caused by an unnormalized design. I understand your reluctance to go back to the design board but frequently, that is actually the best and most cost effective approach. You are good to go for now but when designing new apps, keep my advice in mind. When you have more than one of something, you have many, and many requires a second table. It doesn't matter how many and the only reason for finding out how many is because you need to do space planning and need to know approximately how many rows will be stored so you can allocate enough disk space. Don't fall into the trap of making a limit for something that shouldn't have a limit. All it does is add complexity and cause programming when none should be required. Relational databases are not spreadsheets.
Happy coding:)
Happy coding:)
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.
-
Microsoft ApplicationsCertification: IC3 GS4 Internet Core Competency Global Standard 4… 135 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Jim.