Countries and Cities help.

[kimx]

What to do with countries and cities…

I’m designing some tables which hold country and city in them. I’d like to create some tables:

Table tblCountries
CountryID
CountryName

Table tblCities
CityID
CityName
CountryID (FK to tblCountries.CountryID)

And then finally the table tblLocations with
LocationID
CityID (FK to tblCities.CityID so the country is also included in this)


Doing so, I’m always having problems with displaying these items in the forms as combobox or list (the item created out of tblLocations.CityID shows city and country when you drop down the list, but the field only shows the city etc etc…

Isn’t this the right way to do it? Or is there a better way to create relationships between countries and cities if you want them in a separate table ?
(I checked out all the sample databases from MS Access but they never have cities and countries in separate tables…. I know why !)

Thanks for any suggestions !

Kimx

 

[mikey69]

One point. Your location table has no location description, so you would not easily beaable to use a combo box to list locations.

Otherwise, your logic looks OK to me.

But remember that you would have to construct a query to ist a combination of location, city, country.

 

[LittleSmudge]

When 'collapsed' combo boxes only ever show one column.

So you need to have other text box controls on your form to display the other text entries that you need.

Assuming that your combo box is called cboLocation and the bound column returns tblLocations.LocationId then

Have a text box control called txtHidCityId
In that control's ControlSource put
=DLookup("CityId","tblLocations",LocationId = cboLocation)
Set the .Visible property to NO

Then have a text box control called txtHidCountryId
In that control's CountrolSource put
=DLookup("CountryId","tblCity",CityId = txtHidCityId)
Set the .Visible property to NO

Then have a text box control called txtCity
In that control's CountrolSource put
=DLookup("CityName","tblCity",CityId = txtHidCityId)

Then have a text box control called txtCountry
In that control's CountrolSource put
=DLookup("CountryName","tblCountry",CountryId = txtHidCountryId)

Then in the comboBox AfterUpdate event you need to requery each of these controls in order.



An alternative - if you're into coding would be to open a record set based on a SELECT statement that combines all three tables then set the City and Country controls using VBA. This would probobly be a quicker solution to execute but needs more coding.


'ope-that-'elps.

G LS

 

[kimx]

Thanks !!!
I'll try this stuff out.
I'm so annoyed with this. I'd like to learn access but I'm used to Oracle forms with pl/sql and this seems much easier for me. I know exactly what to do there and where and which trigger etc. and sometimes when I want to do something very simple in access, I feel so stupid and just don't know where or how to begin...

 

[LittleSmudge]

And from an experienced Access programmer I see the same thing in the other direction.

Keep coming back here kimx - we'll sort you out.

Don't feel embarrased. I even ask questions myself - and it's often the 'can't see the wood for the trees' type questions too.


G LS

 

[kimx]

Heya LittleSmudge's,

Hope you'll still read this. I'm doing what you're suggesting and stuff works well except for one thing.
But in fact, as I've got a big plan/idea to make myself an appplication, I'd like to talk about it with somebody to see if my thoughts are OK and I would also like to check the beginning as that is very important...
Would it be possible to send you some stuff by email to discuss about it? I think it's better than posting here. I really could use a hand. Of course I understand if you don't fancy this...
Let me know, OK ?
My email home is: et.out@wanadoo.be
thanks a lot and hope to hear from you soon !