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 OLEDB:Database 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

Excel 2003 on Vista HP System - can use VBA
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=87598

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


"eggpap"
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 OLEDB:Database 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
Excel 2003 on Vista HP System - can use VBA