Display limited items in the listbox according to the value of some field

[yltang]

Hi experts:
When I design the input form for customers' data, I divide the address into three parts: City, Borough, and RestAddress, where City and Borough are of the listbox type. I have two tables as follows. I would like to do the following. For example, if I choose "New York City" in the City listbox, then only the corresponding boroughs (Manhattan, Bronx, and Queens) of New York City will show up in the Borough listbox.

In the design view, I would like to try the SQL statement in the "Row Source" of Borough.Borough Name as follows, in which "?" means the content of the CityID field already inputted. So what is "?" ?

SELECT * from Borough WHERE CityID = ?



TABLES:

City:
ID CityName
1 New York City
2 Boston
...

Borough:
ID CityID BoroughName
1 1 Manhattan
2 1 Bronx
3 1 Queens
4 2 Holly Woods
5 2 Santa Monica
6 2 Pasadena
...

[hjozinovic]

We discussed very similar question here

[yltang]

Thanks for the reference. Well, there are a lot of discussions and the names of tables, variables, etc., are very confusing. Let me state my problem specifically. I have three tables: City, Area, and Customer:

City:
CityID: Autonumber
CityName: Text

Area:
AreaID: Autonumber
AreaName: Text
CityID: Number (Lookup: from City)

Customer:
CustomerID: AutoNumber
CustomerName: Text
CityID: Number (Lookup: from City)
AreaID: Number (Lookup: from Area)

I have also created a form named “Customer,” and it has basically 4 fields to fill: CustomerID, CustomerName, CityID, and AreaID, in which CityID and AreaID are both combo boxes with control names, "cboCity" and "cboArea", respectively.

I created the following program, but it doesn’t work. The system pops up a window asking me to input the value of variable "Me.cboArea.Lis tIndex." But if I give a correct number it works OK. Can anybody give some suggestion about the program? Thanks in advance.


------------------------------

Private Sub cboCity_AfterUp date()
Me.cboArea.RowS ource = "SELECT DISTINCTROW AreaID, AreaName FROM Area WHERE (CityID = Me.cboCity.List Index)"
Me.cboArea.Requ ery
End Sub

-----------------------------

[hjozinovic]

try changing the WHERE part like this:

Private Sub cboCity_AfterUpdate()
Me.cboArea.RowSource = "SELECT DISTINCTROW AreaID, AreaName FROM Area WHERE (CityID = " & Me.cboCity.ListIndex & ")"
Me.cboArea.Requery
End Sub