For Each...Next

[yeatmanj]

Good day all.

I need help understanding For Each...Next statements. I have read a lot of help files and reference material, but still do not have a grasp of how to handle various objects. Most examples that I review show the syntax and explain what goes where, but I can't seem to find any complete examples that explain how to handle objects.

Two example in my work come to mind. A form with a series of check boxes that control a report output. I would like to loop through the controls on the form to determine what items a user wants in the report. The result would be a custom sql string based on the boxes the user checked.

The second example is similar because it is a table full of blnYes/No items that should be looped through to build a custom string.

I think that I understand roughly how to reference the object in the form, but not in the table. It is very confusing because most info that I read seems to expect you to know how to reference the different objects and apparently I don't.

Any references or help would be greatly appreciated.

 

[Guus2005]

Use for each ctl in frm.controls and check out the typeof statement to determine the type of control.

 

[yeatmanj]

Guess I should elaborate a little more. What if there are fields on the form that I want to exclude?

One of my biggest issues with understanding this is how to declare the variables. Or even when to declare variables.

 

[OldGit]

Perhaps this may help - on a form add a checkbox - name it chkbox1.
add a command button - in the click event paste this:-
Dim ctl As Control
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acLabel
Case acTextBox
Case acCheckBox

If .Name = "chkbox1" Then
If .Value = True Then
MsgBox "chkbox is true"
Else
MsgBox "chkbox not true"
End If
End If
Case Else
End Select
End With
Next ctl
Run the form - click the checkbox and button to see results - could form the basis of an answer to part of your question.

 

[Guus2005]

You can also use the TAG property of the control to identify the control:

Public Sub ClearFields(frm As Form, strTag As String, Optional blnLocked As Boolean = False, Optional blnBound As Boolean = False)
'Empty fields on the form (if unbound) using the tag property and set the Locked property to true or false.

    Dim intX          As Integer
    Dim intMaxControl As Integer
    Dim ctl           As Control
    
    If Not IsLoaded(frm.Name) Then
        Exit Sub
    End If
    
    For Each ctl In frm.Controls
        If InStr(1, ctl.Tag, strTag) > 0 Then
            Select Case ctl.ControlType
                ' Input Controls
                Case acTextBox
                    If Not blnBound Then ctl.Value = ""
                    ctl.Locked = blnLocked
                Case acComboBox
                    If Not blnBound Then ctl.Value = ""
                    ctl.Locked = blnLocked
                Case acCheckBox
                    If Not blnBound Then ctl.Value = ""
                    ctl.Locked = blnLocked
                Case Else 'Skip all other cobtrols
            End Select
        End If
    Next ctl

End Sub

This is what i usually do