Unanswered: Form Fields referenced by Strings in VB
I am a novice programmer and have run into a stopping block. Tried before but was was not clear to forum so here it goes again in simple mode.
What is the naming convention to take a form field and use a string to reference that field. Reason is I have 100 yes no fields and want to use a for/next to reference each field on form to change its value to either true or false. See below:
Rem [Field 1]= True, (yes,no) field on form and 100 of these named Field 1-100
Dim FieldNumber as Integer
Dim FieldName as String
UserInput = Text box 1 on form
for x =1 to UserInput
FieldNumber=x+1; steps the FieldNumber Integer by 1
FieldName = "[Field" & FieldNumber & "]" ; Referenced the field by a string, so output looks like a field ie [Field 3] or [Field 5] etc.
Rem now comes the trick. How do I use "FieldName" to change the value of that field? I would think below would work but it does not probably because it is a string. It just takes the value of "False" and assigns it to "FieldName". Tried using Fields(FieldName) and does not work either.
FieldName = False; Want to change all the form fields to false
FieldNumber = FieldNumber + 1; adds 1 to the fieldnumber to increment by 1 in all fields 1-UserInput. This number will change this for even or odd number field names which is why I need the For/Next.
This is going to be a simple solution. One of these thing that I will say "Ahh" after you come up the answer. Thanks
To reference a Form's control you will need to Declare your variable(s) as Control. For example:
Dim myVariable As Control
Then you will need to set the variable(s) to whatever Control you want. For example:
Set myVariable = Me.MyFormsControlName
In your case (by the looks of it) you want to enumerate through all your CheckBoxes on Form and change it's value dependant upon a specific condition or conditions. To do this you can try the following method:
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If Ctrl.ControlType = acCheckbox Then
If Whatever Condition you want Then
' do the task.......
The code above will inspect ALL CheckBoxes located on your Form. This may not be what you want. You may want to be a little more particular as towards which CheckBoxes to actually process. You could specify a limited name within your If/Then conditional statement such as:
If Ctrl.Name = "myFormsControlName" Then
OR....you can be even more explicit. Rather that ripping through all the Controls on your Form you could provide the Control name within your For/Next loop. This is particularly helpfull if the controls in question either start or end with a number. For Example: 1_myControlName, 2_myControlName, etc. With this type of naming, it becomes easy to seek out and process those specific Controls.
Dim Ctrl As Control
Dim i As Integer
For i = 1 To 120
If Me.Controls(i & "_myControlName").Value = True Then
Me.Controls(i & "_myControlName").Value = False
The above code assumes you know how many explicit Controls (CheckBoxes in this case) there are on your Form to fashion (120 of them) and the first Control to process starts with the number '1'.
There are many methods to attack this sort of thing....I hope these few have helped a little.