Passing Array to sub as parameter

[Alain]

Hi to all,

I am trying to pass an array as a parameter to a sub to finish populating a
opened recordset but I get an error of "subscript out of range" fix it but
not quite sure, and the second error is a type mismatch, can anyone tell me
what is wrong here
PS. I need to build 12 subs to I do not have to manipulate the code if a
fiscal yr is changing

Private arrSngMth() As Variant declared at top of module

Private Sub BudAvrMar(rst As DAO.Recordset, rst2 As DAO.Recordset, sngInc As
Single, ParamArray arrSngMth() As Variant)
'rst = main data recordset
'rst2 = budget table
'arrSngMth = array


Dim varArr As Variant
varArr = arrSngMth

'testing array------NOT working not sure why!!!
If UBound(varArr) > 0 Then
ReDim Preserve varArr(UBound(varArr) - 1)
End If

With rst2
For i = LBound(arrSngMth) To 30
sngTot1 = sngTot1 + arrSngMth(i)
Next i
![Avril] = sngTot1

the error is being raised at "sngTot1 = sngTot1 + arrSngMth(i)"

Thanks

 

[Daryl S]

Alain -

Is sngTot1 a field in the rst recordset? If so, change this to
!sngTot1 = !sngTot1 + arrSngMth(i)

 

[Alain]

Hi Daryl

sngTot1 is just a variable to hold totals
the array id being populate thru this code:

For i = intZ To intDaysLeft
arrSngMth(i) = curAmt
Next i

intz min value is 1 and intDaysleft max value is 366, varies in each main loop
I reset the array to zeros at the beginning of each loop
ReDim arrSngMth(366)

really nothing big here

Alain -

Is sngTot1 a field in the rst recordset? If so, change this to
!sngTot1 = !sngTot1 + arrSngMth(i)

--
Daryl S

Hi to all,

I am trying to pass an array as a parameter to a sub to finish populating a
opened recordset but I get an error of "subscript out of range" fix it but
not quite sure, and the second error is a type mismatch, can anyone tell me
what is wrong here
PS. I need to build 12 subs to I do not have to manipulate the code if a
fiscal yr is changing

Private arrSngMth() As Variant declared at top of module

Private Sub BudAvrMar(rst As DAO.Recordset, rst2 As DAO.Recordset, sngInc As
Single, ParamArray arrSngMth() As Variant)
'rst = main data recordset
'rst2 = budget table
'arrSngMth = array


Dim varArr As Variant
varArr = arrSngMth

'testing array------NOT working not sure why!!!
If UBound(varArr) > 0 Then
ReDim Preserve varArr(UBound(varArr) - 1)
End If

With rst2
For i = LBound(arrSngMth) To 30
sngTot1 = sngTot1 + arrSngMth(i)
Next i
![Avril] = sngTot1

the error is being raised at "sngTot1 = sngTot1 + arrSngMth(i)"

Thanks

 

[Daryl S]

Alain -

What is the value of i when you get the error? Could there be fewer
elements in arrSngMth than 30?

--
Daryl S

Hi Daryl

sngTot1 is just a variable to hold totals
the array id being populate thru this code:

For i = intZ To intDaysLeft
arrSngMth(i) = curAmt
Next i

intz min value is 1 and intDaysleft max value is 366, varies in each main loop
I reset the array to zeros at the beginning of each loop
ReDim arrSngMth(366)

really nothing big here

Alain -

Is sngTot1 a field in the rst recordset? If so, change this to
!sngTot1 = !sngTot1 + arrSngMth(i)

--
Daryl S

Hi to all,

I am trying to pass an array as a parameter to a sub to finish populating a
opened recordset but I get an error of "subscript out of range" fix it but
not quite sure, and the second error is a type mismatch, can anyone tell me
what is wrong here
PS. I need to build 12 subs to I do not have to manipulate the code if a
fiscal yr is changing

Private arrSngMth() As Variant declared at top of module

Private Sub BudAvrMar(rst As DAO.Recordset, rst2 As DAO.Recordset, sngInc As
Single, ParamArray arrSngMth() As Variant)
'rst = main data recordset
'rst2 = budget table
'arrSngMth = array


Dim varArr As Variant
varArr = arrSngMth

'testing array------NOT working not sure why!!!
If UBound(varArr) > 0 Then
ReDim Preserve varArr(UBound(varArr) - 1)
End If

With rst2
For i = LBound(arrSngMth) To 30
sngTot1 = sngTot1 + arrSngMth(i)
Next i
![Avril] = sngTot1

the error is being raised at "sngTot1 = sngTot1 + arrSngMth(i)"

Thanks