Populating listbox on the basis of Combo box selection.

[abhay_547]

Hi All,

I have the below code which works fine with Combo box. Now when try to replicate it for listbox it doesn't work properly.
Background : I have sql table called "Region_Mapping" with two columns one is country and another is region and as far as data in both columns is concerned is basically Region and country. For e.g. In country column i have country names like USA, Brazil, Mexico and Argentina and against same country names I have region as America in Region column.

What I want : I want to sql table data in my excel userform combobox and listbox .i.e In combo box I want to populate region names (which is already done and works fine) and in list box I want to populate country names on the basis of region selection. for e.g. If I select region as America in my Combo box then my listbox should get populated with only countries of America region .i.e USA, Brazil, Mexico and Argentina.

Code which I have so Far :
I have pasted the below code in Module 1 :

Public ADOCn As ADODB.Connection
Public adoRS As ADODB.Recordset
Public gstrConnString As String
Public Sub OpenDB()
gstrConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
			  & "Persist Security Info=False;Initial Catalog=XXXXXXX;" _
			  & "Data Source=XXXXXXXXXXXX"
	 
	  Set ADOCn = New ADODB.Connection
	  ADOCn.ConnectionString = gstrConnString
	  ADOCn.Open gstrConnString
	  End Sub

I have pasted the below code in userform background :

Public Sub LoadCombo()
	  Dim sSQL As String
	Set adoRS = New ADODB.Recordset
	sSQL = "SELECT DISTINCT Region FROM Region_Mapping"
	  adoRS.Open sSQL, ADOCn
	  ComboBox1.Clear
		Do While Not adoRS.EOF
			ComboBox1.AddItem adoRS(0)
		  adoRS.MoveNext
	  Loop
	  adoRS.Close
	  Set adoRS = Nothing
	  ADOCn.Close
	  Set ADOCn = Nothing
	  End Sub

Private Sub UserForm_Initialize()
OpenDB
LoadCombo
End Sub

Private Sub ComboBox1_Click()
Dim sSQL As String 
 Set adoRS = New ADODB.Recordset
sSQL = "SELECT DISTINCT Country FROM Region_Mapping WHERE Country = '" & ComboBox1.Value & "' "
adoRS.Open sSQL, ADOCn
ListBox1.Clear
	   Do While Not adoRS.EOF		
		   ListBox1.AddItem adoRS(0)			
		  adoRS.MoveNext		 
	  Loop	  
	  adoRS.Close	  
	  Set adoRS = Nothing 
End Sub

Issue : Now the issue is I have observed that it's not populating countries of those regions which have multiple countries mapped under it / mentioned against it in sql table for e.g. America has Mexico, Brazil, Argentina and USA mentioned against it in sql table but it populates the country name only for regions like Germany, UK & Japan as this regions have only one country mentioned against them in sql table.

Now this code should work for those regions which have multiple countries mapped against them in sql table.

Please help.

Thanks for your help in advance.