Cascading Combo Boxes Zip/State

[traycee]

I have one table, City, that contains an autonumber field called AddressID. The other fields in that table are text and are City, Township, County, Zip and State. I have 2 combo boxes I'd like to cascade. The first combo box is cboZip, which is text. The info for it comes from the City table. The related combo box is named cboCity and it's info comes from the same table. The user is able to pick or type a zip code in the first combo box and they do get the correct drop down choices for city and the correct AddressID is stored in the table. But when that record is reopened the zip code shows but the city field is blank even though the correct numeral is stored in the table.

Private Sub cboZip_AfterUpdate()
Me.cboCity = Null
Me.cboCity.Requery
Me.cboCity = Me.cboCity.ItemData(0)
End Sub

The event for the City field is

Private Sub cboCity_Enter()
Me.cboCity.Requery
End Sub

I need to store the autonumber field when a city is picked because a city can have multiple zip codes, multiple townships and be in multiple counties. The row source for city is:
SELECT City.AddressID, City.City, City.Township, City.County, City.Zip FROM City WHERE (((City.Zip)=[Forms]![Navigation Form]![NavigationSubform].[Form]![Zip])) ORDER BY City.City;

This is driving me nuts. I'd appreciate any help I can get.

Thanks.

 

[MajP]

So when you go to a record that has a stored city value I assume that the value of the Zip is blank at that time. So eventhough you have a field value it cannot be displayed because the combo has basically no records in its rowsource.

"SELECT City.AddressID, City.City, City.Township, City.County, City.Zip FROM City WHERE (((City.Zip)=[Forms]![Navigation Form]![NavigationSubform].[Form]![Zip])) ORDER BY City.City;"

Because the zip combo is blank. So my solution would be on the forms current event:

1) check to see if the current record has an addressid. If so then
2) use a dlookup to find the associated zip for that address id
3) set the value of the zip combobox to the associated zip code
4) requery the city combobox

 

[dhookom]

I think the issue is with a continuous form which might be resolved by Roger Carlson's solution.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Based on the term "reopened" I am assuming this is not a continuous form, but it still is probably a cleaner solution.

1) Set the rowsource to
"SELECT City.AddressID, City.City, City.Township, City.County, City.Zip FROM City ORDER BY City.City

2) On the on enter event if the zip combo has a value, set it to
"SELECT City.AddressID, City.City, City.Township, City.County, City.Zip FROM City WHERE (((City.Zip)=[Forms]![Navigation Form]![NavigationSubform].[Form]![Zip])) ORDER BY City.City"

3) On the Exit event set it back to
"SELECT City.AddressID, City.City, City.Township, City.County, City.Zip FROM City ORDER BY City.City

 

[traycee]

Sorry it took me so long to respond. I was trying the ideas above but still not working. Originally, this did work for me but I was storing the name of the city in the city field instead of the autonumber which is what I need to store for the record. I changed the fields accordingly. Now when I open the contact form, the zip code shows in the zip cbo field but the city field is blank. However, if I hit the drop down arrow on the city cbo box the correct city is listed....but it should show up in the city field since it has previously been selected.

The contacts correspond with the client. There is a field in the client form to select the contact information. Double clicking on that field takes the user to the correct contact information. The zip shows as usual but the city field is blank as is the record source. Maybe it would make more sense, instead of having cascading combo boxes to have a single combo box. The user types in the zip then picks the correct row containing city, township, county and store the ID.

Again, I do really appreciate the help.