Update a Table from two combo boxes

[Guernsey Gaz]

Hi All,

I have a db that has a table called tblCountryCity it has two columns the first being the Country the second being the City The table is not linked to anything as it is just used to get Country and City into other tables. The Country field has many duplicates, not a problem.

I have a couple of forms that allow users to Add New contacts and other things.
On each of these forms I have two Cascading combo boxes, 1 for the country, once the user has selected the country the second combo gives a list of cities only for that country. I have no problems there.
If a Country is typed in and is not in the countrycity table I can get it to add using the following code.

Private Sub cboCountry_NotInList(NewData As String, Response As Integer)
On Error GoTo cboCountry_NotinList_Error
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Country " & " '" & NewData & " '" & _
" is not currently listed in the Country Table." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Country")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCountryCity([Country]) " & "VALUES ('" & NewData & " ' );"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Country" & " '" & NewData & " '" & "has been add to the list. " _
, vbInformation, "Country"
Response = acDataErrAdded
Else
MsgBox "Please choose a Country from the list." _
, vbInformation, " Country"
Response = acDataErrContinue
End If
cboCountry_NotInList_Exit: _
Exit Sub
cboCountry_NotInList_Error:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCountry_NotInList_Exit
End Sub

This is fired with the NotInList Event for the cboCountry

OK heres the bit I got problems with, obviously if I add a new country to the list I am not going to have the city so if I type in the city then I want it to add it to the city field in the table... I have tried just copying the code and changing the fields about but it dosn't work. I can get either one without the other ie if I go straight to cboCity I can add it to the list but there is nothing in the country field next to the new city

Question: When I add a new Country via the cboCountry can I get the cboCity to add the new City?

Any help helps

Gaz

 

[doco]

It seems to me it would be much easier to eliminate some of the possibilities. Why not just populate the country list with the known quantity: countries of the world http://www.infoplease.com/ipa/A0932875.html or the possible countries you would deal with. Then just deal with adding the new cities when they arise?

 

[Guernsey Gaz]

Thanks Doco
But if I have a list of all countries I still only want the second combo to return cities that are in that country

Gaz

 

[Guernsey Gaz]

Hi All Still having trouble with this, even if I use a single list of countries when I add the city it adds a new record to the CountryCity table but dosn't have a country to go with it. I need to add countries and Cities. If I add the country and then add the city I want them as a record my table looks like this


Country City
U.K. London
U.K. Birmingham
USA NewYork
USA Chicago
USA Atlanta
France Reenes
France Paris


and a lot more, I can get the cboCountry to update the Country field using the code posted earlier but I also need to add the city from the cboCity
both the cbo's are on a form.

Gaz

 

[Guernsey Gaz]

OK I am trying this a differnt way, I have taken off the limit to list, that way the contact new record will have the details in the contact table. I have done an Append query to append the country and city fields in the CountryCity table from the Contacts table. I have the City in the CountryCity Table as PK so that I dont get lots of repertitions without having to click OK 3 times for the query, I just want it to run and then leave me in the next txtbox of the form.

Any Help helps

Gaz

How do I now run the append query when I tab out of the cboCity

 

[doco]

How do I now run the append query when I tab out of the cboCity

Still not entirely clear on what ( and how ) you are trying to do. But you could run your append query from cboCity_LostFocus() event.