function returning an array 3

[lanelouna]

hello all
i am trying to return an array from a function
however it is not working
i am not sure why
here are the functions, the functions that returns the array and he call function
any comments?any ideas?
thank you
Lina



Option Compare Database
Function CreateResArray() As Variant
Dim db As Database
Set db = CurrentDb

Dim mesReseaux() As String
Dim j As Integer
j = 0
Dim RS As DAO.Recordset

Set RS = db.OpenRecordset("T106 Importation Reseau", dbOpenDynaset)
Do Until RS.EOF
mesReseaux(j) = RS!Réseau
j = j + 1
RS.MoveNext
Loop
CreateResArray = mesReseaux
End Function


the call function

Function GetRes(i) As String
Dim db As Database
Set db = CurrentDb

Dim reseauxArray As Variant

reseauxArray = CreateResArray()
GetRes = reseauxArray(i)
End Function

 

[MichaelRed]

Function CreateResArray() As Variant

    Dim db   As DAO.Database        'Add explicit ref to DAO access
    Set db = CurrentDb
 
    Dim mesReseaux() As String      'Note the array is not dimensioned
    Dim Jdx As Integer

    Jdx = 0
    Dim RS As DAO.Recordset

    'Replaced the recordset table name just to have a "local" table _
     to use for illustration
'    Set RS = db.OpenRecordset("T106 Importation Reseau", dbOpenDynaset)
    Set RS = db.OpenRecordset("tblHolidates", dbOpenDynaset)

    'Added to assure the record count is available
    RS.MoveLast

    'Added to ACTUALLY produce an array of the proper size
    ReDim mesReseaux(RS.RecordCount)

    'Add to restore RS to start
    RS.MoveFirst

    Do Until RS.EOF
        mesReseaux(Jdx) = RS!Holidate
        Jdx = Jdx + 1
        RS.MoveNext
   Loop

    'Added Elipsis to Assure the ARRAY is returned
    CreateResArray = mesReseaux()

End Function
'the call function
Function GetRes(i) As String

'    these are not used and not needed
'    Dim db   As Database
'    Set db = CurrentDb

    Dim reseauxArray As Variant
 
    reseauxArray = CreateResArray()

    'This implies yet another procedure to retrieve the single element
    GetRes = reseauxArray(i)

    'This added to just illustrate the array is returned
    Dim Idx As Integer
    While Idx <= UBound(reseauxArray)
        Debug.Print Idx, eseauxArray(Idx)
        Idx = Idx + 1
    Wend

End Function

Note. This is NOT a reccomendation of the approach, just a minimal 'correction' to illustrate a procedure and comment on the actual errors in the original.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[lanelouna]

Hello Micheal
thanks for your help, however istill get the same error
on the line
reseauxArray = CreateResArray()
in the call function

it gives me: compilation error
variable or procedures expected, and not a module
any ideas?
thanks (you ve been a great lately)
Lina

 

[lanelouna]

i know i know
i didn't pay attention to the name of the module
it was the same as the name of the function, forgot to add "fun"
thanks a million
for the million times!
Lina

 

[lanelouna]

i meant to say great help!
thanks

 

[MK10]

Is someone with your skills really unemployed?

Great posting!

 

[MichaelRed]

MK10,

sigh - yes, ... any hints / leads ... appreciated


MichaelRed
m.red@att.net

Searching for employment in all the wrong places