Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using 2 numeric variables in a string variable for nested loops

Status
Not open for further replies.
03Explorer

03Explorer

Technical User
Sep 13, 2005
304
US
Have a working loop that uses a variable name that is using the loop value (i) within the string name for populating. I am working on a nested second loop that sits within the first loop and need that loop value (r) to do data checking on specific values. The (i) loop is working wonderfully, but trying to get the 2nd loop (r) to work within is not appearing to be as easy.

Code: 
            Dim i As Integer  'loop counter for row tracking
            Dim r As Integer  'loop counter for Skill Type checking (Rob)
            For i = 1 To txtCountRows
                                   
            ' Set Row variables - note: the "(i)" is how we tell what row we are processing
                tmp1R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectProjectRow" & (i)).Value
                tmp2R(i) = Forms![frmSurveysPreGenerate].Controls("txtClientIDRow" & (i)).Value
                tmp3R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectServiceTypeRow" & (i)).Value
                tmp4R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRoleRow" & (i)).Value
                tmp5R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor1Row" & (i)).Value
                tmp6R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType1Row" & (i)).Value
                tmp7R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole1Row" & (i)).Value
                tmp8R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor2Row" & (i)).Value
                tmp9R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType2Row" & (i)).Value
                tmp10R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole2Row" & (i)).Value
                tmp11R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor3Row" & (i)).Value
                tmp12R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType3Row" & (i)).Value
                tmp13R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole3Row" & (i)).Value
                tmp14R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor4Row" & (i)).Value
                tmp15R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType4Row" & (i)).Value
                tmp16R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole4Row" & (i)).Value
                tmp17R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor5Row" & (i)).Value
                tmp18R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType5Row" & (i)).Value
                tmp19R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole5Row" & (i)).Value
               
               ' Count how many parents were populated for each row (variable strParentPopulated)
                    
                DoCmd.SetWarnings False
                
        ' Walk through Skill Type, checking for values of 'None' so they do NOT get appended  20190925(Rob)
        '    5 variables to check:
        '         tmp6R(i), tmp9R(i), tmp12R(i), tmp15R(i), tmp18R(i)
        '
        For r = 6 To 18 Step 3
            If tmp(r)R(i) = "None" then
                 tmp(r-1)R(i) = ""
                 tmp(r)R(i) = ""
                 tmp(r+1)R(i) = ""
            End If
        Next r
Next i


The intent is to cycle through 5 records looking for where the value = 'None'. If it does exist, the loop blanks out three values.

The issue is MS Access does not like the (r) numeric value as a second dynamic change in a field name.

I hope this is a simple mistake or oversight.
 
Sort by date Sort by votes
Just a suggestion....

Code: 
[blue]Dim aryTemp(4) As String[/blue]
Dim i As Integer  'loop counter for row tracking
            Dim r As Integer  'loop counter for Skill Type checking (Rob)
            For i = 1 To txtCountRows
                                   
            ' Set Row variables - note: the "(i)" is how we tell what row we are processing
                tmp1R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectProjectRow" & (i)).Value
                tmp2R(i) = Forms![frmSurveysPreGenerate].Controls("txtClientIDRow" & (i)).Value
                tmp3R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectServiceTypeRow" & (i)).Value
                tmp4R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRoleRow" & (i)).Value
                tmp5R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor1Row" & (i)).Value
                tmp6R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType1Row" & (i)).Value
                [blue]aryTemp(0) = tmp6R(i)[/blue]
                tmp7R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole1Row" & (i)).Value
                tmp8R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor2Row" & (i)).Value
                tmp9R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType2Row" & (i)).Value
                [blue]aryTemp(1) = tmp9R(i)[/blue]
                tmp10R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole2Row" & (i)).Value
                tmp11R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor3Row" & (i)).Value
                tmp12R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType3Row" & (i)).Value
                [blue]aryTemp(2) = tmp12R(i)[/blue]
                tmp13R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole3Row" & (i)).Value
                tmp14R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor4Row" & (i)).Value
                tmp15R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType4Row" & (i)).Value
                [blue]aryTemp(3) = tmp15R(i)[/blue]
                tmp16R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole4Row" & (i)).Value
                tmp17R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor5Row" & (i)).Value
                tmp18R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType5Row" & (i)).Value
                [blue]aryTemp(4) = tmp18R(i)[/blue]
                tmp19R(i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole5Row" & (i)).Value
               
               ' Count how many parents were populated for each row (variable strParentPopulated)
                    
                DoCmd.SetWarnings False
                
        ' Walk through Skill Type, checking for values of 'None' so they do NOT get appended  20190925(Rob)
        '    5 variables to check:
        '         tmp6R(i), tmp9R(i), tmp12R(i), tmp15R(i), tmp18R(i)
        [blue]For r = LBound(aryTemp) To aryTemp(UBound)
            If aryTemp(r) = "None" then
                 tmp5R(i) = = ""
                 tmp6R(i) = = ""
                 tmp7R(i) = = ""
            End If
        Next r[/blue]
Next i

I hope I've got the numbers right, but you get the idea...


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
I think you are on the right track, but in my version the for next loop is tracking numbers 6, 9,12,15,18 as the 5 fields needing to be vetted out. if one of them has 'none' then the loop replaces value checked with blank, value checked minus one = blank, and value checked plus one = blank.

I don't see how that happens in your suggestion... I am hoping I am going to be wrong.
Rob
 
Upvote 0 Downvote
I see, so forget about my array approach, I would change:

Code: 
For r = 6 To 18 Step 3
            If tmp(r)R(i) = "None" then
                 tmp(r-1)R(i) = ""
                 tmp(r)R(i) = ""
                 tmp(r+1)R(i) = ""
            End If
        Next r

to (unfortunetly)

Code: 
If tmp6R(i) = "None" Then
    tmp5R(i) = = ""
    tmp6R(i) = = ""
    tmp7R(i) = = ""
End If
If tmp9R(i) = "None" Then
    tmp8R(i) = = ""
    tmp9R(i) = = ""
    tmp10R(i) = = ""
End If
If tmp12R(i) = "None" Then
    tmp11R(i) = = ""
    tmp12R(i) = = ""
    tmp13R(i) = = ""
End If
If tmp15R(i) = "None" Then
    tmp14R(i) = = ""
    tmp15R(i) = = ""
    tmp16R(i) = = ""
End If
If tmp18R(i) = "None" Then
    tmp17R(i) = = ""
    tmp18R(i) = = ""
    tmp19R(i) = = ""
End If

Unless somebody smarter jumps in with some bright solution....


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
I was hoping to keep it cleaner. why are you using the double equals to set TMPs?

ie tmp17R(i) = = ""

why not

tmp17R(i) = ""



Still hoping that someone has a looping option that works!! :)

Rob
 
Upvote 0 Downvote
>The issue is MS Access does not like the (r) numeric value as a second dynamic change in a field name.

No, the issue is that VBA doesn't like you trying to make part of a variable name a variable …


But the reality is that what you really seem to be trying to do is address a 2 dimensional array, but are only using a single dimension array, which can be remedied fairly quickly without any significant rewrite

So your code could become something like:

Code: 
[blue]    Dim i As Integer  [COLOR=green]'loop counter for row tracking[/color]
    Dim r As Integer  [COLOR=green]'loop counter for Skill Type checking (Rob)[/color]
    
    For i = 1 To txtCountRows
        [COLOR=green]' Set Row variables - note: the "(i)" is how we tell what row we are processing[/color]
        tmp(1, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectProjectRow" & (i)).Value
        tmp(2, i) = Forms![frmSurveysPreGenerate].Controls("txtClientIDRow" & (i)).Value
        tmp(3, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectServiceTypeRow" & (i)).Value
        tmp(4, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRoleRow" & (i)).Value
        tmp(5, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor1Row" & (i)).Value
        tmp(6, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType1Row" & (i)).Value
        tmp(7, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole1Row" & (i)).Value
        tmp(8, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor2Row" & (i)).Value
        tmp(9, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType2Row" & (i)).Value
        tmp(10, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole2Row" & (i)).Value
        tmp(11, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor3Row" & (i)).Value
        tmp(12, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType3Row" & (i)).Value
        tmp(13, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole3Row" & (i)).Value
        tmp(14, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor4Row" & (i)).Value
        tmp(15, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType4Row" & (i)).Value
        tmp(16, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole4Row" & (i)).Value
        tmp(17, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSurveyor5Row" & (i)).Value
        tmp(18, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectSkillType5Row" & (i)).Value
        tmp(19, i) = Forms![frmSurveysPreGenerate].Controls("cboSelectRole5Row" & (i)).Value
       
        [COLOR=green]' Count how many parents were populated for each row (variable strParentPopulated)[/color]
            
        DoCmd.SetWarnings False
        
        [COLOR=green]' Walk through Skill Type, checking for values of 'None' so they do NOT get appended  20190925(Rob)
        ' 5 variables to check:
        '  tmp(6,i), tmp(9,i), tmp(12,i), tmp(15,i), tmp(18,i)[/color]
        For r = 6 To 18 Step 3
            If tmp(r, i) = "None" Then
                tmp(r - 1, i) = ""
                tmp(r, i) = ""
                tmp(r + 1, i) = ""
            End If
        Next r
    Next I[/blue]
 
Upvote 0 Downvote
The issue with [tt]tmp17R(i) = = ""[/tt] is: copy-paste without paying attention, sorry about it :-(

And here is somebody smarter with the clever solution [thumbsup2]



---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
I ended up not using the double variable within a variable name. Instead I used a straight up nested If statement to catch the specific named fields. My attempt to use a loop was too much and by adding an array is overkill for the purpose at hand.

Thanks for suggesting it tho.
Rob
 
Upvote 0 Downvote
>adding an array is overkill

But you already had an array. All we did was extend the dimensions. Or are you saying even your original use of one was overkill? Ah well.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JimStrand
  • Locked
  • Question
Using Report Header (Form control) as Variable in Report Expression 1
Replies
11
Views
809
dhookom
dhookom
Moss100
  • Locked
  • Question
Remove everything including the comma from the last comma of a string. 1
Replies
10
Views
381
Andrzejek
Andrzejek
JimStrand
  • Locked
  • Question
Trying to Use Form Control as Variable in Report Control Source 2
Replies
5
Views
300
Andrzejek
Andrzejek
M
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
538
SoftwareRT
SoftwareRT
Moss100
  • Question
Open filtered for and set cursor at particular record
Replies
1
Views
156
Moss100
Moss100

Part and Inventory Search

Sponsor

Back
Top