Populating a combobox by the getrows method

[eggpap]

Hello,

with the following code I populate one combobox on a userform of
Excel.

Suppose to have a source table of N fields and M records, so the sub
would populate the combobox with M rows and N columns. What happens is
that if the recordset contains more than one record the combobox
populates correctly, the vadata variable gets bi-dimensional and I have
a list of M rows and N columns, like the source table. Viceversa, when
the recordset contains only one record, vadata gets monodimensional and
the combobox populates transposed of one column and N rows instead of
one row x N columns.

In the case of only one record I have tried to use the (.getrows)
statement, instead of application.transpose(.getrows) but the result is
the same. I have also tried to redim vadata preserving the data, but I
get a "index out of interval" error.

I know I could use other ways to populate the combobox, but I am
curious to understand why this one fails.

Thanks, Emiliano

'
' piece of code
'
stSQL = "SELECT Risorse.CID, Risorse.RepUtil, Risorse.ImpUtil,
Risorse.Profilo, " _
& "Risorse.Cognome, Risorse.Nome FROM Risorse WHERE
(((Risorse.Cessato) = True)) " _
& "ORDER BY Risorse.Cognome, Risorse.Nome;"

With cnt
CursorLocation = 3
Provider = "Microsoft.jet.OLEDB.4.0"
Properties("Data Source") = stDB
Properties("Jet OLEDBatabase Password") = PWORD
Open
'Instantiate the Recordsetobject and execute the SQL-state.
Set rst = .Execute(stSQL)
End With

With rst
MoveFirst 'To retrieve the Recordset.
'Set .ActiveConnection = Nothing 'Disconnect the recordset.
k = .Fields.Count

'Populate the array with the whole recordset.
vaData = Application.Transpose(.GetRows)

'statementes to populate the combobox
With Me.cbSelect
Clear
ColumnCount = k
BoundColumn = (k - 1)
List = vaData
ListIndex = -1
End With

 

[Jim Cone]

Change .List to .Column ...
.Column = vaData
--
Jim Cone
Portland, Oregon USA


"eggpap" <[email protected]>
wrote in message
Hello,
with the following code I populate one combobox on a userform of Excel.
Suppose to have a source table of N fields and M records, so the sub
would populate the combobox with M rows and N columns. What happens is
that if the recordset contains more than one record the combobox
populates correctly, the vadata variable gets bi-dimensional and I have
a list of M rows and N columns, like the source table. Viceversa, when
the recordset contains only one record, vadata gets monodimensional and
the combobox populates transposed of one column and N rows instead of
one row x N columns.

In the case of only one record I have tried to use the (.getrows)
statement, instead of application.transpose(.getrows) but the result is
the same. I have also tried to redim vadata preserving the data, but I
get a "index out of interval" error.

I know I could use other ways to populate the combobox, but I am
curious to understand why this one fails.

Thanks, Emiliano

'
' piece of code
'
stSQL = "SELECT Risorse.CID, Risorse.RepUtil, Risorse.ImpUtil,
Risorse.Profilo, " _
& "Risorse.Cognome, Risorse.Nome FROM Risorse WHERE
(((Risorse.Cessato) = True)) " _
& "ORDER BY Risorse.Cognome, Risorse.Nome;"

With cnt
CursorLocation = 3
Provider = "Microsoft.jet.OLEDB.4.0"
Properties("Data Source") = stDB
Properties("Jet OLEDBatabase Password") = PWORD
Open
'Instantiate the Recordsetobject and execute the SQL-state.
Set rst = .Execute(stSQL)
End With

With rst
MoveFirst 'To retrieve the Recordset.
'Set .ActiveConnection = Nothing 'Disconnect the recordset.
k = .Fields.Count

'Populate the array with the whole recordset.
vaData = Application.Transpose(.GetRows)

'statementes to populate the combobox
With Me.cbSelect
Clear
ColumnCount = k
BoundColumn = (k - 1)
List = vaData
ListIndex = -1
End With

 

[eggpap]

Change .List to .Column ...
.Column = vaData

Thank you very much, however I'ld like to know why .List fails with one
record.
Do you have a reply ?

 

[Jim Cone]

One of them does an automatic transpose - check the Excel VBA help
file for List and Column.
--
Jim Cone
Portland, Oregon USA



"eggpap" <[email protected]>
wrote in message

Change .List to .Column ...
.Column = vaData

Thank you very much, however I'ld like to know why .List fails with one
record.
Do you have a reply ?

 

[marwan.hefnawy]

One of them does an automatic transpose - check the Excel VBA help
file for List and Column.
--
Jim Cone
Portland, Oregon  USA

"eggpap" <[email protected]>
wrote in message


Thank you very much, however I'ld like to know why .List fails with one
record.
Do you have a reply ?

Hi,
Try this as a debugging trial

Arr = .GetRows
MsgBox UBound(Arr, 1) & "," & UBound(Arr, 2)
vaData = Application.Transpose(Arr)
MsgBox UBound(vaData, 1) & "," & UBound(vaData, 2)

For Arr:
You will get the bounds of Arr as you expect (Number of
Fields-1,Number ofRecords -1)
In case of one record, you will get (Number of Fields-1,0)
Note that the getrows returns a ZERO based two dimentional array.

for vaData :
In case of One record you will get an error in the part UBound(vaData,
2)
Of course you expect to see (1,Number of Fields)
Note that the Transpose function returns a ONE based two dimentional
array
but this did not happen because the Transpose function returned a one
based one dimentional array rather than a two dimentional array as
expected.


As a workaround you can make the tranpose process manually in this
case by filling a new array item by item with the results from the
getrows array.