Combo to replace existing control

[CW]

In our tblOrders and on frmOrders I have a field/control for the customer's
country.
In the past this has always been entered manually (as free text) which
unfortunately resulted in lots of varied and incorrect spellings of country
names.
We have now cleaned those up and all the records contain valid spellings.
I have now added a new table of approx 100 country names and I want to
replace the existing text box on frmOrders with a dropdown based on the new
tblCountries.
I have no problem with how to do that in itself, i.e. adding a combo, and
telling it to bind to the existing tblOrders.Country field. However I am
concerned about retaining all the existing country names on the several
thousand records. If there is a match between the existing country name on
any given record and a country contained in tblCountries, will the record be
maintained? Or will they all be deleted?
I can't quite get my head around what will happen and would be grateful for
guidance.
Many thanks
CW

 

[Jeff Boyce]

What is actually being stored in that field in that main table? Is it text
(i.e., the "country name"), or is it an ID value?

In the new table, do you have a single field with "country name", or two
fields, one with an ID and then the "country name"?

If your current/main table has "country name" spellings that are NOT on your
new list, plan on doing some data cleanup. You might be able to get by with
a simple series of update queries, or even Find/Replace.

Then, when you start using the combobox on the form, you are guaranteed that
every exiting "country name" is in your short list.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP