Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    104

    Unanswered: A more efficient way to do this? [VBA, MS Access 2002]

    Hi All,
    This is a bit of a coding problem. I hope I don't get flamed for this. Please note that YES I have searched the forums before posting this!
    OK, here is the situation. I have 10 combo boxes (pull-down lists). And I wish to update them. The number of boxes to be updated ranges from 1 to 10. The variable names (objects) used for these combo boxes are cmbobxGroup1, cmbobxGroup2, cmbobxGroup3, ..... all the way up to cmbobxGroup10.
    The only way I could think of is to do it this way (below), but I know its very inefficient. Is there a way to add an integer at the end of the variable name so I can use a For/next loop? Thanx
    Code:
    Private Sub UpdateGroupCombos(groups() As String)
    Dim num As Integer
    num = UBound(groups) + 1 'keeps track of how many groups to do
    
    If (num = 1) Then 'only one group
        cmbobxGroup1.Value = groups(0)
    ElseIf (num = 2) Then
        cmbobxGroup1.Value = groups(0)
        cmbobxGroup2.Value = groups(1)
    ElseIf (num = 3) Then
        cmbobxGroup1.Value = groups(0)
        cmbobxGroup2.Value = groups(1)
        cmbobxGroup3.Value = groups(2)
    ElseIf (num = 4) Then
        cmbobxGroup1.Value = groups(0)
        cmbobxGroup2.Value = groups(1)
        cmbobxGroup3.Value = groups(2)
        cmbobxGroup4.Value = groups(3)
    ElseIf (num = 5) Then
        cmbobxGroup1.Value = groups(0)
        cmbobxGroup2.Value = groups(1)
        cmbobxGroup3.Value = groups(2)
        cmbobxGroup4.Value = groups(3)
        cmbobxGroup5.Value = groups(4)
    ElseIf (num = 6) Then
        cmbobxGroup1.Value = groups(0)
        cmbobxGroup2.Value = groups(1)
        cmbobxGroup3.Value = groups(2)
        cmbobxGroup4.Value = groups(3)
        cmbobxGroup5.Value = groups(4)
        cmbobxGroup6.Value = groups(5)
    ElseIf (num = 7) Then
        cmbobxGroup1.Value = groups(0)
        cmbobxGroup2.Value = groups(1)
        cmbobxGroup3.Value = groups(2)
        cmbobxGroup4.Value = groups(3)
        cmbobxGroup5.Value = groups(4)
        cmbobxGroup6.Value = groups(5)
        cmbobxGroup7.Value = groups(6)
    ElseIf (num = 8) Then
        cmbobxGroup1.Value = groups(0)
        cmbobxGroup2.Value = groups(1)
        cmbobxGroup3.Value = groups(2)
        cmbobxGroup4.Value = groups(3)
        cmbobxGroup5.Value = groups(4)
        cmbobxGroup6.Value = groups(5)
        cmbobxGroup7.Value = groups(6)
        cmbobxGroup8.Value = groups(7)
    ElseIf (num = 9) Then
        cmbobxGroup1.Value = groups(0)
        cmbobxGroup2.Value = groups(1)
        cmbobxGroup3.Value = groups(2)
        cmbobxGroup4.Value = groups(3)
        cmbobxGroup5.Value = groups(4)
        cmbobxGroup6.Value = groups(5)
        cmbobxGroup7.Value = groups(6)
        cmbobxGroup8.Value = groups(7)
        cmbobxGroup9.Value = groups(8)
    Else 'if 10 or more (update the first 10 only)
        cmbobxGroup1.Value = groups(0)
        cmbobxGroup2.Value = groups(1)
        cmbobxGroup3.Value = groups(2)
        cmbobxGroup4.Value = groups(3)
        cmbobxGroup5.Value = groups(4)
        cmbobxGroup6.Value = groups(5)
        cmbobxGroup7.Value = groups(6)
        cmbobxGroup8.Value = groups(7)
        cmbobxGroup9.Value = groups(8)
        cmbobxGroup10.Value = groups(9)
        
    End If
        
    End Sub

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Try it this way, shassouneh

    Code:
    Private Sub UpdateGroupCombos(groups() As String)
       Dim intIndex As Integer
       Dim num As Integer
       num = UBound(groups) + 1 'keeps track of how many groups to do
       If num > 10 Then
          num = 10
       End If
       For intIndex = 1 To num
          Me("cmbobxGroup" & CStr(intIndex)).Value = groups(intIndex - 1)
       Next
    End Sub

  3. #3
    Join Date
    Nov 2003
    Posts
    104
    Oh wow.
    That did it. Super!

    Thanx. It much easier to deal with the code u gave me than the horrible code I had.

    Thanx again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •