Combobox Populating from recordset
 

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|
+-------------------------------------------------------------------+

--
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=84562

Combobox Populating from recordset
 

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" wrote:


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|
+-------------------------------------------------------------------+

--
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=84562

Combobox Populating from recordset
 

joel;302362 Wrote:
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


--
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=84562