dynamic arrays

G

Guest

I have the following code in an Access 2003 form:


Dim i As Integer
Dim rstHrngLossFctrs As New ADODB.Recordset
Dim arrHrngLoss() As Variant
Dim SQLstmt As String

If Not IsNull(Me!Tnchp_Profile_ID) Then

intTnchpProfile_ID = Me![Tnchp_Profile_ID]
SQLstmt = "SELECT Hrng_Loss_Fctr_Txt FROM Hrng_Loss_Fctrs " & _
"WHERE Tnchp_Profile_ID = " & intTnchpProfile_ID & _
" ORDER BY Hrng_Loss_Fctrs_Lst_ID;"

rstHrngLossFctrs.Open SQLstmt, CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

i = 0

With rstHrngLossFctrs

/* Subscript out of range happens here */
ReDim arrHrngLoss((.RecordCount - 1), 2)

Do Until .EOF

arrHrngLoss(i, 0) = (i + 1)
arrHrngLoss(i, 1) = ![Hrng_Loss_Fctr_Txt]
.MoveNext
i = i + 1

Loop

.Close

End With

Set rsHrngLossFctrs = Nothing

I get a Subscript out of range error on the ReDim statement. The array is a
two dimentional array
 
G

Guest

If your record count is zero you would be attempting a redim of -1,2 which
would give you that error.

I would recommend putting the recordcount in a variable first and then

If newvariable > 0 then
ReDim arrHrngLoss(Newvariable - 1, 2)
....

Hopes this helps!
 
G

Guest

Thank you Rick, that suggestion works fine, but I am a bit lost. The query in
my code should return 3 rows but when I look at the count of rows the query
will return it is -1. Any idea why I would get -1 and not the 3 rows I expect
or even 0?

Rick Roberts said:
If your record count is zero you would be attempting a redim of -1,2 which
would give you that error.

I would recommend putting the recordcount in a variable first and then

If newvariable > 0 then
ReDim arrHrngLoss(Newvariable - 1, 2)
...

Hopes this helps!
BRHancock said:
I have the following code in an Access 2003 form:


Dim i As Integer
Dim rstHrngLossFctrs As New ADODB.Recordset
Dim arrHrngLoss() As Variant
Dim SQLstmt As String

If Not IsNull(Me!Tnchp_Profile_ID) Then

intTnchpProfile_ID = Me![Tnchp_Profile_ID]
SQLstmt = "SELECT Hrng_Loss_Fctr_Txt FROM Hrng_Loss_Fctrs " & _
"WHERE Tnchp_Profile_ID = " & intTnchpProfile_ID & _
" ORDER BY Hrng_Loss_Fctrs_Lst_ID;"

rstHrngLossFctrs.Open SQLstmt, CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

i = 0

With rstHrngLossFctrs

/* Subscript out of range happens here */
ReDim arrHrngLoss((.RecordCount - 1), 2)

Do Until .EOF

arrHrngLoss(i, 0) = (i + 1)
arrHrngLoss(i, 1) = ![Hrng_Loss_Fctr_Txt]
.MoveNext
i = i + 1

Loop

.Close

End With

Set rsHrngLossFctrs = Nothing

I get a Subscript out of range error on the ReDim statement. The array is a
two dimentional array
Click to expand...
Click to expand...
 
G

Guest

Without first hand knowledge of your database structure the only thing I
would suggest is to break the code at your open statement, debug.print the
SQLstmt to make sure your query works (paste in into a new query and execute
it).

The only other thing i see is that you have no .movefirst but that should
not affect your .recordcount/redim

Good luck!

BRHancock said:
Thank you Rick, that suggestion works fine, but I am a bit lost. The query in
my code should return 3 rows but when I look at the count of rows the query
will return it is -1. Any idea why I would get -1 and not the 3 rows I expect
or even 0?

Rick Roberts said:
If your record count is zero you would be attempting a redim of -1,2 which
would give you that error.

I would recommend putting the recordcount in a variable first and then

