How to return an array from a function ? And handle the result after ?

[Johnjohn]

Hi there !

I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet.
Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.

So I try this :

*here is my function

[vba]Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant
Dim requetteSQL As String
Dim rst As New ADODB.Recordset
 
requetteSQL = "SELECT libelle " _
    & "FROM operation;" _

 
rst.Open requetteSQL, cnn
 
 
get_nom_operation = rst.GetRows
 
End Function[/vba]

* and here is the code that call the function

[vba]Dim res As Variant
 
Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1
 
For i = 1 To UBound(res)
  
    Set Obj = Me.Controls.Add("forms.CommandButton.1")
    With Obj
        .Name = "monButton" & i
        .Object.Caption = res(0,i)
        .Left = 14
        .Top = 25 * i
        .Width = 60
        .Height = 20
    End With
    
    'ajout de l'objet dans la classe
    Set Ge = New gere_event
    Set Ge.CButton = Obj
    Collect.Add Ge
    i = i + 1

 
Next[/vba]

But it doesn' work, and I don't know why...

The error doesn't show where the code is stoped, but only show the call to display this form.

someone could show me some way to find the solution please ?

Thanks a lot.

[Paul_Hossler]

Just a thought, but arrays are usually 0 based (unless you use Option Base 1) so maybe

[VBA]
For i = Lbound To UBound(res)
[/VBA]

Paul

[Johnjohn]

Thanks but it doesn't work.

I try :

[VBA]For i = LBound(res) To UBound(res)[/VBA]

and get an "incompatibility of type" (this is my own error message translation)

[Bob Phillips]

[vba]

Dim res As Variant

Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1

For i = LBound(res, 2) To UBound(res, 2)

Set Obj = Me.Controls.Add("forms.CommandButton.1")
With Obj
.Name = "monButton" & i
.Object.Caption = res(LBound(res, 1),i)
.Left = 14
.Top = 25 * i
.Width = 60
.Height = 20
End With

'ajout de l'objet dans la classe
Set Ge = New gere_event
Set Ge.CButton = Obj
Collect.Add Ge
i = i + 1

Next
[/vba]

Whyare you maually incrementing i within the loop?

[Johnjohn]

Because I was beleving that "Next" don't do it. I remove this line. But I get the same error.

Thanks anyway

How do you do when you need to do some request and handle many results ? Maybe I do it in a wrong way ? Or you never put it in a function maybe ?