How do I loop through chosen types of controls on a form?

[Seth Schrock]

I'm trying to setup a class module that will allow me to pass the form name and the types of controls that I want tested (textboxes, comboboxes, etc.) and have it test if they are populated. My thinking is that I would have a parameter that would be something like TxtCboLstChkOpt and if I wanted to only check Textboxes and Checkboxes I would pass it 10010 as flags for the class to know which types to check. My problem is that I don't know how to only run the loop once and select those types that have a 1 in their section. Normally I would have this hard coded into a Select Case statement like the following:

For Each ctl in Controls
    Select Case ctl.Type
        Case acTextBox, acCheckBox
            'Perform my checks
        
    End Select
Next ctl

I don't want to have to test for the 32 different possible combinations of flags nor to I want to test each position and loop through all the controls once for each type of control for a total of five loops if all flags are checked.

I'm guessing that a bitwise comparison is going to be part of the answer, but I'm not very good with that. I wish that I could change the third line to be

Case If txt = True Then acTextBox, If cbo = True then acComboBox, etc.

so that it would add the control type to the case statement criteria. I'm just out of ideas. Hopefully I have worded my question clearly so you know what I'm looking for.

[NeoPa]

Unfortunately, the values of acTextBox, acComboBox, acListBox, etc are not powers of two, so cannot be used in the way you suspected. If you want to pass multiple values to a procedure or class method then you will need to use either an array or a string with value lists embedded within it.

PS. The control property you would want to check would be ControlType rather than Type.

[Rabbit]

Are you using flags or checkboxes to determine what controls to check? Because the second block of code makes it sound like you want to use checkboxes.

Either way, you don't have to use select, I would just use 5 if statements for the 5 control types you want to check.

For Each ctl in Controls
    If txt = True And ctl.Type = acTextBox Then
        'Perform checks for textbox
    Else If etc.
    End If
Next ctl

[Seth Schrock]

@Rabbit My plan for the second code block was to set boolean variable (txt, cbo) based on the flags. So your suggestion would then loop through all of the controls five times (once for each control type)?

@NeoPa I wasn't thinking that the values of acTextBox, acComboBox, etc were powers of two, but that I could use my flags to then choose which type of control to add to the list. You suggest using an array. Would it be something that I could add control types to the array based on my flag system and then have it loop through the list of types to see if it is supposed to check it (instead of the select case statement)? Using the example in the OP, my flag is set to 10010. I test the first character (textboxes) and it is set to true, so I add it to the array. I test the second character (comboboxes) and it is set to false so I don't add it to the array. I continue on through each type and I end up with Textboxes and Checkboxes in my array of types. So then inside each loop through the controls, I would then loop through the array to see if I'm supposed to check it. Would something like that work?

I'll fix the .Type to .ControlType.

[Rabbit]

No, it's just one loop. It's like your switch, just using if statements.

[Seth Schrock]

I got it. I used your solution Rabbit, except that instead of using the ElseIF, I just made the criteria be

If (bTxt = True And ctl.ControlType = acTextBox) Or (bCbo = True And ctl.ControlType = acComboBox) Or _
       (bLst = True And ctl.ControlType = acListBox) Or (bChk = True And ctl.ControlType = acCheckBox) Or _
       (bOpt = True And ctl.ControlType = acOptionGroup) Then

So my final code that works is

Public Sub CheckControls(frm As String, TxtCboLstChkOpt As String, Optional tag As String = "req")
Dim bTxt As Boolean
Dim bCbo As Boolean
Dim bLst As Boolean
Dim bChk As Boolean
Dim bOpt As Boolean

'Check which control types to check
bTxt = (Mid(TxtCboLstChkOpt, 1, 1) = 1)
bCbo = (Mid(TxtCboLstChkOpt, 2, 1) = 1)
bLst = (Mid(TxtCboLstChkOpt, 3, 1) = 1)
bChk = (Mid(TxtCboLstChkOpt, 4, 1) = 1)
bOpt = (Mid(TxtCboLstChkOpt, 5, 1) = 1)

Dim ctl As Control

For Each ctl In Forms(frm).Controls
    If (bTxt = True And ctl.ControlType = acTextBox) Or (bCbo = True And ctl.ControlType = acComboBox) Or _
       (bLst = True And ctl.ControlType = acListBox) Or (bChk = True And ctl.ControlType = acCheckBox) Or _
       (bOpt = True And ctl.ControlType = acOptionGroup) Then
        
        If Forms(frm).Controls(ctl.Name).tag = tag Then
            
            If Forms(frm).Controls(ctl.Name).value & "" <> "" Then
                Forms(frm).Controls(ctl.Name).Controls.Item(0).ForeColor = vbBlack
            Else
                Forms(frm).Controls(ctl.Name).Controls.Item(0).ForeColor = vbRed
                blnCompleted = False
            End If
            
        End If
    End If

Next ctl

End Sub

Thanks for all your help.

**EDIT: For those who would try to copy this code, the variable blnCompleted referenced in line 28 would need to be declared. I don't have it declared in this code because it was declared at the module level instead of at the procedure level.

[ADezii]

I was just running out the door and did not have time to read the entire Thread, so please forgive me if I am off course. Why not create a Public Function and pass to it a Reference to the Current Form as well as a number of Variable Control Types to be analyzed?

Public Function fEvalControls(frm As Access.Form, ParamArray varControls())
Dim varControl As Variant

For Each varControl In varControls
  Debug.Print frm.Name, varControl
    Select Case varControl
      Case acListBox
        'Analyze for each specific Control Type on Form frm
      Case acComboBox
      Case acTestBox
      Case acCheckBox
      Case acSubform
      Case acTabCtl
      Case Else
    End Select
Next
End Function

