Can I do a sort within a sort on combo boxes? (1 Viewer)

[Divit11]

Hi all,

I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.

It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.

Thanks in advance for any responses.

D

 

[gemma-the-husky]

combo boxes dont frop down until you click them. A list box is always open and perhaps is better for this purpose, because you can see you have alternative choices

 

[ejstefl]

Also, something I've noticed is that most applications make you type the zip code, and then select a city to be used. You are having the the user type in a city, and then choose a zip. This might confuse users who are used to the more conventional method. Just a thought!

 

[Divit11]

Thanks for the replies.

In reverse order, ejstefl, I am the only user of this form & database. I'm adding names to a DB from a visitors list and they don't always provide zip codes.

G-T-H, use of a list would require constant scrolling to find the right zip. There has got to be a better way than that.

Perhaps there is some VBA code that could take the string of characters entered into the combo box and then just display the sorted records that contain that string in the city attribute. Perhaps employing two different sql queries.

If someone can give me a key word(s) to help search for this solution, that would be very helpful.

D

 

[ejstefl]

I guess what I would do is this:

1) Separate Zip Code from the city

2) Create two combo boxes, one for city and one for zip code

3) Set the second combo box (or list box, if you prefer) to only display zipcodes associated with the city in the first combo box

Search for Cascading Combo Boxes or Cascading Lists for more info, or check out this link for a really great tutorial:

http://www.fontstuff.com/access/acctut10.htm

I forgot to mention that you can use the .dropdown method of the combo box to drop it down, perhaps after you update the city combo box you can dropdown the zipcode combo box.

 

[Divit11]

Thanks again Ejsterfl,
Martin Green's tutorial was very helpful. After I had wrestled with my code I have been able to get a list box to display when there is more than one record in my tblPostalCodes table with city name entered in my combo box.

Now I want to find a good way to rehide that list box when the focus moves to another control box in this form, but that's the topic of my next thread.

D

 

[gemma-the-husky]

out of interest, list and combo boxes are self searching. As you begin to type in your search, the combo box automatically jumps to the appropriate p[art. You don't HAVE to just use the scroll bars to search.