Hi All,

I have a excel userform which has some combo boxes and list boxes and I want to
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 be
populated from a sql table called Region_Mapping and my first list box should be
linked to the same sql table but it should populate the countries on the basis
of the region which user will select in the combo box. For eg : In my sql table
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 box
one should get populated with the above mentioned countries with check boxes so
that user can remove the unwanted countries while extracting data. Please
expedite.

What specific part of this are you having a problem with ?

Feel free to expedite *that*...

Tim


On Apr 12, 9:02*pm, abhay-547 wrote:
Hi All,

I have a excel userform which has some combo boxes and list boxes and I want to
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 be
populated from a sql table called Region_Mapping and my first list box should be
linked to the same sql table but it should populate the countries on the basis
of the region which user will select in the combo box. For eg : In my sql table
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 box
one should get populated with the above mentioned countries with check boxes so
that user can remove the unwanted countries while extracting data. Please
expedite.

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


On Apr 12, 9:02*pm, abhay-547 wrote:
Hi All,

I have a excel userform which has some combo boxes and list boxes and I
want
to
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 be
populated from a sql table called Region_Mapping and my first list box
should
be
linked to the same sql table but it should populate the countries on the
basis
of the region which user will select in the combo box. For eg : In my sql
table
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
box
one should get populated with the above mentioned countries with check
boxes
so
that user can remove the unwanted countries while extracting data. Please
expedite.
Hi All,

I have a excel userform which has some combo boxes and list boxes and I want to
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 be
populated from a sql table called Region_Mapping and my first list box should be
linked to the same sql table but it should populate the countries on the basis
of the region which user will select in the combo box. For eg : In my sql table
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 box
one should get populated with the above mentioned countries with check boxes so
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 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"
Call GetSQLData(stSQL, k, RTNData)

' 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 .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.

abhay-547 wrote on 04/13/2010 00:02 ET :
Hi All,

I have a excel userform which has some combo boxes and list boxes and I want
to
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 be populated from a sql table called Region_Mapping and my first list box
should be linked to the same sql table but it should populate the countries
on
the basis of the region which user will select in the combo box. For eg : In
my
sql table 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
box
one should get populated with the above mentioned countries with check boxes
so
that user can remove the unwanted countries while extracting data. Please
expedite.
Hi All,

Finally I got it. The issue was with the below sql statement .i.e instead of
region I had mentioned country in the same.

SELECT DISTINCT Country FROM Region_Mapping WHERE Region = '" & ComboBox1.Value
& "' "

Any how. Thanks a lot for your help.