'Pass to Function a Reference to the Form as well as any Control
'Types that you wish to analyze
Call fEvalControls(Me, acTextBox, acListBox, acComboBox)

'Form Name & Values of Control Type Intrinsic Constants
Form1          109 
Form1          110 
Form1          111

[Seth Schrock]

Unless I'm misunderstandin g you, that is basically what I'm doing. However, your code will always check for the six control types that you have listed even if you didn't call for it to. Whereas mine checks to see if that control type is supposed to be checked before doing anything with it. Eventually, I'm going to expand the capabilities of the procedure to include which required controls were empty in the message box that will be shown the user, but that can wait as it isn't needed for my current project.

My main goal was to make it so that I could just copy the module to any of my programs and have it work instead of having to create it for each form. Yours would allow me to do the same thing I believe, but, from my understanding of the code, yours doesn't quite do what I was wanting. Also, the same check is performed for each control type selected, so I don't need to have each control type be a separate case.

[ADezii]

Guess I was off course (LOL)! You can always Redimension an Array of checked Control Types and Pass that to the Function instead, this will eliminate the checking of ALL Control Types, or those specified in ParamArray().

[NeoPa]

I think maybe I wasn't following the question as clearly as I could have either :-(

If you want to specify something (like a procedure parameter), so it is entirely under your own control, then a bit flag value should work fine for you. IE. A value of 18 (10010 binary) would indicate that two of the five control types (The two you allocate to those positions) should be checked for or processed.

Personally, when multiple options need to be coded, I prefer the Select Case statement over multiple If Then Elses as it's neater code, even though it may not necessarily produce the most efficient compiled code. That said, that's a matter of style rather than the ability of the code to work correctly.

With the 5-bit value it is certainly possible to use it as separate bit-flags. To code that you can use something like the following :

Select Case True
Case (intX And &H10) = &H10
...
Case (intX And &H8) = &H8
...
Case (intX And &H4) = &H4
...
Case (intX And &H2) = &H2
...
Case (intX And &H1) = &H1
...
End Select

This illustration uses hex literal values, but in real code I would normally use constants defined to represent these. intX is the value you're testing of course.

[Seth Schrock]

Horray! I think that I have finally figured out bitwise comparison and it usage thanks to your last post NeoPa. I think that I will stick with my single If/Then statement as it puts all the criteria in one condition making it so that I don't have to write my control checking code multiple times (or the procedure calling code if I set the control checking code in its own procedure). However, I am going to use bitwise comparison to set my boolean variables. I just setup and enum to hold the values of the control types as I have ordered them.

Public Enum cTypes
    txt = 16
    cbo = 8
    lst = 4
    chk = 2
    opt = 1
End Enum

I then have my procedure using bitwise comparison to set my variables.

Public Sub CheckControls(frm As String, TxtCboLstChkOpt As Long, Optional tag As String = "req")
Dim bTxt As Boolean
Dim bCbo As Boolean
Dim bLst As Boolean
Dim bChk As Boolean
Dim bOpt As Boolean
Dim ctl As Control


'Check which control types to check
bTxt = (TxtCboLstChkOpt And txt)
bCbo = (TxtCboLstChkOpt And cbo)
bLst = (TxtCboLstChkOpt And lst)
bChk = (TxtCboLstChkOpt And chk)
bOpt = (TxtCboLstChkOpt And opt)


'Mark controls that are required and empty
 
For Each ctl In Forms(frm).Controls
    If (bTxt = True And ctl.ControlType = acTextBox) Or (bCbo = True And ctl.ControlType = acComboBox) Or _
       (bLst = True And ctl.ControlType = acListBox) Or (bChk = True And ctl.ControlType = acCheckBox) Or _
       (bOpt = True And ctl.ControlType = acOptionGroup) Then
        
        With Forms(frm).Controls(ctl.Name)
        
            If .tag = tag Then
                
                '.Controls.Item(0) refers to the attached label
                If .value & "" <> "" Then
                    .Controls.Item(0).ForeColor = okColor
                Else
                    .Controls.Item(0).ForeColor = errColor
                    blnCompleted = False
                End If
            
            End If
           
        End With
        
    End If
 
Next ctl
 
End Sub

and to call my code, I just add the enumed values together.

Dim verifyFields As clsRequired
Set verifyFields = New clsRequired
verifyFields.CheckControls Me.Name, txt + chk

The only issue that I'm having now is that it didn't like it when I put the enum in the class module, so I had to put it in a regular module. This kind of defeats the idea of having a self contained class module that I can just copy and paste between projects and it works. Now I have to remember to go copy the enum as well. I'll continue looking online for a solution to this and start a new thread here if I can't find anything.

Thanks NeoPa.

[NeoPa]

Always a pleasure Seth :-)

However - bTxt will never be True. bTxt may trigger the True side of an If or IIf(), but that is not the same as bTxt = True (because True is a specific numeric value of -1 - or Hex FFFF). It is necessary to test this correctly when the values returned are not exactly equal to True.

You may get away with it as Access, in some newer versions, may assign True whenever the value to be assigned is non zero, but this is an area where care is necessary - even if that is simply to make the code more clearly readable. I hope this makes sense.

[Seth Schrock]

Once again you have caught me with that. But I think that I understand it more this time having seen that, say 18 AND 16 = 16 and not just a "True". I guess I was assuming that since bTxt was a boolean variable that it would take on the value of "True" when assigned anything other than 0. However, I do like to code "right" and not necessarily just what works (which it does work as I've tested it).

[NeoPa]

I guess I was assuming that since bTxt was a boolean variable that it would take on the value of "True" when assigned anything other than 0.

It seems you're quite right on that score Seth. Boolean variables only allow boolean values it seems.

IE.

If 18 = True Then FAILS

bTxt = 18
If bTxt = True Then SUCCEEDS