Looping through subform controls (1 Viewer)

[TallMan]

Good Morning and thank you in advance for any assistance provided -

I have a main form, lets call it FrmMain. In addition, I have a subform on FrmMain that has a tab control on it (frmSubform). What I would like to do is on form load, reference the tab control that is on the subform, loop through the controls/textboxes, lock them and change their backcolor. The code I am attempting is listed below, however, I am getting a "type mismatch error" highlighting this line:banghead::

Set Frm = Forms!frmConsultingMain!FrmConsultingFeeData

Below is my code:

Dim ctl As Control
Dim Frm As Form
Set Frm = Forms!frmmain!FrmSubform
For Each ctl In Frm.Controls
    If (TypeName(ctl) = "Textbox" Or (TypeName(ctl) = "combobox") Or (TypeName(ctl) = "listbox")) Then
        ctl.Value = Null
        ctl.BackColor = vbYellow
        ctl.Locked = True
    End If
Next ctl

Thank you for any help.

 

[boblarson]

A subform is not counted as a form when it is being used as a subform. It is housed within a subform control so you would need to reverence it like this:

Set Frm = Forms!frmConsultingMain.SubformControlNameHere.Form

The part I wrote SubformControlNameHere would be FrmConsultingFeeData IF the subform control that houses/displays that subform on frmConsultingMain is actually NAMED the same as the subform. It isn't always but it can be. But if it isn't the same exact name as the subform, you use that subform control name instead.

The .Form part tells Access you want to refer to the actual form that the subform control is using.

 

[MarkK]

Another principle that seems relevant is, get objects to handle things for themselves. In this case you are running code on the main form that manipulates objects on a subform, but a subform is an independent object. In this case I would write a public sub on the subform and call it from the main form, so move this code to the subform . . .

Public Sub LockControls
   Dim ctl As Control
   For Each ctl In Me.Controls
      If (TypeName(ctl) = "Textbox" Or (TypeName(ctl) = "combobox") Or (TypeName(ctl) = "listbox")) Then
         ctl.Value = Null
         ctl.BackColor = vbYellow
         ctl.Locked = True
      End If
   Next ctl 
End Sub

. . . and then call it from your main form, like . . .

Me.SubformControl.Form.LockControls

. . . so if you ever decide to host that subform on other mainforms (common) then the subform itself knows how to lock its own controls with a single command.

hth

 

[boblarson]

Another principle that seems relevant is, get objects to handle things for themselves. In this case you are running code on the main form that manipulates objects on a subform, but a subform is an independent object. In this case I would write a public sub on the subform and call it from the main form, so move this code to the subform . . .

Public Sub LockControls
   Dim ctl As Control
   For Each ctl In Me.Controls
      If (TypeName(ctl) = "Textbox" Or (TypeName(ctl) = "combobox") Or (TypeName(ctl) = "listbox")) Then
         ctl.Value = Null
         ctl.BackColor = vbYellow
         ctl.Locked = True
      End If
   Next ctl 
End Sub

. . . and then call it from your main form, like . . .

Me.SubformControl.Form.LockControls

. . . so if you ever decide to host that subform on other mainforms (common) then the subform itself knows how to lock its own controls with a single command.

hth

But if the code would be used to do the same thing to other forms, you want to use ONE function and reuse it by just calling it. You don't want the same code on each form.

 

[ChrisO]

There might be something else going on here but by the description it seems this would do:-

Public Sub LockControls(ByRef frm As Form)
    Dim ctl As Control
   
    For Each ctl In frm
        Select Case TypeName(ctl)
            Case "Textbox", "Combobox", "Listbox"
                ctl.Value = Null
                ctl.BackColor = vbYellow
                ctl.Locked = True
                
        End Select
    Next ctl
   
End Sub

And call it from the subform with:-

Private Sub Form_Load()

    LockControls Me
    
End Sub

But now we can see something else we could do.

If the code is always and only called from the Load event then why not make the Controls locked and yellow in design view? Any changes made to those Controls will not be saved and will revert back to locked and yellow the next time the Form is opened.

In other words, if the above is all that is required then we don’t need the code at all.

Chris.

 

[MarkK]

Hey Bob, hey Chris. Happy Friday.
My point is to avoid the case where one class encapsulates a behaviour that belongs to another class. Many classes implementing the same behaviour is a different case again. Too bad VBA doesn't support inheritance.
Mark

 

[ChrisO]

