Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Unanswered: Having Trouble With Arrays & For Next

    Hello,

    I'm hoping someone can help me. With the code below, the x array seems to work fine, but the z array automatically goes to the last value in the array and then gives me a subscript out of range error for the value of aProductType(z). I think there's a problem with the placement of the 2nd array. Can anyone help??

    'Delete all potential customer count queries based on the above array
    For x = 0 To 16
    strProductLine = aProductLine(x)
    For z = 0 To 14
    strProduct = strProductLine & " " & aProductType(z)

    For Each qdfLoop In CurrentDb.QueryDefs
    If qdfLoop.Name = "" & strProduct & " Customer Count By Month" Then
    DoCmd.DeleteObject acQuery, "" & strProduct & " Customer Count By Month"
    End If
    Next qdfLoop

    Next z

    Next x

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's in the arrays aProductLine() and aProductType() (or are they functions) and how are they defined ?

    Have a nice day!

  3. #3
    Join Date
    Jun 2009
    Posts
    6
    Hello,

    Thanks for taking a look at this. The arrays are defined as follows:

    aProductLine = Array("Product Line A", "Product Line B", etc...)
    aProductType = Array("Product Type A", "Product Type B", etc...)

    Thanks again.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The loop through the query defs looks unnecessary
    Code:
    'Delete all potential customer count queries based on the above array
    For x = 0 To 16
        strProductLine = aProductLine(x)
    
        For z = 0 To 14
            strProduct = strProductLine & " " & aProductType(z)
    
            debug.print "DELETING: " & strProduct & " Customer Count By Month"
    
            'DoCmd.DeleteObject acQuery, "" & strProduct & " Customer Count By Month"
    
        Next z
    
    Next x
    This should print out stuff like (based on your sample data):

    "DELETING: Product Line A Product Type A Customer Count By Month"
    "DELETING: Product Line A Product Type B Customer Count By Month"
    "DELETING: Product Line B Product Type A Customer Count By Month"
    "DELETING: Product Line B Product Type B Customer Count By Month"

    Does it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2009
    Posts
    6
    Hi,

    I tried that, however I still get a subscript out of range error. I think the issue is how I'm nesting the 2nd For Next loop.

    When I look at the variable values, I see at that strProduct is set to "Product Line A (First in the 1st array) Product Type B (last in the 2nd array)." This is strange that it would be the last in the 2nd array..shouldn't it go through each other one first?. The strProduct variable is set to "Product Line A (First in the 1st array)" and aProductType(z) is where the <Subscript out of range> error is. Am I somehow not telling it to stop looping through the 2nd array so that when it gets to the end it's generating the <Subscript out of range> error?

    Thanks.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh sorry - I missed the subscript problem. Try using the UBound and LBound functions to get the upper and lower bounds. Also (and I admit I'm a bit hazy on my VBA) but have you set the arrays to be zero based?

    {pseudo}
    Code:
    for z = LBound(myarray) to UBound(myarray)
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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