By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

Subscript out of range problem

100+
P: 122
Can anyone see what's wrong with this code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command278_Click()
  2. On Error GoTo Err_Command278_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim whereArray()
  6.     Dim stLinkCriteria, fromLName, toLName, strMsg As String
  7.     Dim counter, i As Integer
  8.  
  9.     Select Case optionPrintWhat.Value
  10.     Case 1
  11.         stDocName = "rptPastMenus"
  12.         counter = 0
  13.         If Not IsNull(Me![txtDateSelectFrom]) Then
  14.             whereArray(counter) = "menu.date >=#" & Me![txtDateSelectFrom] & "#"
  15.             counter = counter + 1
  16.         End If
  17.         If Not IsNull(Me![txtDateSelectTo]) Then
  18.             whereArray(counter) = "menu.date <=#" & Me![txtDateSelectTo] & "#"
  19.             counter = counter + 1
  20.         End If
  21.         If Not IsNull(Me![chkSubmitted]) Then
  22.             If IsNull(Me![chkNotSubmitted]) Then
  23.                 whereArray(counter) = "menu.submitted"
  24.                 counter = counter + 1
  25.             End If
  26.         End If
  27.         If Not IsNull(Me![chkNotSubmitted]) Then
  28.             If IsNull(Me![chkSubmitted]) Then
  29.                 whereArray(counter) = "menu.submitted IS NULL"
  30.                 counter = counter + 1
  31.             End If
  32.         End If
  33.         If Not IsNull(Me![cboProviderSelectFrom]) Then
  34.             fromLName = Left(Me![cboProviderSelectFrom], InStr(Me![cboProviderSelectFrom], ",") - 1)
  35.             whereArray(counter) = "provider.lname >='" & fromLName & "'"
  36.             counter = counter + 1
  37.         End If
  38.         If Not IsNull(Me![cboProviderSelectTo]) Then
  39.             toLName = Left(Me![cboProviderSelectTo], InStr(Me![cboProviderSelectTo], ",") - 1)
  40.             whereArray(counter) = "provider.lname >='" & toLName & "'"
  41.             counter = counter + 1
  42.         End If
  43.  
  44.         'Display contents of array (for debugging)
  45.  
  46.         strMsg = "Items in array:" & vbCr & vbCr
  47.         For i = 0 To counter
  48.             strMsg = strMsg & i & ":" & vbTab & whereArray(i) & vbCr
  49.         Next i
  50.         MsgBox strMsg, vbOKOnly + vbInformation, "Where Conditions"
  51.  
  52.  
  53.     Case 2
  54.         stDocName = "rptChildReEnrollment"
  55.     Case 3
  56.         stDocName = "rptClaimSummaryForm"
  57.     Case 4
  58.         stDocName = "rptProviderDetail"
  59.     Case 5
  60.         stDocName = "rptProvMailingLabels"
  61.     Case Else
  62.         stDocName = "rptParentMailingLabels"
  63.     End Select
  64.  
  65.     DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
  66.  
  67. Exit_Command278_Click:
  68.     Exit Sub
  69.  
  70. Err_Command278_Click:
  71.     MsgBox Err.Description
  72.     Resume Exit_Command278_Click
  73.  
  74. 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?
Aug 13 '08 #1
Share this Question
Share on Google+
4 Replies


100+
P: 122
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.
Aug 13 '08 #2

P: 50
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.
Aug 13 '08 #3

100+
P: 122
Thanks! I'll give that a try.
Aug 14 '08 #4

NeoPa
Expert Mod 15k+
P: 31,489
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.
Aug 15 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.