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