I am working on a database that shows a Metropolitan Statistical Area (MSA) and its member cities. In my database each MSA group has records for individual cities and one Summary Record that aggregates data for the MSA (this is how the original database is structured).
I have a field, MSACities, that contains the names of each member city for the MSA. Another field, MSA Summary, indicates that it is a summary field. I used Excel to put all of the member city names in a field on the summary MSA record. It is separated by semicolons. (I know this is not the correct way to do it.)
My form contains a listbox, MSACities, where I need to display the cities associated with each MSA. When an MSA summary record is selected the members cities should appear in the listbox. Each municipality 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 a query that selects only the records that are flagged as MSA Summary records. When I bind the listbox to the MSACities field I was able to get the cities to show up in the textbox but they are not on separate lines. They are also not selectable.
I can not figure out how to specify the record source for the list box.
How do I get all of the member cities of the current MSA to show up in the listbox?
How do I get the list box to refresh with each new summary record?
I have searched 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 have attached an image and a mockup of the database.
I am a novice at this and have gotten myself into what seems like the deep water. Help!