Looping field names on form with variable used in the fieldname

[03Explorer]

I have a task to take a form that uses many similar field names but the difference is the 2nd character (pertaining to sections on form).

Field names on form frmSomeName.
S1ClientID
S1PracticeID
S1ProjectName
S1ChildRoleID
S1ServiceTypeRadio  
S1Parent1ID
S1Parent1RoleID
S1Parent2ID
S1Parent2RoleID

S2ClientID
S2PracticeID
S2ProjectName
S2ChildRoleID
S2ServiceTypeRadio  
S2Parent1ID
S2Parent1RoleID
S2Parent2ID
S2Parent2RoleID

... 
S5xxx

What I want to do is loop 1 = 1 to 5
Then call field S(i)ClientID [S1ClientID] to verifiy if it is null or not
for a few of those fields.

I am trying
For i = 1 to 5
Me.S(i)ClientID
S(i)ClientID
Forms.("frmSomeName").S(i)ClientID
next i

all three ways don't work, (Compile error: Expected: list separator or ) any suggestions?

 

[03Explorer]

*sigh* do I need to make each an array (1 to 5)
for those variables and then use a loop with calling the array vs the field names+variable counter embedded?

 

[dhookom]

Are those field names or control names or both? You can use something like:

Dim strSuffix as String
Dim intI as Integer
Dim intMaxI as Integer
intMaxI = 5
For intI = 1 To intMaxID 
    strSuffix = "ClientID"
    [COLOR=#4E9A06]'I don't know what you want want to do[/color]
    If IsNull(Me("S" & intI & strSuffix)) Then
      [COLOR=#4E9A06]'your code here?[/color]
    End If
Next
For intI = 1 To intMaxID 
    strSuffix = "ChildRoleID"
    If IsNull(Me("S" & intI & strSuffix)) Then
     [COLOR=#4E9A06] 'your code here?[/color]
    End If
Next

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

they are the 'name' of a combo box

What I started doing was to create each section into a single array so I can loop through them

'-- Convert Variables on form to Arrays(1 to 5)
    Dim Client(1 To 5) As Variant
    Dim Practice(1 To 5) As Variant
    Dim ProjectName(1 To 5) As Variant
    Dim ChildRole(1 To 5) As Variant
    Dim ServiceTypeRadio(1 To 5) As Variant
    Dim ServiceType01ID(1 To 5) As Variant
    Dim ServiceType02ID(1 To 5) As Variant
    Dim ServiceType03ID(1 To 5) As Variant
    Dim ServiceType04ID(1 To 5) As Variant
    Dim ServiceType05ID(1 To 5) As Variant
    Dim ServiceType06ID(1 To 5) As Variant
    Dim ServiceType07ID(1 To 5) As Variant
    Dim ServiceType08ID(1 To 5) As Variant
    Dim ServiceType09ID(1 To 5) As Variant
    Dim ServiceType10ID(1 To 5) As Variant
    Dim Parent01ID(1 To 5) As Variant
    Dim Parent02ID(1 To 5) As Variant
    Dim Parent03ID(1 To 5) As Variant
    Dim Parent04ID(1 To 5) As Variant
    Dim Parent05ID(1 To 5) As Variant
    Dim Parent06ID(1 To 5) As Variant
    Dim Parent07ID(1 To 5) As Variant
    Dim ParentRole01ID(1 To 5) As Variant
    Dim ParentRole02ID(1 To 5) As Variant
    Dim ParentRole03ID(1 To 5) As Variant
    Dim ParentRole04ID(1 To 5) As Variant
    Dim ParentRole05ID(1 To 5) As Variant
    Dim ParentRole06ID(1 To 5) As Variant
    Dim ParentRole07ID(1 To 5) As Variant


'populate array fields with form data
    Client(1) = Me.S1ClientID
    Practice(1) = Me.S1PracticeID
    ProjectName(1) = Me.S1ProjectName
    ChildRole(1) = Me.S1ChildRoleID
    ServiceTypeRadio(1) = Me.S1ServiceTypeRadio
    ServiceType01ID(1) = Me.S1ServiceType01ID
    ServiceType02ID(1) = Me.S1ServiceType02ID
    ServiceType03ID(1) = Me.S1ServiceType03ID
    ServiceType04ID(1) = Me.S1ServiceType04ID
    ServiceType05ID(1) = Me.S1ServiceType05ID
    ServiceType06ID(1) = Me.S1ServiceType06ID
    ServiceType07ID(1) = Me.S1ServiceType07ID
    ServiceType08ID(1) = Me.S1ServiceType08ID
    ServiceType09ID(1) = Me.S1ServiceType09ID
    ServiceType10ID(1) = Me.S1ServiceType10ID
    Parent01ID(1) = Me.S1Parent1ID
    Parent02ID(1) = Me.S1Parent2ID
    Parent03ID(1) = Me.S1Parent3ID
    Parent04ID(1) = Me.S1Parent4ID
    Parent05ID(1) = Me.S1Parent5ID
    Parent06ID(1) = Me.S1Parent6ID
    Parent07ID(1) = Me.S1Parent7ID
    ParentRole01ID(1) = Me.S1Parent1RoleID
    ParentRole02ID(1) = Me.S1Parent2RoleID
    ParentRole03ID(1) = Me.S1Parent3RoleID
    ParentRole04ID(1) = Me.S1Parent4RoleID
    ParentRole05ID(1) = Me.S1Parent5RoleID
    ParentRole06ID(1) = Me.S1Parent6RoleID
    ParentRole07ID(1) = Me.S1Parent7RoleID

(...)

For i = 1 To 5
If (PracticeID <= 5 And IsNull(Client(i))) Or (PracticeID > 5 And IsNull(ChildRole(i))) Then
    'Skip Section due to non sufficient data
    GoTo Continue
Else
    Debug.Print "Section - " & i
    Debug.Print "Child: " & ChildID
    Debug.Print "Practice: " & PracticeID
    Debug.Print "S" & i & "Client: " & Client(i)
    Debug.Print "S" & i & "Practice: " & Practice(i)
    Debug.Print "S" & i & "Projectname: " & ProjectName(i)
    Debug.Print "S" & i & "Child Role: " & ChildRole(i)
    Debug.Print "S" & i & "ServiceTypeRadio: " & ServiceTypeRadio(i)
    Debug.Print "S" & i & "Parent01: " & Parent01ID(i)
    Debug.Print "S" & i & "ParentRole01: " & ParentRole01ID(i)
    Debug.Print "S" & i & "Parent02: " & Parent02ID(i)
    Debug.Print "S" & i & "ParentRole02: " & ParentRole02ID(i)
End If
Continue:
Next i

Purpose: I am building an outer loop so I can grab the data in each section (inner loop) and populate into specific tables.

 

[dhookom]

I'm not understanding this at all since it seems you are creating lots of arrays with a single value. Can you explain the bigger picture?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

okay here is a layout of what I am working on grabbing. (image)

What I am doing is collecting data from each grouping (ie survey1, survey2, survey3, ..., survey5) to be populated into two tables. I found that using arrays allows me to loop through the groupings (outer loop) with 1st inner loop being Surveyors and a inner loop off the surveyors is going to work with the ServiceTypes.

Using the field names did not seem to be friendly when trying to loop through

Loop i = 1 to 5
  S(i)ClientID
  S(i)Parent1ID
next i

So I make them into arrays
ie ClientID(1 to 5)

Loop i = 1 to 5
  clientID(i)
next i

The parent led me to a 2 dimensional array
ParentID(1 to 5, 1 to 7)
S1Parent1ID = ParentID(1,1)
S1Parent2ID = ParentID(1,2)
S3Parent1ID = ParentID(3,1)
S3Parent2ID = ParentID(3,2)
S3Parent3ID = ParentID(3,3)

So I can have my loops to process the data in a more compiled method (loop vs linear-aka line by line, fieldname hard coded)

For i = 1 to 5
  clientID(i)
  For p = 1 to 7
    ParentID(i, p)
  next p
next i

* In essence I am trying to make a form that was created by someone else, have data I can work with in a more streamlined and efficient coding approach (what is in the image are the fields I am given to work with) *

** maybe in other worlds when one person makes a form and another codes to use it can collaborate more, just trying to make best decisions on how to write efficient code using fast techniques vs UBER long code (aka newbie method) **