Full Version: Disabling Controls in a Collection
UtterAccess Forums > Microsoft® Access > Access Forms
Hello all (again).
Not another challenge.
I have a form that has several collections. Each collection contains a set of controls on the form that have to be enabled or disabled based on the state of the form or the value of a control. I'm trying to write a sub that will refer to a control in a collection based on a string value passed to the sub.
Example, I have 6 collections. If I passed the strings "14" and "01" to the sub, then I want all controls in Collection1 to be disabled and all controls in Collection4 to be enabled. I've tried several different methods, to include an array of collection names, array of objects, etc. with no luck. Because the names of these collections are strings (in the arrays), I can't use the 'For Each ctl In' method of enabling/disabling. I can easily parse out which collection I want to reference using the strings above, but have not been able to actually reference them in VBA.
I have also tried using a Select...Case, using a different collection name for each case, but because the collections are rather numerous, this can get ungainly. A For...Next Loop would be ideal, but again, the reference to the appropriate collection is a problem.
I haven't been able to find the solution searching the forums here. If anyone's got any great ideas, I'd be happy to give them a try. I'm pretty familiar with programming with VBA, so even a hint in the general direction to go in will be useful.
If you are not using the Tag property or even if you are you could put your string (ie 14 and 01) in the tag and use the For each loop to inspect the tag and turn the ones that you want on or off. If you are using the tag it could be split into an array and then inspected in the For Each statement.
Hope this helps
There are actually several threads here that refer to looping thru the controls on a form. Here is one of them.
nother possibility is to use a Tab control and put each collection on a tab then just hide or display the tab.
However, I would like to know more about these "collections" and what they represent. Its possible that you may need to normalize your database.
Hope this helps,
I hadn't thought of using the Tag before. I know it will work, however I'm concerned that it would take too long to loop through every control checking it's Tag. There are a lot of controls on the form and the enabled properties will be changing constantly.
Thanks for the idea. If I can't figure out how to reference the collection, I'll probably use this method. The next question becomes, how do I reference a variable in a module by string name (if it's possible). For example, in a form, you can use Me.Controls("..."). Is there a way to do the same with modules? Example. basMyModule.---("strVariableName"). If so, what would go in place of the ---, the equivalent to "Controls" in the Me.Controls("...") example.
hese collections are collections of controls, created when the form is opened. For example, one collection (let's call it colCollection1) contains a group of controls (ctlContol1, ctlControl2, etc.). Each of these collections correspond to a group of controls on the form, say the group of controls where I enter employee info, or the group of controls that represent employee qualifications. Looping through each of these collections allows me to enable or disable certain parts of the form while others remain in their current state.
If a certain value is selected in a listbox on the form, I would like to have colEmployeeInfo be enabled, but colQualData be disabled. I know I can do this with a For...Next Loop, however there are many such collections of controls and I'm looking to create code that will allow me to reference which collection I want to disable with a number. Learning how to create this will probably take longer that just writing the For...Next Loop for each of the collections, but I want to be able to paste this code into any project I create.
o answer you question about normalization. To the best of my limited knowledge, my db is normalized. The data on this form is the result of an updateable query combining data from several tables.
What about having a master collection that holds all of your collections. For example, here's a little something I just made up
Dim col1 As New Collection
Dim col2 As New Collection
Dim colMasterCollection As New Collection
Dim lngCounter As Long
ol1.Add Me.Text0, "1"
col1.Add Me.Label1, "2"
col2.Add Me.Text2, "1"
col2.Add Me.Label3, "2"
colMasterCollection.Add col1, "14"
colMasterCollection.Add col2, "01"
For lngCounter = 1 To colMasterCollection("14").Count
    colMasterCollection("14").Item(lngCounter).Visible = False
Next lngCounter

The collection colMasterCollection holds each individual collection with a reference string such as "14" or "01" as you mentioned. The string references the individual collections. So, in this example I just hard coded to set the visible property for all controls in collection "14" to false. But, obviously, you could have a sub set up where that is an argument.
Does that help at all? frown.gif
OoooK. If I understand this. You are generating forms on the fly, by creating collections of controls that you can turn on and off and then assigning labels to them so the user knows what to enter in each. I'm assuming you aren't using bound forms then.

Frankly, while I understand the concept and even why you might want to do that, its over my head. I'll just go on creating specific forms to deal with specific sets of data.
Don't think you can reference the variable in a module because the variable is only available while the module is running or as they say it's Scope is the module itself. You could pass the variable back to the form that you are using like a function does.
nother way to handle your controls is through the name of the control. If you name the controls in a collection the same with an index number you could use a For Next loop on those controlls. For Example:
Collection 1
Col11 'first control in collection 1
Col12 'Second control in collection 1
Col13 'third control in collection 1
Collection 2
Col21 'first control in collection 2
Col22 'Second control in collection 2
Col23 'third control in collection 2
Then you could just pass the Collection name like below
Call MyDisableSub("Col2", 3, True)
Then in the sub
Public Sub MyDisableSub(DCtl As Control, TCtl As Integer, OnOff As Boolean)
Dim f
Dim i as integer
Set f = Forms!MyForm
For i = 1 to TCtl
f("DCtl" & i).Enabled = OnOff
Next i
End Sub
This would enable you to loop through just the controls that you wanted to. However you would probably have to reprogram some of the controls because of the name change
Hope this helps
The form is not generated on the fly, I just do not necessarily want the user to always be able to enter information into the bound controls. A lot of this depends on permissions on what data is currently being viewed.
For the others, give me a bit to try those inputs and I'll get right back to you. Thanks for the help.
OK I misunderstood. If it is a bound form but bound to an updateable query, I come back to the question of normalization. Grouping fields like you describe makes me wonder, but I'd have to see more of the structure to tell for sure. You stated one collection was employee data and another was "QualData" but without knowing what that is or how it relates to an employee, I can't judge.
If you have a main/subform situation, then maybe you could just disable the subforms. Or I go back to my idea of using a Tab Control.

It worked. Here's the code I used:
Private Sub Form_Status (strCollections As String, _
nbsp;                        strStatus As String)
Dim i As Integer
Dim j As Integer
For i = 1 To Len(strCollections)
    For j = 1 To colPilotCollections(CInt(Mid(strCollections, i, 1))).Count
        colPilotCollections(CInt(Mid(strCollections, i, 1))).Item(j).Enabled = Mid(strStatus, i, 1)
    Next j
Next i

Creating the Master Collection is what did the trick. However, I was able to reference the actual position of the collection in the master collection versus the name of the collection. For those wondering, what this allows me to do is have a group of collections, each consisting of several controls, and disable them with the simple passage of a string to a sub. For example, if I were to pass ("134","001"), it would disable all the controls in collections 1 and 3, and enable all controls in collection 4. I can probably add a couple more variables to make the code more efficient. But this is exactly what I was looking for.


HAs a general example of what I using this for... My form has controls bound to fields from an updateable query. However, some of the fields are one time entry only. Therefore, if no data is present in these bound controls, I want them enabled to allow entry, however if data already exists, then no entry is allowed. Additionally, if this user has a restricted permission, I may want them to be able to view records, but only be able to edit certain parts of them. Finally, this allows me to enable, disable certain command buttons based on which step the user is on in the data entry process (i.e. cmdSave, cmdCancel, cmdAdd, etc).

Again, thanks for all the help guys. It's working great.
Okay, great, glad the master collection (i.e. collection of collections) worked out. Remember, of course, that collections are not as efficient as arrays. However, I'm guessing based on what you're doing that you're just using the collections every so often and not in some massively long loop or whatever. If you can determine the position in the collection exactly rather than having to use its string index (didn't realize until I saw your sub what you meant by the "14" and "01"), seems like you could use an array (or an array of arrays) just as easily, but, hey, if it's working and there is no efficiency issue, why fix what's not broken?! frown.gif
ever tried using arrays of controls before, only collections. I'll probably look into that for the next project. Thanks!
Something like
im ctlArray(1 to 4) as Control
Set ctlArray(1) = Me.Text0
etc. . .
Anyway, glad to help frown.gif
I have a form with several Frames that I want to hide unless the user clicks a button, I am trying to use the code above, I named each of my controls inside the frame Col1, Col2, Col3, etc. I added the code Call MyDisableSub("Col1", 3, True) to a button on my form. I changed the line f("DCtl" & i).Enabled = OnOff
to f("DCtl" & i).Visible = False
keep getting the error type mismatch when I click the button, highlighting "Col1" in the line :
Call MyDisableSub("Col1", 3, True)
Any Ideas.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.