City name changes to a number when control is changed from combobox to text field

[Gina Maylone]

I have a table MN Cites, it contains one column "City" no id numbers! When I choose the city from a combo box it displays the city name. On another form, I want to display the city as well, but I don't want it to display as a combo box, so I change it to a text box, and it displays the record number.




In addition, when I choose a city, it saves as the record number. I don't want this, I want the city name.



Why does this happen and how can I stop it? Thanks in advance!!

[Welshgasman]

You are likely using table lookups, not recommended.

In addition, when I choose a city, it saves as the record number. I don't want this, I want the city name.

No, you do not, you really don't.
You save the PK of the city record.

[June7]

So what is the source of combobox - [MN Cites] (is that a misspelling of [MN Cities])? If combobox is saving a number then this table must have an ID field.

I agree, save the PK, not descriptive text.

What table are you saving into?

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

[ArviLaanemets]

Why is a bad idea to save city names in all tables?! And why is it good idea to have a city registry with separate ID field?!

1st situation - You enter city name directly into all tables (no city registry):
There is nothing to prevent misspelling, so you can easily have several different spellings for same city;
Whenever you have to change the name of city for whatever reason (e.g. you discovered you didn't use right spelling from start), you have to replace manually or using script all misspelled entries in all rows of all tables containing such entry.

2nd situation - You have a city registry, where city name is primary key, and in other tables you have city name too. You read the city name into forms from this registry using combo:
Whenever you need to change the city name for whatever reason, changing it in registry leaves all city fields in all other tables containing this name orphans. You have to edit all of them too;

3rd situation - You have a city registry with (autonumeric) primary key field (e.g. CityID), and a field for city name. In all other tables, you'll have a foreign key where CityID is stored, and in forms you use combos with CityID as combo value and city name as displayed value:
Whenever you need to edit any city name, you edit it in registry (leaving CityID unchanged). In all forms, you use combos linked to CityID and displaying city name. When the name of certain city was changed, then new name for this city is displayed in every form for every linked row.