How to concatenate variables and use it as a new one 2

[JB83]

Dear reader(s),

I apologise for the title of this thread, but I couldn't define it different. My problem is that I want to use a variable which has to be "variable".



Dim tempProdGroup As Variant

tempProdGroup = Report_ProdForm.lstProdGroup.ListCount

For prodGroupCtr = 0 To tempProdGroup

tempGrpVal = Report_ProdForm.lstProdGroup.ItemData(prodGroupCtr)

If tempGrpVal = "Unit" Then

ln1ProdGroup(prodGroupCtr).Visible = True

Else

ln2ProdGroup(prodGroupCtr).Visible = True

End If

Next

End Sub



My problem is in the If - Else - End If part: I would like to use the variable prodGroupCtr to check which line has to become visible. I have defined ln1ProdGroup1 through ln1ProdGroup19 and ln2ProdGroup1 through ln2ProdGroup19.

Can this be done with a a concatenated string like

ln1ProdGroup(prodGrpCtr).Visible = True

(but then in a right way of VBA programming ) or has this be done completely different?

Thanks in advance,

Jochen. --------------------------------------------------------------------------------
It never hurts to help and it never helps to hurt.

 

[BillPower]

Dim tempProdGroup As Variant

tempProdGroup = Report_ProdForm.lstProdGroup.ListCount

For prodGroupCtr = 0 To tempProdGroup

tempGrpVal = Report_ProdForm.lstProdGroup.ItemData(prodGroupCtr)

If tempGrpVal = "Unit" Then

Me.Controls(ln1ProdGroup & prodGroupCtr).Visible = True

Else

Me.Controls(ln2ProdGroup & prodGroupCtr).Visible = True

End If

Next

End Sub

I've assumed that ln1ProdGroup & ln2ProdGroup are controls that you want to show or keep hidden, the above, in blue should work if this is the case.

Let me know if I’ve got it wrong.

Regards

Bill

 

[BillPower]

Oops, Change

For prodGroupCtr = 0 To tempProdGroup

to

For prodGroupCtr = 1 To tempProdGroup

We need the Loop to start at 1, missed that.

 

[JB83]

Bill,

Thank you for your incredible fast respond! I have tried your code and it almost worked: the variables ln1ProdGroup and ln2ProdGroup should be between quotes, otherwise Access can't find the variable. It is not critism on you, but there might be another member who can also use this code.

Dim tempProdGroup As Variant
   
tempProdGroup = Report_ProdForm.lstProdGroup.ListCount

For prodGroupCtr = 0 To tempProdGroup

tempGrpVal = Report_ProdForm.lstProdGroup.ItemData(prodGroupCtr)
    
    If tempGrpVal = "Unit" Then
       
        Me.Controls(

"

ln1ProdGroup

"

 & prodGroupCtr).Visible = True
    
    Else
  
        Me.Controls(

"

ln2ProdGroup

"

 & prodGroupCtr).Visible = True
        
    End If
    
Next
 
End Sub

Again, thanks a million for your reply! You don't know how much you have helped me with this!



Keep up this very good work!

Jochen. --------------------------------------------------------------------------------
It never hurts to help and it never helps to hurt.

 

[janeatwork]

Why don't you post this as a FAQ?