Any Solution for This Combo Box Problem?

[elliot315]

Ok here it is my situation, I have a cascading combo box where I select a city and the other combo box requery and update with the zip codes of that city. Some cities have one zip code, and others have more than one. I want the zip code combo box to put the zip code automatically when a city only has one zip code, and when a city have more than one zip code... to make the combo to dropdown to present the different zip codes.

The other thing is to make the combo box with the name of the cities not to repeat the name of the city in its list, but this is because the table is that way.. I'll put an example of the table where the combo boxes get their values

ID City Zip Code
--------------------------------
1 Aguada 00602
2 Adjuntas 00603
3 Aguadilla 00604
3 Aguadilla 00605
4 Añasco 00606

As you can see in this brief preview "Aguadilla" has two zip codes "00604" and "00605" and the same ID number so the zip code combo box present both of them, but it will appear twice in the City Name Combo Box.. do I have to make two tables to manage this situation?
Thank you in advance... I tried to be as clear as possible.

 

[pbaldy]

First one, use a recordset or DCount to see how many records exist for the chosen city. If 1, use the same recordset or a DLookup to get the zip. Second one, either use SELECT DISTINCT... or a totals query to get only one instance of each city.

 

[Dreamweaver]

There are a number of ways of doing this
First of use Dcount to get a count of cities zips then when you have the number if it's 1 use dlookup to get the zip code then add it to the combo IE mE![Combo]=DlOOKUP("FieldName","TableName","[ID]=" & YOUR ID)
If you have more than one use a query and update the combos rowsource with a query limited to the city then it's just a me![combo].dropdown so the user can select the entry from those available for that city.

Or you can do it with a recordset.
Lookup DCount, DLookup in the help files

if ya have trouble post a copie of your db and I'll do you an example.

mick

 

[elliot315]

Thank you both of you... I can post the db but it is in Spanish... could you manage that?

I don't know much about VBA so please can you explain me how the code goes?
I'll write all the information about the combo boxes with their names and the table name,
here it goes:

Combo75 is the one that contains the Cities (done right with the SELECT DISTINCT, thank you)
Combo77 is the one that requeries and shows the filtered zip codes
Table - tbl Zip Codes Residenciales
fields for this table ID, Cities, Zip Code

so now.. i want to see the Dcount and Dlookup codes... I tried, but you know... that RED ERROR MESSAGE from the compiler pop ups!

Thank You Again

 

[pbaldy]

We may be able to puzzle out the Spanish. Or post the code you're trying, and the error you get.

 

[elliot315]

Ok here it goes the db
By the way if you have msn messenger we can chat directly
mine is schindler101athotmaildotcom (sorry if on the rules says not to share email... I haven't read that, and I'm desperately trying to resolve this).

the form name is "frm Informacion Personal Cliente"
and the combo boxes are Pueblo (City) and Código Postal (Zip Code) (they appear twice on that form).

The way it should work is... first you select Pueblo and Código Postal must update with the Zip Code related to that city... if there is only one zip code... put the zip code.. if there is more than one.. drop down the combo box.

 

[pbaldy]

You weren't all that far off:

  Me![Combo77].Requery
  If DCount("Pueblos", "tbl Zip Codes Residenciales", "[ID]=" & Me.Combo75) = 1 Then    'added criteria
    Me![Combo77] = DLookup("[Zip Code]", "[tbl Zip Codes Residenciales]", "[ID]=" & Me.Combo75)    'added brackets and fixed combo reference
  Else    'added
    Me.Combo77 = Null    'added
  End If    'added

The spaces in your names were part of the problem, and will continue to be a headache. You should get rid of them if it's not too late.

 

[elliot315]

Thanks, I really appreciate your help... I just fix the ELSE part to show the dropdown in the combo box to select the zip codes of cities with more than one zip...
by the way... what do you think about the design of the form... any suggestions will be gladly appreciate it.. thanks!