Solved: Populate a comboxbox and Listbox in a excel userform with an sql table data

[abhay_547]

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.

[abhay_547]

Hi All,

Please expedite.

[lucas]

What have you got so far?

[abhay_547]

Hi,

I have the below code so far. Following are things which I am looking for :

1) While establishing connection to sql server database I want to enter User ID and password in my code.
2) My Combobox gets populate with the data but it shows all instances of each region. For e.g. America is reflecting 4 times in my combo dropdown same in case of other regions. It should take only one instance of each region.
3) As mentioned earlier I want to add a list box to my userform which will get populated on the basis of the value which i had selected in my combo box for example If I select America in my combo box my list box should get populated with the countries which are mapped against America in my sql server table and they should get populated with check boxes in listbox.

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=sap_data;Data Source=DB-77716EFB0313\SQLEXPRESS"
cnt.ConnectionString = stConn
'your  SQL statement
stSQL = "SELECT Region FROM Region_Mapping"
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

Please expedite.

[abhay_547]

Hi,

Please expedite...

[abhay_547]

Hi All,

I want to populate my excel userform combo box with one of the sql database table column unique values. I am using the following sql statement in my code but it shows me all values present in the sql table column for e.g. I can see America region name 4 times in the drop down I want that the combo box should show it only once. Following is the sql statement used by me in my macro code :

stSQL = "SELECT Region FROM [Region_Mapping]"

Please expedite. Thanks for your help in advance.

[lucas]

You won't get help faster by posting the same question multiple times.

Threads merged.

Please be patient, someone will take a look at this when they are able.

[Tinbendr]

'your SQL statement
stSQL = "SELECT DISTINCT Region FROM Region_Mapping"

You'll have to copy this sub to the Combo1 exit event to populate the listbox without the Distinct filter for all the data.

Hope this helps!

[abhay_547]

Hi Tinbendr,

Can you please provide me an example for the post which you have made. I am unable to figure out what you are trying to say. Please..

[Tinbendr]

From your first message...

My first combo box is for region which needs to be populated from a sql table called Region_Mapping

I assumed you want to search the database for UNIQUE list of Americas, yes?

To get unique items from a Table, Use DISTINCT. This will get you the list without duplicates.

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.

Then, you use the value of the combobox to fill the listbox.

So, Double click your combobox, then select the Exit event (upper right)

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
End Sub

Inside this sub write another query to fill the listbox just like your example for the combobox.

[abhay_547]

Hi Tinbendr,

I am facing the following error when i use the below sql statement in my macro.

stSQL = "SELECT DISTINCT Region FROM Region_Mapping"

Error Encountered : "Run-time error '-2147217900 (80040e14)':
The text data type cannot be selected as DISTINCT because it is not comparable."

[Tinbendr]

I googled the error and found this.

The solution is convert text to VARCHAR(MAX).

CONVERT(VARCHAR(MAX), text_type_filedname)

Varchar(Max) is a LOB datatype and has a max size of 2GB.

[abhay_547]

Hi Tinbendr,

Can you please let me know how to you use the below code in my macro.

CONVERT(VARCHAR(MAX), text_type_filedname)

Can you please provide me with an example.

Thanks a lot for your help in advance.

[Tinbendr]

Try

SELECT DISTINCT CONVERT(VARCHAR(MAX), Region) FROM Region_Mapping

Max will be the maximum length of the field your trying to convert.

[abhay_547]

Hi Tinbendr,

Brilliant. You are just awesome. I have tried the sql statement provided by you in my code in the below way and It worked exactly as I wanted.

stSQL = "SELECT DISTINCT CONVERT(VARCHAR(50), Region) FROM Region_Mapping"

Now can you please provide me with an example code which works for the below i.e .
to populate list box on the basis of Combobox selection. Suppose user selects Americas in combo box then it should show only countries of America .i.e Argentina, Brazil, Mexico etc. (which are mentioned against Americas region in country column in same sql table).

Thanks a lot once again.

[Tinbendr]

to populate list box on the basis of Combobox selection. Suppose user selects Americas in combo box then it should show only countries of America .i.e Argentina, Brazil, Mexico etc. (which are mentioned against Americas region in country column in same sql table).

What we are talking about is called 'cascading combo/listboxes.

This may get over my head. I'm not a SQL expert. I had a similar problem and I kept researching it until I figured it out.

If you want to have many cascading combo boxes, I suggest a change to the code now.

In Module 1, put the SQL query code and make it a sub. Pass the SQL statement to the sub from the userform.

As I stated in message #10, when you select a item from the list in ComboBox1, use the Change event and call the SQL again using the selected item from ComboBox1 as the next filter. Something like

Private Sub ComboBox1_Change()
'other code here
stSQL = "SELECT DISTINCT CONVERT(VARCHAR(50), " & ComboBox1 & ") FROM Region_Mapping"
call GetSQLData(stSQL, K, RTNData)
'other code here

Then you assign the returned data to the next Combobox.

.combobox2.list = Application.Transpose(RTNData)

I don't have time to work out all the code. Maybe this weekend.

So, basically this prevents you from repeating the SQL code all through your userform.

[abhay_547]

Hi Tinbendr,

Sorry, but I am still confused about the statements which I have posted. I am attaching my excel workbook which has the userform and the code which I have so far. If you can please provide me an example in the same then It would be great I can replicate the same steps for rest of the combo and listboxes in my excel userform.

Please find the attachment.

Thanks for your help advance...

[abhay_547]

Hi Tinbendr,

Did you get the chance to look into the attached file..


Thanks a lot for your help in advance...

[abhay_547]

Hi Tinbendr,

Did you get the chance to look at the excel file which I had attached with my earlier post. I am awaiting for your reply. Please reply. If you can just show one example I can replicate it for all other combo and listboxes in my userform.

Thanks for your help in advance.

[Tinbendr]

Sorry for the delay.

Since you only have two dropdowns, the quick and easy solution is just to copy the SQL routine to the second dropdown (listbox).

If you have a lot of drop downs to populate, then I might write this different. I'd put the main SQL routine in Module1 and call it from each Change event.

Hope this helps!