Combobox filter combine multiple records
I have a Form with a Subform on it and I want to build a combobox to filter the location.
How do I combined two or more sub-location into one?
For example I have two tables that arrange the location in General Location and into Measuring Location.
Table General Location:
LocationID / LocationShortForm / LocationName
1 USA United States
2 CHN China
3 AUS Australia
Table Measuring Location (ML)
ML-ID / indLocationID / txtML-Name /
1 3 Melbourne
2 1 New York
3 1 Texas
4 2 Beijing
5 3 Sydney
6 1 Florida
My combobox should be based on table General Location with that 3 choices.
So that for example when I clicked on USA, the records for New York, Texas and Florida will be shown at the same time.
My question are:
1) Should I build a new table or a new query, that will sum up all the records in a general location?
2) I'm thinking of doing VBA codes using Select Case but I don't know how. Can anybody outlined how I should write it?