Populating listbox on the basis of Combo box selection.

abhay_547 · May 18th, 2010, 12:06 AM

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 :

Code:

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 :

Code:

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.

gbianchi · May 18th, 2010, 09:38 AM

Hello..

I think this: SELECT DISTINCT Country FROM Region_Mapping WHERE Country = '" & ComboBox1.Value & "' "
is wrong...

You are looking for a specific country, when you said you are looking for lots of countries in a region. So IMHO you should have a region_id attached to every country somewhere..

abhay_547 · May 18th, 2010, 10:33 AM

Hi gbianchi,

Thanks a lot for your reply and I got your point. Can you please provide me with the code line as per your suggestion in post. So that I can use the same in my excel userform background.

Thanks a lot for your help in advance.

gbianchi · May 18th, 2010, 11:08 AM

I don't have anything to provide you.. In your database, is there anywhere where you connect the countries to the region???

That's the query you have to solve

abhay_547 · May 18th, 2010, 11:29 PM

Hi gbianchi,

No I don't have anything in my database which joins country and region column. There is only one thing common between both of them .i.e this two columns are in same table .i.e Region_Mapping. Can you please suggest me some solution for e.g. Do I need to insert any additional column in the above mentioned table. If yes, what kind of column and what data should it contain.

Please help...

abhay_547 · May 19th, 2010, 04:38 AM

Hi gbianchi,

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

Code:

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

Any how. Thanks a lot for your help.