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

[abhay_547]

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.

[abhay_547]

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.

[abhay_547]

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.

[abhay_547]

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.

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

[abhay_547]

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.

[abhay_547]

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.