Hello!
This didn't work very well.
The problem is:
I have the table only for the cities (fields are city_id and city_name) that already have like 200 cities in there.
My main table still doesn't have any data in there, because it is supposed to store data about the people that want to make a reservation in the hotel (the fields in these table are identification, name, age, city_id, etc...), so I am supposed to add it when someone "hypothetically" wants to make a reservation.
When I tried to do your suggestion (I add the CityName2 to the main table), since I only have 2 rows of data in the main table it only updates those 2 rows with the city names...
I think that if I had a lot of data already in this main table (in other words, if I had data of a lot of costumers that already had made a reservation) I could have a lot of different cities already in that table and it would be a "good start" and then when I made a new reservation this cities of the people that have already come to the hotel would be there already.
Since I am doing this for a class but at the same time it is for a real inn (similar to hotel) of a friend of my teacher that will open soon, the database will be with no data in these main table. (I will only put data to test it and for the class, but for the inn owner it shouldn't have any data in these main table at least).
So, what I basically want is to have like a field to put a city of where the person is. I could do this easily by just putting the city as an attribute (that is, a new field) in the main table and as I am writing new data of a costumer, simple put the city name, like I please, in a text box. But, the owner of the inn doesn't like this idea because he wants to be able to make statistics and graphs with the data afterwards (for example, how many people come from a specific city) and with this easy solution (the simple text box) there is the problem I already talked about, same city with different names (because of abbreviations or errors when typing). And the better solution, at least in my mind, is to have already some of the most "important" or "known" cities stored and then as I (or someone) is registering a new client, the city field would show a suggestion (as I am typing) and if the city isn't stored in the database yet, I could just add it and then in a future reservation, that new city and the others that were already there would be in the suggestions.
A solution for this would be to have the combo with the cities I have already stored (the suggestion problem disappears) and if the city is not there, go to the table of the cities and add the new city there, and then update the combo with this new city. But this is not very user-friendly so I would like (if possible) to add the new city in the same form that I am doing the input of the data of the client.
Sorry for the trouble! I hope you understand what I want, I still hadn't the opportunity to talk to my teacher about this, maybe he has a solution!
If you have any ideas, share it please!
Thank you for your solutions anyway!
EDIT: I think I have found a good solution. If I create a new combo but instead of the row source type be (table/query) if I choose "Value List" and then I choose the option to store the value (in a new field like CityName in the main table) I think it could work!
With this solution I don't need the table with the cities. I just put all the cities that I have already in this value list, and I if the city is not there I could just simply add it. I didn't try this solution before because I thought that with this "type" of combo I couldn't do the suggestion thing. Do you think that this works well or some problem could occur?