Question about listboxes and VBA

[Lucas83]

Hi everyone,

I'm a beginner with VBA, so maybe this question is so silly but I'm pulling my hair out. This is the situation: I have two tables. First table is called Countries and it has three records: Spain, Greece and Italy, and two columns: IdCountry and Country. Second table is called Cities and it has six records: Almeria, Sevilla, Atenas, Creta, Patral, Roma and Sicilia (by the way, City names are in spanish) and three columns: IdCity, City and IdCountry, so I can link both tables by column IdCountry. I have an unbound form with two listbox. First listbox, called List0, should show country names and the second list, List2, should show only the cities that belong to a country when I click in one country name from List0, so If I click Spain in List0, List2 should show Almeria and Sevilla, so on.

I tried this by myself but the result is not what I expected. I created List0 and List2. I set as rowsource, from List0, Country column from country table and City column from city table. And the I choose Afterupdate event and I put this line:



Private Sub Lista0_AfterUpdate()

List2 = List0.Column(1)

End Sub

The result is as follows: List0 shows 6 records: Spain, Spain, Italy, Italy, Greece, Greece, and List2 shows 6 records: Almeria,Sevilla,Sicilia,Puglia,Atenas and Patral.

I've been looking for a solution but I haven't found anything, and I would like to solve this little problem because my next step would be to creat another listbox with different plot in each city. So If I click in one country, list2 would show all cities in that country, and list3 (the new list) would show all plots related to a city, when I would click in one city name from list2. Sorry if this explanation is confused but It's diffucult to explain an Access scenario in detail . Anyone has any solution? Thank you in advance!.

[PianoMan64]

Hey Lucas83,

Here is a code example that you can use do what it is that you need to do:

Copy and Paste the code into your code behind the form.

Private Sub List0_Change()
  Me.ProjectCodeLookup = ""
End Sub
Private Sub List0_LostFocus()
    Me.List2.RowSource = "SELECT City " & _
                         "FROM Cities " & _
                         "WHERE ((CountryId) = " & Me.List0 & ");"
 
End Sub

If this solves your issue, please mark the thread as SOLVED.

Thanks,

Joe P.

[Lucas83]

Thank you very much!! ^^ Only one last question when you use:

"SELECT City " & _

"& _" means that you get all columns from that table?

[TG_W]

Lucas,

No, it doesn't. The " _" is simply telling the code to keep running on the next line. It helps to make the code easier to read, or it simply breaks up a long string to prevent from having to scroll right to see the rest of it.