Tim Williams wrote on 04/13/2010 18:12 ET :
What specific part of this are you having a problem with ?
Feel free to expedite *that*...
Tim
Hi All,
I have a excel userform which has some combo boxes and list boxes and I want t
populate this boxes with the data which I have on my sql server database tables
Just to give an example. My first combo box is for region which needs to b
populated from a sql table called Region_Mapping and my first list box should b
linked to the same sql table but it should populate the countries on the basi
of the region which user will select in the combo box. For eg : In my sql tabl
I have following countries which are mapped against America
1) Argentina
2) Brazil
3) Mexico
4) Canada.
Now if the user selects the region America in Combo box one then the List bo
one should get populated with the above mentioned countries with check boxes s
that user can remove the unwanted countries while extracting data.
I have the below code so far.
I have below mentioned code in my userform .i.e named as frmdata.
Code:
Option Explicit
Private Sub ComboBox1_Change()
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 RTNData As Variant
Dim k As Long
Set cnt = New ADODB.Connection
stConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Securit
Info=False;Initial Catalog=meta_data;Data Source=DB-77716EFB0314\SQLEXPRESS"
cnt.ConnectionString = stConn
'your SQL statement
stSQL = "SELECT DISTINCT Region FROM Region_Mapping"
Call GetSQLData(stSQL, k, RTNData)
' I have Country Column in my sql table which should get populated on th
basis of Region Combo selection and I need the checkboxes with country names i
listbox.
With cnt
.CursorLocation = adUseClient 'Necesary for creating disconnecte
recordset.
.Open stConn 'Open connection.
'Instantiate the Recordsetobject and execute the SQL-state.
Set rst = .Execute(stSQL)
End With
With rst
Set .ActiveConnection = Nothing 'Disconnect the recordset.
k = .Fields.Count
'Populate the array with the whole recordset.
vaData = .GetRows
End With
'Close the connection.
cnt.Close
'Manipulate the Combobox's properties and show the form.
With frmdata
With .ListBox1
.Clear
.BoundColumn = k
.List = Application.Transpose(RTNData)
.ListIndex = -1
End With
End With
'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing
End Sub
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
End Sub
Private Sub CommandButton6_Click()
Unload Me
End Sub
Private Sub ListBox1_Click()
End Sub
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
Set cnt = New ADODB.Connection
stConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=meta_data;Data Source=DB-77716EFB0314\SQLEXPRESS"
cnt.ConnectionString = stConn
'your SQL statement
stSQL = "SELECT DISTINCT Region FROM Region_Mapping"
' I have Country Column in my sql table which should get populated on the
basis of Region Combo selection and I need the checkboxes with country names in
listbox.
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
Set .ActiveConnection = Nothing 'Disconnect the recordset.
k = .Fields.Count
'Populate the array with the whole recordset.
vaData = .GetRows
End With
'Close the connection.
cnt.Close
'Manipulate the Combobox's properties and show the form.
With frmdata
With .ComboBox1
.Clear
.BoundColumn = k
.List = Application.Transpose(vaData)
.ListIndex = -1
End With
End With
'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing
End Sub
Note : While establishing connection to sql server database (as I am doing in
the above code) I want to mention User ID and password in my code.
And I have following code in Module1
Code:
Sub GetSQLData(stSQL As String, k As Long, RTNData As Variant)
stSQL = "SELECT DISTINCT Country FROM Region_Mapping"
End Sub
Now I am facing following error while trying to launch my userform.
Error Message :
Runtime Error '381' Could not set the list Property. Invalid Property array
Index.
Please help I am unable to figure out that I am making a mistake at which point
in the above code.
Thanks for your help in advance.
