By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,284 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,284 IT Pros & Developers. It's quick & easy.

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

P: 11
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.BoroughName 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
...
Aug 22 '08 #1
Share this Question
Share on Google+
3 Replies


100+
P: 167
We discussed very similar question here
Aug 22 '08 #2

P: 11
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.ListIndex." But if I give a correct number it works OK. Can anybody give some suggestion about the program? Thanks in advance.


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

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

-----------------------------
Aug 26 '08 #3

100+
P: 167
try changing the WHERE part like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboCity_AfterUpdate()
  2. Me.cboArea.RowSource = "SELECT DISTINCTROW AreaID, AreaName FROM Area WHERE (CityID = " & Me.cboCity.ListIndex & ")"
  3. Me.cboArea.Requery
  4. End Sub
Aug 26 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.