Solved Perform several loops in one (1 Viewer)

[rudy65]

Dear Forum,

Is it possible to perform multiple (nested?) loops in one.
For example:

        For Each ctl In Me.Controls
            For i = 1 To 5
                If ctl.ControlType = 106 Then
                    With ctl.Controls(0)
                        If ctl Then
                            If ctl.Name = "P" & i Then
                                'my stuff... Dlookup something in table
                            End If
                        End If
                    End With
                End If
            Next i
        Next ctl

Thing is, I have three sets of data I want to consider and a couple of single lookups.
So I have for example:
for i =1 to 5
for i = 11 to 16
for i =21 to 24
and i = 36 and i = 42

Is it possible to perform all these loops in one?

Thank you,
Rudy

 

[CJ_London]

you can include loops within loops but not clear what you are trying to achieve - you do need to get the order right. For example this does not look right to me

For Each ctl In Me.Controls
            For i = 1 To 5
                If ctl.ControlType = 106 Then
                    With ctl.Controls(0)

it may be you need to look at using a case statement

 

[MajP]

Public Sub InOne()
  Dim i As Integer
  For i = 1 To 42
    Select Case i
    Case 1 To 5
      Debug.Print "do something 1 to 5 " & i
    Case 11 To 16
      Debug.Print "do somethin 11 to 16 " & i
    Case 21 To 24
      Debug.Print "do something 21 to 24 " & i
    Case 36
      Debug.Print "do something 36 " & i
    Case 42
      Debug.Print "do something 42 " & i
    End Select
  Next i
End Sub

 

[The_Doc_Man]

From an efficiency standpoint, your WITH and IF CTL statements appear somewhat odd. I suppose you could rearrange so that your name test is immediately next to the FOR I= loop. The IF CTL presumes something that might not be true. If that is a loop over all controls, there are controls that don't have values and thus your IF CTL would get an error like "property not found" or something like that.

You CAN do everything in a single loop by nesting in a case statement rather than using the IF construct.

For i = 1 to 42
    Select Case i
        Case 1 to 5, 11 to 16, 21 to 24, 36, 42
             ctl = Me.Controls("P" & i)
            If ctl.ControlType = 106 Then
                ....
            End If
        Case Else
    End Select
Next I

EDIT: I see that MajP beat me to the CASE suggestion.

Your WITH statement makes no sense in your original loop because the only thing you can do from there would involve properties of the control.

 

[MajP]

My assumption is that you do something different for each case. Docman assumes you want to do the same thing for each case thus the difference

 

[XPS35]

This is more or less a repost of https://www.helpmij.nl/forum/showthread.php/965998-Checkbox-true-op-basis-van-waarde-in-tabel
The real problem is bad table design.

 

[rudy65]

Thank you all for your help,
@ XPS35 :
Although it is my post on Helpmij, this is certainly not a repost.
The post in the link had more to do with the maximum permitted number of controls in a table or query. Nothing to do with (bad) table design.

@ MajP and The_Doc_Man: The solution of the_doc_man is exactly what I am looking for.
Works like a charm.

Thank you all very much.

Rudy.

 

[Pat Hartman]

Of course you can nest loops but are the loops nested? Two experts thought not and gave you the Case statement solution. So, please clarify.

 

[The_Doc_Man]

had more to do with the maximum permitted number of controls in a table or query.

Look up "Access Specifications" - and since there have been multiple versions of Access, there will be multiple versions of this topic. That is why I didn't provide a link. Pick the version that is closest to the version of Access you are using. The answers to limits of various kinds will be found in the Specifications web page.