Subscript out of range on an array = recordset.GetRows

[Azzna]

I have a record set that has only one column basically. It hold
between 0-5 distinct values ascending. These values can combine to
mean different things. So, I need a way to look at this data and
evaluate it. This is within a for loop that looks at each record on a
table. I need to look at the values based on certain criteria. IIf,
for example, there are three values in that record set, I need to know
what they are. If they are 1,2,3, then do this set of instructions,
else do this other set of instructions. I have set up the values of
1,2,3 to a string for comparison. I am trying to put the values of
the record set into a string so that I can compare them. The record
set is already sorted to go from lowest value to highest value, so I
need to just dump them into an array that I can compare to. I hope
that makes sense.

So I set up some code:

recCount = vel.recordCount ' vel is the record set
Dim Array1() As Variant
Dim Array2() As Variant
Dim ValidArray As Boolean

ReDim Array1(vel.recordCount)
Array1 = Array(1, 2, 3) ' Set array for comparison
ReDim Array2(vel.recordCount)
Array2 = vel.GetRows(20)

' Check array against array2 to see if they
match, if no, then set ValidArray to false
Dim x As Integer
For x = 0 To 3
If (Array1(x) = Array2(x)) Then
ValidArray = True
Else
ValidArray = False
Exit For
End If
Next x


I read that I could use the GetRows function to put the values of my
record set into an array, but that I had to set the array I was
putting it into as a Variant. I had to set my comparison variable as
a variant too or they wouldn't compare. My array from my record set
doesn't seem to be populating. Specifically Array2 = vel.GetRows() is
showing "Subscipt out of Range" for an error message when I run the
code, and when I debug and look at the value of Array2 it says the
same thing. Why am I seeing this message and how could I fix it? Or,
is there a better way to do this? Any help is greatly appreciated.

Thank you!

 

[Douglas J. Steele]

Since Array1 is defined as Array(1, 2, 3), that means it has elements
Array1(0), Array1(1) and Array1(2). You're going up to 3.

To avoid problems like that, you're best off using

For x = LBound(Array1) To UBound(Array1)

 

[Azzna]

Since Array1 is defined as Array(1, 2, 3), that means it has elements
Array1(0), Array1(1) and Array1(2). You're going up to 3.

To avoid problems like that, you're best off using

For x = LBound(Array1) To UBound(Array1)

Thank you for that. I have switched it. I had it that way at one
point, but I have worked this code over a few times and apparently
forgot to switch it back. Sadly I am still getting the Subscript out
of range error. It seems that Array2 (Which I am using the getRows()
function with) isn't populating properly. Do you know if I am somehow
misusing that function?

 

[Douglas J. Steele]

I don't believe you need to declare the size of the array.

Try removing the 2 ReDim statements.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you for that. I have switched it. I had it that way at one
point, but I have worked this code over a few times and apparently
forgot to switch it back. Sadly I am still getting the Subscript out
of range error. It seems that Array2 (Which I am using the getRows()
function with) isn't populating properly. Do you know if I am somehow
misusing that function?