How to Populate One ListBox Based On Another ListBox

[irisbutnaru1]

Hello everybody!

I have created a UserForm with 2 listboxes (comboboxes). The data for
those combo's is from an Access Database ( a table with 2 coloms).

I work with WORD/Access XP.

colomn 1: countries
colomn 2: cities

for example:
USA NewYork
France Paris
USA Washington

I need a macro that will enable me to choose a value in combobox 1
(for example: USA) and in combobox 2 I will get only the cities of USA
(for example: New-York, Washington... end so on).

I will be very grateful for your HELP!!

Iris

 

[Jay Freedman]

See http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Helmut Weber]

Hi Iris,

in prinicple, there is not enough information
to do it the right way, as it is done in relational databases.
You need a relation between the two lists,
some matchcode which connects the values in list 1 and in list 2,
that is, a list 3. But that is leading us too far.

It would be easier to do all in Excel.

However, there are workarounds, with drawbacks, of course.
Like that:

Private Sub CommandButton1_Click()
Me.ListBox1.Clear
Me.ListBox2.Clear
With Me.ListBox1
.AddItem "USA"
.AddItem "France"
.AddItem "USA"
.AddItem "USA"
.AddItem "France"
End With
With Me.ListBox2
.AddItem "Washington"
.AddItem "Paris"
.AddItem "Boston"
.AddItem "New York"
.AddItem "Marseille"
End With
End Sub
' --------------------------------------
Private Sub CommandButton2_Click()
Dim x As Long
Dim y As Long
Dim z As Long
Dim s As String
x = Me.ListBox1.ListIndex
If x = -1 Then
MsgBox "nothing selected"
Exit Sub
End If
s = Me.ListBox1.List(x) ' USA, France
y = Me.ListBox2.ListCount - 1
For z = y To 0 Step -1
If Me.ListBox1.List(z) <> s Then
Me.ListBox2.RemoveItem (z)
End If
Next
End Sub


Commandbutton1 clears and fills the lists.

Commandbutton2 clears list 2 from all values,
which don't have a corresponding value = country
in list 1.
After that you have to fill both lists again!
Otherwise you'll soon know what the drawbacks are.

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[Helmut Weber]

Oops,

I see now, it was about comboboxes, not listboxes.
Ask gain, if you can't adjust the code.

Hmm, I don't think a combobox is a kind of listbox...

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[iris]

Thank you for you answers.

I have a code that works, but it have a little problem... if I open the
first combobox twice... The first time dosn't clear... I think that yo will
understand only if you try to actually activate the code, so I put the code
here with the hope that you will try to use it and see what the problem is...

to use this code you need to open a userform with 2 comboboxes and an access
database with 2 colomns.

Option Explicit

Private Sub cboCountry_Exit()

Dim dbDatabase As Database
Dim rst As Recordset
Dim y As Integer

Dim strSQL As String

Set dbDatabase = OpenDatabase("C:\phone.mdb")
Set rst = dbDatabase.OpenRecordset("SELECT DISTINCT city FROM tblAll WHERE
Country = '" & cboCountry.Text & "' ORDER BY city;", dbOpenSnapshot)

y = 0

'This code populates the combo box with the values in the CompanyName field.
With rst
On Error Resume Next
'strSQL = "Select city " & _
' "FROM tblAll " & _
' "WHERE Country = '" & cboCountry.Text & "' " & _
' "ORDER BY city;"

Do Until .EOF
cboCity.AddItem (y)
cboCity.ColumnCount = 3
cboCity.BoundColumn = 3
cboCity.ColumnWidths = "6 in"
cboCity.AutoTab = True
cboCity.Column(0, y) = rst.Fields("city")
.MoveNext
y = y + 1
Loop
End With
End Sub

Private Sub UserForm_Initialize()

Dim dbDatabase As Database
Dim rs As Recordset
Dim i As Integer

' This code activates the Database connection. (Change the path to reflect
your database).
Set dbDatabase = OpenDatabase("C:\phone.mdb")

' This code opens the Managers table. (Change the Table to reflect the
desired table).
Set rs = dbDatabase.OpenRecordset("SELECT DISTINCT Country FROM tblAll ORDER
BY Country;", dbOpenSnapshot)

i = 0

'This code populates the combo box with the values in the CompanyName field.
With rs
On Error Resume Next
Do Until .EOF
cboCountry.AddItem (i)
cboCountry.ColumnCount = 3
cboCountry.BoundColumn = 3
cboCountry.ColumnWidths = "6 in"
cboCountry.AutoTab = True
cboCountry.Column(0, i) = rs.Fields("country")
.MoveNext
i = i + 1
Loop
End With
End Sub