Mark.

Why do you say that it’s “Too bad VBA doesn't support inheritance.”

Without defining what you mean by inheritance it becomes rather ambiguous and if you do define what you mean by inheritance then it becomes just your interpretation.

And I don’t want a heap of Wiki articles to read. They often become a ‘boffin speak feast’. So I will define what I mean as inheritance in VBA.

If a Class can incorporate properties, methods or values from another Class then that is inheritance in VBA. Or, more simply, if a Class can incorporate anything from another Class then that is inheritance in VBA.

Here is the logic I am using to define it that way. In general usage, inherit means to get from, to be left, something from someone else. So inheritance is the ability to get something from somewhere else. In computer jargon in other languages it may be defined differently but it is still amounts to the same thing; inheritance is the thing we get.

So if we take it that VBA does not support inheritance then I am free to define it anyway I like in VBA. I mean the word inheritance is not some sort of reserved word in VBA which would prevent me from using it anyway I like.

Simple example in VBA:-
X = Date()
I could say X is assigned the value of the Date() function, or, I could say that X inherits the value of the Date() function. There is nothing to prevent me from using the word ‘inherits’ here.

Similarly if, in VBA, a Class can get information from another Class then I am free to say that one Class inherits information from another Class. And a VBA Class can get information from another VBA Class. And that means VBA does support inheritance.

So the notion that VBA does not support inheritance really means that VBA may not support the concept of inheritance where inheritance was not defined by the person making the statement.

The same thing goes for Overloading. I was once told that VBA does not support Overloading, but it does. It simply did not support the method of Overloading which the person making that statement had not defined.

----------

This is the reason I bring this up…
There are some people using VBA who have been formally ‘educated’ as programmers. I think that is a fatal flaw with some. Over the years, too many to go into here, and to this day I have seen many people with very precise meanings of words in their head.

While it might seem like a good idea, to know the meaning of words, I think it is a drawback to some people. The meanings can fill a void but it can also produce a rigid response. Very often that rigid response leads to “you can’t do that” especially when the ‘Ritz hits the fan’.

All too often, in my working life, did I have to go to situations where the ‘educated’ people were too educated. They had rigid ‘educated’ ideas but I was a void. (Well not quite but sufficiently not to be swayed by their opinion which hadn’t actually solved the problem.)

So, for what it’s worth, read everything you can but do not simply believe a word of it. Keep the void in your head open for the facts at the time. Hindsight is the only exact science.

Maintain the void…

Chris.

 

[MarkK]

Chris:
There is an OOP language feature called inheritance, and VBA doesn't support it. I would like VBA more if it did.

You are free to call this inheritance . . .

X = Date()

I agree that education can sometimes seem to cause people to lack creativity in problem solving.

 

[ChrisO]

Mark.

The problem I’m speaking of has just been demonstrated. You have posted links to Wiki articles as if to say “Here, Chris, learn all about it.”

Well, I had read those articles many times before but I do not go around needlessly throwing big words into conversations. Quite frankly I find that sort of posting counter productive so I prefer to do it by example:-

http://www.access-programmers.co.uk/forums/showthread.php?t=214369

In the above demo it is quite clear that Classes inherit from other Classes. (Drawing a Flanged Tee, for example, actually invokes 499 Classes.)

From the Wiki article you linked to on inheritance:-
“In classical inheritance where objects are defined by classes, classes can inherit attributes and behavior from pre-existing classes called base classes, superclasses, or parent classes.”

Given the above information, I fail to see how it can be said that VBA does not support inheritance.

If you were to say that VBA does not support ‘your interpretation’ of inheritance then that would be fine. But without stating ‘your interpretation’ it remains ambiguous.

Chris.

 

[MarkK]

Chris,

By default everything I express is my interpretation, and I assume this is true for everybody.

You are free to dispute my interpretation by presenting yours, and I think the resulting discussion has value. Discussion is a marketplace of ideas, and better, clearer, simpler ideas will prevail.

Thanks, as usual, for your contribution.

 

[ChrisO]

Mark.

No, I am not free to dispute your interpretation simply because you have not given one.

And that is one of the problems with making blanket statements like “VBA does not support inheritance.” Clearly it does, at least to some degree. But it certainly could give VBA a bad reputation and, in the process, cause others to not look at the possibilities.

And that’s what I mean by ‘maintain the void’. We want to keep an open mind and not close it with undefined, negative blanket statements.

Chris.