How do I get an MS Access Combo Box to modify not just the Control Source field but another field

Question

I have a city table that has a city_id, name, country_name columns. This country_name column links with the name column in the country table.

The values I need to insert in my company table are the city_id and the country_code columns. The Combo box solves the insertion into the city_id column by just selecting the city's name. Nonetheless, I need to create a scenario where whenever a particular city is selected, the corresponding country_name where the city is located pops out in another combo box or something. (Dont forget, the cities already come with the country_name field listing the countries each city is located in).

It's also a possibility for me to edit the city table data source and substitute the list of countries in the country_name column for their actual country codes. This way I would be able to insert directly into the country_code column of the company table. The problem still remains how 2 columns from one Combo Box will be inserted into separate columns in the company table depending on which value is selected.

Thank you, I will be needing your support here.

Answer 1

Use the Column property in the AfterUpdate event of the combobox, ie:

Me!OtherTextbox.Value = Me!YourComboBox.Column(1)