Subscript out of range problem

[Annalyzer]

Can anyone see what's wrong with this code:

Private Sub Command278_Click()
On Error GoTo Err_Command278_Click
 
    Dim stDocName As String
    Dim whereArray()
    Dim stLinkCriteria, fromLName, toLName, strMsg As String
    Dim counter, i As Integer
 
    Select Case optionPrintWhat.Value
    Case 1
        stDocName = "rptPastMenus"
        counter = 0
        If Not IsNull(Me![txtDateSelectFrom]) Then
            whereArray(counter) = "menu.date >=#" & Me![txtDateSelectFrom] & "#"
            counter = counter + 1
        End If
        If Not IsNull(Me![txtDateSelectTo]) Then
            whereArray(counter) = "menu.date <=#" & Me![txtDateSelectTo] & "#"
            counter = counter + 1
        End If
        If Not IsNull(Me![chkSubmitted]) Then
            If IsNull(Me![chkNotSubmitted]) Then
                whereArray(counter) = "menu.submitted"
                counter = counter + 1
            End If
        End If
        If Not IsNull(Me![chkNotSubmitted]) Then
            If IsNull(Me![chkSubmitted]) Then
                whereArray(counter) = "menu.submitted IS NULL"
                counter = counter + 1
            End If
        End If
        If Not IsNull(Me![cboProviderSelectFrom]) Then
            fromLName = Left(Me![cboProviderSelectFrom], InStr(Me![cboProviderSelectFrom], ",") - 1)
            whereArray(counter) = "provider.lname >='" & fromLName & "'"
            counter = counter + 1
        End If
        If Not IsNull(Me![cboProviderSelectTo]) Then
            toLName = Left(Me![cboProviderSelectTo], InStr(Me![cboProviderSelectTo], ",") - 1)
            whereArray(counter) = "provider.lname >='" & toLName & "'"
            counter = counter + 1
        End If
 
        'Display contents of array (for debugging)
 
        strMsg = "Items in array:" & vbCr & vbCr
        For i = 0 To counter
            strMsg = strMsg & i & ":" & vbTab & whereArray(i) & vbCr
        Next i
        MsgBox strMsg, vbOKOnly + vbInformation, "Where Conditions"
 
 
    Case 2
        stDocName = "rptChildReEnrollment"
    Case 3
        stDocName = "rptClaimSummaryForm"
    Case 4
        stDocName = "rptProviderDetail"
    Case 5
        stDocName = "rptProvMailingLabels"
    Case Else
        stDocName = "rptParentMailingLabels"
    End Select
 
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
 
Exit_Command278_Click:
    Exit Sub
 
Err_Command278_Click:
    MsgBox Err.Description
    Resume Exit_Command278_Click
 
End Sub

I'm trying to create an array of strings based on choices made by the user and then concatenate the strings into one string that I can use as the Where Condition for the DoCmd.OpenReport method.

Currently, this is throwing an error on line 14 telling me the "Subscript is out of range." I do not have Option base 1 set, so it should default to option base 0, right? So, if counter equals 0, then whereArray(0) should be the first location in the array, shouldn't it? Where is my logic failing?

[Annalyzer]

Update: I got this code to work by declaring the array as a fixed-size array, but I don't understand why that worked. Isn't it better to declare a dynamic array if you don't know how many elements will be stored in the array? If someone can explain this to me, I would be grateful.

[Krandor]

You are using an empty array and then you are trying to insert a value into an array cell that doesn't exist. Big problem.

If you want to dynamically add a new element to the array each time you loop, then you have to use
Redim Preserve YourArray(1 to Ubound(YourArray)+1)
just before your data insertion.

Make sure and code
Redim Preserve YourArray(1 to 1)
before your loop so the array is at least initialized, otherwise it will fail on the very first attempt.

[Annalyzer]

Thanks! I'll give that a try.

[NeoPa]

As a general rule, redimming an array in each iteration of a loop is not to be encouraged. If it is important to save as much space as possible, then consider redimming every 10 iterations or so.