If newvariable > 0 then
ReDim arrHrngLoss(Newvariable - 1, 2)
...

Hopes this helps!
BRHancock said:
I have the following code in an Access 2003 form:


Dim i As Integer
Dim rstHrngLossFctrs As New ADODB.Recordset
Dim arrHrngLoss() As Variant
Dim SQLstmt As String

If Not IsNull(Me!Tnchp_Profile_ID) Then

intTnchpProfile_ID = Me![Tnchp_Profile_ID]
SQLstmt = "SELECT Hrng_Loss_Fctr_Txt FROM Hrng_Loss_Fctrs " & _
"WHERE Tnchp_Profile_ID = " & intTnchpProfile_ID & _
" ORDER BY Hrng_Loss_Fctrs_Lst_ID;"

rstHrngLossFctrs.Open SQLstmt, CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

i = 0

With rstHrngLossFctrs

/* Subscript out of range happens here */
ReDim arrHrngLoss((.RecordCount - 1), 2)

Do Until .EOF

arrHrngLoss(i, 0) = (i + 1)
arrHrngLoss(i, 1) = ![Hrng_Loss_Fctr_Txt]
.MoveNext
i = i + 1

Loop

.Close

End With

Set rsHrngLossFctrs = Nothing

I get a Subscript out of range error on the ReDim statement. The array is a
two dimentional array
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am displaying the constructed SQL statement in a message box and the
statement looks fine. When you say break the code at the open statement do
you mean place a Debug.Print SQLstmt after the open? Would I put the
..MoveFirst after the With statement?

Rick Roberts said:
Without first hand knowledge of your database structure the only thing I
would suggest is to break the code at your open statement, debug.print the
SQLstmt to make sure your query works (paste in into a new query and execute
it).

The only other thing i see is that you have no .movefirst but that should
not affect your .recordcount/redim

Good luck!

BRHancock said:
Thank you Rick, that suggestion works fine, but I am a bit lost. The query in
my code should return 3 rows but when I look at the count of rows the query
will return it is -1. Any idea why I would get -1 and not the 3 rows I expect
or even 0?

Rick Roberts said:
If your record count is zero you would be attempting a redim of -1,2 which
would give you that error.

I would recommend putting the recordcount in a variable first and then

If newvariable > 0 then
ReDim arrHrngLoss(Newvariable - 1, 2)
...

Hopes this helps!
:

I have the following code in an Access 2003 form:


Dim i As Integer
Dim rstHrngLossFctrs As New ADODB.Recordset
Dim arrHrngLoss() As Variant
Dim SQLstmt As String

If Not IsNull(Me!Tnchp_Profile_ID) Then

intTnchpProfile_ID = Me![Tnchp_Profile_ID]
SQLstmt = "SELECT Hrng_Loss_Fctr_Txt FROM Hrng_Loss_Fctrs " & _
"WHERE Tnchp_Profile_ID = " & intTnchpProfile_ID & _
" ORDER BY Hrng_Loss_Fctrs_Lst_ID;"

rstHrngLossFctrs.Open SQLstmt, CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

i = 0

With rstHrngLossFctrs

/* Subscript out of range happens here */
ReDim arrHrngLoss((.RecordCount - 1), 2)

Do Until .EOF

arrHrngLoss(i, 0) = (i + 1)
arrHrngLoss(i, 1) = ![Hrng_Loss_Fctr_Txt]
.MoveNext
i = i + 1

Loop

.Close

End With

Set rsHrngLossFctrs = Nothing

I get a Subscript out of range error on the ReDim statement. The array is a
two dimentional array
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error '9' when calling LBound on a dynamic array 2
SQL-Statment Error "No value given for one or more required parame 1
Please help!!! Data Type Mismatch in Criteria Expression. 6
Dynamic array 3
Redim Preserve dynamic array gives subscript out of range - Access 3
Array range error question 1
Displaying A Recordset 1
Remove Duplicates in Array 5

Top