Array Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,

I have the following code in Access 2000 and have a question in regards to
arrays. I was wondering if I can declare a variable array (without
specifying the number of elements ahead of time)? The reason I ask is that
the size of the array will vary depending upon the number of elements a user
selects in a list box. So far, I have been unable to get this to work. I
have tried "Dim strArray() as String", but I receive the error message
"Subscript out of range" when I attempt to assign a value to the individual
array element. Is there something obvious I am missing here? My current
code is below.

Thanks in advance!

Private Sub cmdAdd_Click()
Dim db As DAO.Database
Dim i As Long
Dim j As Long
Dim rs As DAO.Recordset
Dim ctlSource As Control
Dim strArray() As String
Dim strItem As String
Dim lngCurrentRow As Long

Set ctlSource = Me!lstBranchCodes
i = 0

'// Loop through the list box to obtain each item that was selected.
For lngCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(lngCurrentRow) Then
strItem = ctlSource.Column(0, lngCurrentRow)
'// "Subscript out of range" occurs here.
strArray(i) = strItem
i = i + 1
End If
Next lngCurrentRow
...
...
 
You need to use the ReDim statement once you know the size of the array:

ReDim strArray(ctlSource.ListCount - 1)

'// Loop through the list box to obtain each item that was selected.
For lngCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(lngCurrentRow) Then
strItem = ctlSource.Column(0, lngCurrentRow)
'// "Subscript out of range" occurs here.
strArray(i) = strItem
i = i + 1
End If
Next lngCurrentRow

If you're concerned that not all of the elements in the array are assigned a
value (because of your If statement), you can ReDim again afterwards, using
the Preserve keyword:

ReDim Preserve strArray(i-1)

The other option would be to redim the array before each insertion:

'// Loop through the list box to obtain each item that was selected.
For lngCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(lngCurrentRow) Then
strItem = ctlSource.Column(0, lngCurrentRow)
'// "Subscript out of range" occurs here.

ReDim Preserve strArray(i)
strArray(i) = strItem
i = i + 1
End If
Next lngCurrentRow

However, that's very inefficient.
 
You only need to add the Redim Preserve to what you have.

For lngCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(lngCurrentRow) Then
strItem = ctlSource.Column(0, lngCurrentRow)
'// "Subscript out of range" occurs here.
Redim Preserve strArray(i)
strArray(i) = strItem
i = i + 1
End If
Next lngCurrentRow
 
Thanks!
--
Sherwood


Douglas J Steele said:
You need to use the ReDim statement once you know the size of the array:

ReDim strArray(ctlSource.ListCount - 1)

'// Loop through the list box to obtain each item that was selected.
For lngCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(lngCurrentRow) Then
strItem = ctlSource.Column(0, lngCurrentRow)
'// "Subscript out of range" occurs here.
strArray(i) = strItem
i = i + 1
End If
Next lngCurrentRow

If you're concerned that not all of the elements in the array are assigned a
value (because of your If statement), you can ReDim again afterwards, using
the Preserve keyword:

ReDim Preserve strArray(i-1)

The other option would be to redim the array before each insertion:

'// Loop through the list box to obtain each item that was selected.
For lngCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(lngCurrentRow) Then
strItem = ctlSource.Column(0, lngCurrentRow)
'// "Subscript out of range" occurs here.

ReDim Preserve strArray(i)
strArray(i) = strItem
i = i + 1
End If
Next lngCurrentRow

However, that's very inefficient.
Click to expand...
 
Back
Top