Looping through objects on a form

[jpatrick]

Hi! I found a very old thread that gave me the basis for this but I can't seem to expand it as I need to. ctrl.Value does not seem to be recognized.

My goal is this:
-If it's a text box or combo box, required and blank = change background color
-If it's a check box or option button, required and false = change background color

Any help would be greatly appreciated!

Sub Validate()

Dim ctrl As Control
setColor = RGB(255, 244, 164)

foundrequired = 0

For Each ctrl In Form_Submissions.Controls

If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
    If InStr(1, ctrl.Tag, "*") <> 0 And ctrl.Value = "" Then
    ctrl.BackColor = setColor
    foundrequired = foundrequired + 1
    End If
End If

If ctrl.ControlType = acCheckBox Or ctrl.ControlType = acOptionButton Then
    If InStr(1, ctrl.Tag, "*") <> 0 And ctrl.Value = False Then
    ctrl.BackColor = setColor
    foundrequired = foundrequired + 1
    End If
End If

Next

If foundrequired > 0 Then
MsgBox "Please fill in the required fields before submitting.", vbCritical, "HOTEL CHECKS"
Exit Sub
End If

'continue code if passes validation

End Sub

 

[Cronk]

If you had checked the value of the particular controls as you were looping through, you'd probable see the value of a blank control is Null.

Use
Nz(ctrl.Value, vbNullString) = "" for a text box and combo
or
Nz(ctrl.Value) = 0 for check box

Also
(1) You don't need to use Value. Debug.print ctrl, ctrl.value return the same
(2) There is no re-setting the backcolor if on subsequent data checks, the data has been inserted
(3) In an option group, there is normally at least one one optionButton which is false. Test the value of acOptionGroup
(4) You can't set the backcolor of a check box. Either put it on top of another label that you can change color, or change the color of the label associated with the check box

 

[pbaldy]

Well for starters, you're only testing for a zero length string, not Null. Try

If InStr(1, ctrl.Tag, "*") <> 0 And Len(ctrl.Value & vbNullString) = 0 Then

 

[jpatrick]

Thanks! That got me much closer, both of your code works as desired.

I've associated each checkbox or optionbutton to a label by giving them matching numbers for the last 3 digits of their names.

It's all working perfectly now except I also need it to loop through the subforms. Could you let me know what I need to modify for that?

Sub Validate()

Dim ctrl As Control
Dim l As Control

setColor = RGB(255, 0, 0)

foundrequired = 0

For Each ctrl In Form_Submissions.Controls

    If InStr(1, ctrl.Tag, "*") <> 0 Then
        
        If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
            If Nz(ctrl.Value, vbNullString) = "" Then
            ctrl.BackColor = setColor
            foundrequired = foundrequired + 1
            Else
            ctrl.BackColor = 16777215
            End If
        End If
    
        If ctrl.ControlType = acCheckBox Or ctrl.ControlType = acOptionButton Then
            If Nz(ctrl.Value) = 0 Then
                For Each l In Form_Submissions.Controls
                    If l.ControlType = acLabel And Right(l.Name, 3) = Right(ctrl.Name, 3) Then
                    l.BackColor = setColor
                    foundrequired = foundrequired + 1
                    End If
                Next l
            Else
                For Each l In Form_Submissions.Controls
                    If l.ControlType = acLabel And Right(l.Name, 3) = Right(ctrl.Name, 3) Then
                    l.BackColor = 16777215
                    End If
                Next l
            End If
        End If
        
    End If

Next ctrl

If foundrequired > 0 Then
MsgBox "Please fill in the required fields before submitting.", vbCritical, "HOTEL CHECKS"
Exit Sub
End If

'continue code if passes validation

End Sub

 

[Cronk]

For Each ctrl In Form_Submissions.YourSubFormControlName.Form.Controls

 

[jpatrick]

Thanks, Cronk! I'm getting 'run time error 438/object doesn't support this property or method' on the two lines indicated below. I have the subform in datasheet view. Any idea why? Thanks again!

rt_foundrequired = 0

    For Each ctrl2 In Form_Submissions.[Room Types subform].Form.Controls
        
'error here        
If ctrl2.ControlType = acTextBox And Nz(ctrl2.Value, vbNullString) = "" Then
            rt_foundrequired = rt_foundrequired + 1
        End If
        
'error here        
If InStr(1, ctrl2.Tag, "*") <> 0 And ctrl2.ControlType = acCheckBox And Nz(ctrl2.Value) = 0 Then
            rt_foundrequired = rt_foundrequired + 1
        End If
    
    Next ctrl2

 

[The_Doc_Man]

The problem is that some controls don't have a value property. Stepping through all controls, you have to be sure that you test .ControlType first to assure that you have the right kind of control to expose the value of it. Then there is the fact that the control could be null, so you need an NZ([control], 0) function or an explicit IsNull test before mucking about with the .Value property.

Sometimes I do something like this (when generality is needed and speed isn't):

For Each ctX in Me.Controls

    on error resume ctX_No_Value
    ctX_Val = CStr(ctX.Properties("Value"))
    GoTo ctX_Got_Value
ctX_NoValue:
    ctx_Val = "{nil}"        'or pick another 
    Resume ctX_Got_Value
ctx_Got_Value:
....
Next ctX

You pick whatever you want for the value to return when the object does not support the property. If the object doesn't have a value property, you take the trap, drop in the special-case value, and go on. Otherwise, you pick up the value and make a string of it. (Just a simplified example, of course)

 

[jpatrick]

Thanks, Doc! I didn't really understand how to make that work but it pointed me in the right direction and I was able to figure something out.

So now I've learned looping through the subform is not sufficient, I need to loop through every record of the subform as well. I'll search for topics on this but if anyone can post a resolution here it would be greatly appreciated!