Populate a Listbox with values from a field in the same Table.

[d2rd]

I amworking on a database that shows a MetropolitanStatistical Area (MSA) and its member cities. In my database each MSA group hasrecords for individual cities and one Summary Record that aggregates data forthe MSA (this is how the original database is structured). I havea field, MSACities, that contains the names of eachmember city for the MSA. Another field, MSASummary, indicatesthat it is a summary field. I used Excel to put all of the member city names ina field on the summary MSA record. It is separated by semicolons. (I know thisis not the correct way to do it.)

My form contains alistbox, MSACities, where I need to display the cities associated with eachMSA. The MSA Name and other data fields are text boxes that display the current record. Each textbox is bound to one field displaying the current value in that field. I have that part worked out.



As the user clicks the navigation buttons below the Municipalities (Cities) listbox a new record becomes current. If the current record is a city record then nothing should appear in the listbox. When the pointer selects an MSA Summary record the municipalities (cities) associated with the MSA should appear in the listbox. Each city should only appear once, one per line. Ideally the user should be able to double click the city name and popup a window with data on that city. When I move to the next MSA record I want the listbox to refresh with the member cities associated with the next MSA.


I was able write aquery that selects only the records that are flagged as MSA Summary records ("MSASummary = True").When I bind the listbox to the MSACities field I was able to get the cities toshow up in the textbox but they are not on separate lines. They are also not selectable. I can not figure outhow to specify the record source for the list box.

1. How do I get all ofthe member cities of the current MSA to show up in the listbox?
2. How do I get thelist box to refresh with each new summary record?

I havesearched online for days but cannot find the solution that applies to my case.I have tried code and SQL statements but cannot get anything to work. I haveattached an image and a mockup of the database.

I am a novice atthis and have gotten myself into what seems like the deep water. Help!



I have attached anannotated screenshot to that hopefully clarifies what I am trying to do.
MSA Municipalities Problem Screenshot.pdfMSA_Municipalities_Problem.zip

[June7]

Cross post http://forums.aspfree.com/microsoft-...le-538769.html