<subscript out of range>

[Candace]

In the function FillIndefArray below, when I run it the
code runs through but I get inaccurate results. When I
traced through the code I discovered that for the lines
with aryTestArray(intArrayCount) it says <subscript out of
range>

How do I fix that? Is it not recognizing the end of file?

Candace

*********************************************

Function FillIndefArray()
Dim dbSample As DAO.Database
Dim rstSample As DAO.Recordset
Dim intArrayCount As Integer
Dim aryTestArray() As Variant

Set dbSample = CurrentDb()
Set rstSample = dbSample.OpenRecordset("Holiday")

intArrayCount = 0

' Fill the array.
With rstSample
.MoveFirst
Do Until rstSample.EOF
If (Forms![Startup]![Last_YTD_Start] <= !
[Holiday_Stat_Date]) And (Forms![Startup]![Q4_End] >= !
[Holiday_Stat_Date]) Then
'Make place for the start date and
' fill it with the start date in SQL format.
ReDim Preserve aryTestArray(intArrayCount)
aryTestArray(intArrayCount) = !
[Holiday_Stat_Date]
' Increase the array index for the next
Redim
intArrayCount = intArrayCount + 1
End If
.MoveNext
Loop
.Close
End With

'Return the array with dates
FillIndefArray = aryTestArray

End Function

 

[Can]

I forgot to mention when I traced through the code I
discovered that

1) the line With rstSample - gives error 'object invalid
or no longer set'
2) rhe if statement on
If (Forms![Startup]![Last_YTD_Start] <= !
[Holiday_Stat_Date]) And (Forms![Startup]![Q4_End] >= !
[Holiday_Stat_Date]) Then
- gives error 'Object variable or with Block variable not
set' for the ![Holiday_Stat_Date] field

Candace

 

[Mike M.T.]

It could be a problem with the date format and the declaration of the array
as Variant. A Variant can only accept assignment of data having a valid
VarType. If the date it's not in the US format, errors may occur.

 

[Scott McDaniel]

Try adding this line BEFORE your With block (immediately under the line
reading intArrayCount=0)

ReDim aryTestArray(0)

Another poster commented on using the Variant datatype ... if your array
will contain ONLY date datatypes, then you can declare your array variable
with a Date datatype:

Dim aryTestArray() As Date

This could increase performance, but it won't solve your problem.

 

[Tim Ferguson]

When I
traced through the code I discovered that for the lines
with aryTestArray(intArrayCount) it says <subscript out of
range>

1) what is the value of intArrayCount when it stops, and what is the value
of UBound(aryTestArray)? I guessed that
redim preserve aryTestArray(0)
would fail, but in fact it's fine.

2) wouldn't it be easier to use a Collection? Since the recordset is
unsorted, then you don't gain anything by using an array except a load of
hard work.

3) and wouldn't it be much easier and quicker to use a query? What about

SELECT ALL Holiday_Stat_Date
FROM Holiday
WHERE Forms![Startup]![Last_YTD_Start]<=Holiday_Stat_Date
AND Holiday_Stat_Date <= Forms![Startup]![Q4_End]


HTH


Tim F

 

[Dirk Goldgar]

In the function FillIndefArray below, when I run it the
code runs through but I get inaccurate results. When I
traced through the code I discovered that for the lines
with aryTestArray(intArrayCount) it says <subscript out of
range>

How do I fix that? Is it not recognizing the end of file?

Candace

*********************************************

Function FillIndefArray()
Dim dbSample As DAO.Database
Dim rstSample As DAO.Recordset
Dim intArrayCount As Integer
Dim aryTestArray() As Variant

Set dbSample = CurrentDb()
Set rstSample = dbSample.OpenRecordset("Holiday")

intArrayCount = 0

' Fill the array.
With rstSample
.MoveFirst
Do Until rstSample.EOF
If (Forms![Startup]![Last_YTD_Start] <= !
[Holiday_Stat_Date]) And (Forms![Startup]![Q4_End] >= !
[Holiday_Stat_Date]) Then
'Make place for the start date and
' fill it with the start date in SQL format.
ReDim Preserve aryTestArray(intArrayCount)
aryTestArray(intArrayCount) = !
[Holiday_Stat_Date]
' Increase the array index for the next
Redim
intArrayCount = intArrayCount + 1
End If
.MoveNext
Loop
.Close
End With

'Return the array with dates
FillIndefArray = aryTestArray

End Function

It's not obvious to me what's wrong, unless this code is in an ADP, as
oppose to an MDB. It's not, is it? Do you have error-handling
enabled -- that is, do you have one of the VB options, "Break on
Unhandled Errors" or "Break on All Errors", checked? Have you veried
that you do in fact have a table or query named "Holiday" -- not, for
example, "Holidays"? If necessary, set a breakpoint and step through
the code, and after each line use the Immediate and Local windows to
investigate what the result of that line's execution is.

 

[Can]

I did, I still get the error. ;-\

Now what?

-----Original Message-----

Try adding this line BEFORE your With block (immediately under the line
reading intArrayCount=0)

ReDim aryTestArray(0)

Another poster commented on using the Variant datatype ... if your array
will contain ONLY date datatypes, then you can declare your array variable
with a Date datatype:

Dim aryTestArray() As Date

This could increase performance, but it won't solve your problem.

In the function FillIndefArray below, when I run it the
code runs through but I get inaccurate results. When I
traced through the code I discovered that for the lines
with aryTestArray(intArrayCount) it says <subscript out of
range>

How do I fix that? Is it not recognizing the end of file?

Candace

*********************************************

Function FillIndefArray()
Dim dbSample As DAO.Database
Dim rstSample As DAO.Recordset
Dim intArrayCount As Integer
Dim aryTestArray() As Variant

Set dbSample = CurrentDb()
Set rstSample = dbSample.OpenRecordset("Holiday")

intArrayCount = 0

' Fill the array.
With rstSample
.MoveFirst
Do Until rstSample.EOF
If (Forms![Startup]![Last_YTD_Start] <= !
[Holiday_Stat_Date]) And (Forms![Startup]![Q4_End] >= !
[Holiday_Stat_Date]) Then
'Make place for the start date and
' fill it with the start date in SQL format.
ReDim Preserve aryTestArray (intArrayCount)
aryTestArray(intArrayCount) = !
[Holiday_Stat_Date]
' Increase the array index for the next
Redim
intArrayCount = intArrayCount + 1
End If
.MoveNext
Loop
.Close
End With

'Return the array with dates
FillIndefArray = aryTestArray

End Function

.