I have a sub-form with a combo box to choose a location. Each location has a unique ID that is then stored in the table.
Each location is associated with a region, and a region can have more than one location. I have two combos on the same form that make it easier to enter locations -- reducing the list using the region drop down first.
However, since each location has a unique ID there's no need to store the region ID also, indeed, if I were to then there's a chance that the location could be changed to an ID that doesn't exist for that region.
Locations and regions are stored in separate tables, but I have created a view that joins the two together, repeating regions for each location. Ideally, I'd use this so that when a location is changed it region can be identified and selected in the other drop-down.
Is this possible? At the moment I am ignoring the chance of it being able to automatically select the regio, but just having it to filter locations and storing those -- that's causing problems. For some bizarre reason, if I change the region it then changes the region box for any of the other forms (using the continuous forms display type). I can't link the two together without the second combo box then only showing IDs for the wrong region (thus losing the valid location that was previously stored).
All suggestions would be much appreciated. I've tried a few different ways of linking the boxes, such as using the OnGetFocus event to change the RowSource and update, as well as changing the RowSource to use Forms!Locations!Region notation.
I've made a little progress -- realising I can pull the Region ID from the row associated with the location drop-down.
I used the OnCurrent event to change the Region drop-down's value to the correct one. However, this doesn't work as expected within a continuous form display -- changing the other form drop-downs. Any suggestions would be much appreciated.