I would like make a reference table tblRefTable (for maintenance changes) that identifies Sectors and the Agencies assigned to them. The table will have a 1 to many relationship:
1 Sector may have various Agencies.
I would then like to be able to use tblRefTable to auto populate a Sector field based on the Agency selected i.e.
When the user enters a Agency in the form, it refers back to tblRefTable and selects the associated Sector, and uses this value to auto populates the Sector field in the same record row of the data form.
For example tblRefTable =
Sector "Red" = Agencies "A" "B" "C"
Sector Blue = Agencies "D" "E"
Sector Green = Agencies "F" "G"
I understand I could hard code this, but Agencies are often added to Sectors or deleted from them. I thought the logic of creating the reference table would prevent updating any coding if there was a change. My thoughts are, that I would then only need to code on the Agency after update event. (I will need assistance with this code) And all maintenance (changes) between Sectors and Agency would be controlled through the tblRefTable.
Is this the best way to approach this.
Thanks Ron
1 Sector may have various Agencies.
I would then like to be able to use tblRefTable to auto populate a Sector field based on the Agency selected i.e.
When the user enters a Agency in the form, it refers back to tblRefTable and selects the associated Sector, and uses this value to auto populates the Sector field in the same record row of the data form.
For example tblRefTable =
Sector "Red" = Agencies "A" "B" "C"
Sector Blue = Agencies "D" "E"
Sector Green = Agencies "F" "G"
I understand I could hard code this, but Agencies are often added to Sectors or deleted from them. I thought the logic of creating the reference table would prevent updating any coding if there was a change. My thoughts are, that I would then only need to code on the Agency after update event. (I will need assistance with this code) And all maintenance (changes) between Sectors and Agency would be controlled through the tblRefTable.
Is this the best way to approach this.
Thanks Ron