For Each Control in a Form that is a Text or Combo box

[SarahHall]

Hi all,

I currently have a procedure that goes something like:

NullString = ""
    For Each ctl In Me.Detail.Controls
 
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            If ctl.Tag Like "*" And IsNull(ctl.Value) Then
                NullString = NullString & ctl.ControlSource & ", "
            End If
 
        End If
    Next ctl

The only problem is that I know this is inefficient as I don't want to loop through ALL controls - only text or combo boxes. Is there any way I can define controls with those control types as the collection to loop through?

Thanks

 

[KenHigg]

Can you prefix the the control name with txt or lbl, etc and test against this?

 

[SarahHall]

All the controls are already prefixed with a 3 character control type, but I'm not sure how this is any different to ctl.controltype = acTextbox, etc....

What I'm not sure of is how to tell it to only loop through those controls.

 

[KenHigg]

Ah... After looking at all of your code I see you're doing kinda what I was suggesting. I think you've optimized this all you can. Is it slow or just your intuition telling you there may be something else you should do?

 

[stopher]

You could add all the required controls to a collection when you first open the form. Then subsequently you only need to loop through the collection.

I don't have any code to hand as I'm sat on a cold railway station platform in the middel of Germany (not that that should be an excuse). You could search these forums for the keywords: control collection form and maybe also loop. I know I've seen the collection thing mentioned a couple of times.

I've also successfully used the tab control (of all things) as a container. This means you don't need tags. Instead you put all your required controls in a tab form page and them just loop all controls in the tab form page. You can format the tab form so that the user does not know about it. Also you can overlay tab forms so you can have more than one group of controls. If you search my posts with some keywords you've find a fuller explanation.

However, as Ken said, is it really causing a problem to loop the whole form?

Chris

 

[vbaInet]

        If ctl.ControlType = [COLOR=Red]acTextBox[/COLOR] Or ctl.ControlType = [COLOR=Red]acComboBox[/COLOR] Then

The only problem is that I know this is inefficient as I don't want to loop through ALL controls - only text or combo boxes.

You are doing that already in the code line above. I can see that you also specified the section within which the code should run, so that's good. Fyi, the following contruct runs slightly faster than For Each:

For i=0 To Me.Detail.Controls.Count - 1

Is there any way I can define controls with those control types as the collection to loop through?

There isn't. What you've done already is the only way to select specific control types within the Controls collection.

            If ctl.Tag [COLOR=Red]Like[/COLOR] "*" And IsNull(ctl.Value) Then

Why the Like? You can use the equal to operator instead.

Edit: Second time this has happened to me on the site. I don't see the last replies. I didn't notice your reply Chris.

 

[SarahHall]

Thanks for your responses - very much appreciated.

vbaInet: I may be wrong, but I think the 'for each' loop I've given above actually loops though *all* controls, then tests to see whether its either control type. There are quite a lot of controls that doesn't fit into that criteria, therefore there is a lot of resource spent testing controls I'm not interested in. This is why I've decided its inefficient!

Chris: I did have a little look online about creating a custom collection, but I couldn't find anything that I could make sense of. Perhaps I'll try your tab suggestion. Still, I know I'm heading in the right direction, so thanks!!

 

[vbaInet]

Yes, you're right it does loop through all controls. I didn't think you had hundreds of controls.

What Chris is advicing is that you use the Dictionary object. Here's a good tutorial to get you started:

http://www.techbookreport.com/tutorials/vba_dictionary.html

 

[stopher]

What Chris is advicing is that you use the Dictionary object. Here's a good tutorial to get you started:

http://www.techbookreport.com/tutorials/vba_dictionary.html

Actually I was refering to the Collection object but for this job dictionary is just as good I guess. Take a look at post #2 here submitted by Galaxiom. Also further down the thread is where I've given more detail about using tabs as a container.

Chris

 

[vbaInet]

Actually I was refering to the Collection object but for this job dictionary is just as good I guess.

Yea they are pretty much the same. I will try not to assume next time

 

[KenHigg]

Seems the overhead you pick up would offset gain. Unless the form had several hundred controls. And that could indicate a less than optimal design for the form...