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

Printable View

04-12-2010, 12:52 PM
abhay_547

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

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.
04-13-2010, 01:18 PM
abhay_547

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

Hi All,

Please expedite.
04-13-2010, 01:23 PM
lucas

What have you got so far?
04-13-2010, 01:48 PM
abhay_547

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

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.

Code:

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.
04-13-2010, 09:04 PM
abhay_547

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

Hi,

Please expedite...
04-14-2010, 01:25 PM
abhay_547

Excel Combo box to be filled with sql table column data

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 :

Code:

stSQL = "SELECT Region FROM [Region_Mapping]"

Please expedite. Thanks for your help in advance.
04-14-2010, 01:57 PM
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.
04-16-2010, 11:04 AM
Tinbendr

Code:

'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!
04-16-2010, 11:40 PM
abhay_547

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

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..:think:
04-17-2010, 05:30 AM
Tinbendr

From your first message...

Quote:

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.

Quote:

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)

Code:

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.
04-17-2010, 11:19 PM
abhay_547

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

Hi Tinbendr,

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

Code:

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."
04-21-2010, 09:35 AM
Tinbendr

I googled the error and found this.

Quote:

The solution is convert text to VARCHAR(MAX).

Code:

CONVERT(VARCHAR(MAX), text_type_filedname)

Quote:

Varchar(Max) is a LOB datatype and has a max size of 2GB.
04-21-2010, 11:05 AM
abhay_547

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

Hi Tinbendr,

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

Code:

CONVERT(VARCHAR(MAX), text_type_filedname)

Can you please provide me with an example.

Thanks a lot for your help in advance. :bow:
04-21-2010, 05:52 PM
Tinbendr

Try

Code:

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

Max will be the maximum length of the field your trying to convert.
04-21-2010, 08:53 PM
abhay_547

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

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.

Code:

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. :bow:
04-22-2010, 07:25 PM
Tinbendr

Quote:

Originally Posted by abhay_547

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

Code:

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.

Code:

.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.
04-22-2010, 09:36 PM
abhay_547

1 Attachment(s)

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

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...:bow:
04-25-2010, 03:17 AM
abhay_547

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

Hi Tinbendr,

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

Thanks a lot for your help in advance...:bow:
04-28-2010, 11:58 AM
abhay_547

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

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. :bow:
05-01-2010, 11:50 AM
Tinbendr

1 Attachment(s)

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!
05-02-2010, 09:12 PM
abhay_547

1 Attachment(s)

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

Hi Tinbendr,

Thanks a lot for replying on my post. I have just now checked the file attached by you. I didn't find any sub routine in module1 as mentioned by you in your recent post. Apart from this We I try to run the form it shows me the following error.

[VBA]Run-time error '-2147217900 (80040e14)'
Incorrect syntax near ')'
[/VBA]

When I click on debug after facing the above error. It highlights below line

[VBA]Set rst = .Execute(stSQL)[/VBA]

Though when I debug it highlights above line but I think there is something wrong with the below sql statement.

[VBA]stSQL = "SELECT DISTINCT CONVERT(VARCHAR(50), " & ComboBox1 & ") FROM Region_Mapping"[/VBA]

I am re-attaching the macro file and the sql table Region_Mapping which I have copied into excel.

Thanks a lot for your help in advance. :bow:
05-05-2010, 11:28 AM
abhay_547

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

Hi Tinbendr,

Did you get the chance to look at the above post. I am awaiting for your reply.

Thanks a lot for your help in advance. :bow:
05-06-2010, 07:10 PM
abhay_547

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

Hi Tinbendr,

Did you get the chance to look at the above post. I am awaiting for your reply.

Thanks a lot for your help in advance. :bow:
05-11-2010, 08:41 PM
abhay_547

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

Hi Tinbendr,

Did you get the chance to look at the above post. I am awaiting for your reply.

Thanks a lot for your help in advance. :bow:
05-15-2010, 12:56 PM
Tinbendr

Hover over stSQL to make sure the string is being formed correctly.

If you do not select something from the listbox, it will error out.
05-15-2010, 08:46 PM
abhay_547

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

Hi Tinbendr,

Please help me with code line. I am bit confused. According the following code line should be used for populating countries in listbox1

[VBA]sSQL = "SELECT DISTINCT Country FROM Region_Mapping WHERE Country = '" & ComboBox1.Value & "' "[/VBA]

Can you please help me in replicating above code in such a way that the listbox get populated with countries on the basis of region selection.
for e.g. If I select America in combo box then it should show Brazil, Argentina, USA and Mexico countries which are mapped against america in sql table.

Thanks for your help in advance.:bow:
05-19-2010, 02:49 AM
abhay_547

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

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.