Combobox Populating from recordset

[eggpap]

Hello,

I got the following code from the web:

Private Sub UserForm_Initialize()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String, stConn As String, stSQL As String
Dim xlCalc As XlCalculation
Dim vaData As Variant
Dim k As Long

'In order to increase the performance.
With Application
xlCalc = .Calculation
Calculation = xlCalculationManual
EnableEvents = False
ScreenUpdating = False
End With

'Instantiate the Connectionobject.
Set cnt = New ADODB.Connection

'Path to the database.
stDB = ThisWorkbook.Path & "\ImportExport.mdb"
'Create the connectionstring.

stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & stDB
& ";"

'Create the SQL-statement.

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 = adUseClient 'Necesary for creating disconnected
recordset.
Open stConn 'Open connection.
'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)
End With

'Close the connection.
cnt.Close
'Restore the settings.
With Application
Calculation = xlCalc
EnableEvents = True
ScreenUpdating = True
End With
'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing
End Sub

I open the userform containing the combobox from the workbook_open
event and all performs correctly but I get an empty combobox.

If you like, I have attached the test.zip file containing both the
workbook and the mdb database.

I know I can get the same result in many ways, but I'ld like to
experiment this one. In another userform, infact, I have used the cb
rowsource property without problems.

Many thanks,
Emiliano


+-------------------------------------------------------------------+
|Filename: test.zip |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=106|
+-------------------------------------------------------------------+

 

[joel]

I'm confused??????????

First you have a listbox not a combobox (both can have multiple coluns).
Second you have no code to put the datta into the box. A initialize form
function doesn't move data it is only the routine that is called when a form
is opened. What line of code isn't working?

 

[eggpap]

I'm confused??????????

First you have a listbox not a combobox (both can have multiple
coluns).
Second you have no code to put the datta into the box. A initialize
form
function doesn't move data it is only the routine that is called when a
form
is opened. What line of code isn't working?

Thanks,

the userform contains both a listbox and a combobox, but the code
refers to the cbselect control (a combobox). You are right: I have not
reported the statement:

*me.cbselect.list=vadata*

after the code rows

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

NOW IT'S OK.

regards,
Emiliano