Selecting values from a combo box

Using Access 2003... I have a table that contains, City, Zip and State.  This is used on a form so the user can just enter a zip code and then the form takes that zip code and then automatically populates the city and state fields on that form.  All is working great.

The issue I'm trying to correct is that some zip codes belong to multiple cities.  When this condition occurs I would like the zip code field on the form (a combo value that has the cities and states in columns) drop down so the user knows there are multipul cities for that one zip to allow them to select the correct city.

I have the following code that works however.... due to the cancel event the user is stuck and can't get out of the field as the condition is always true... without the cancel event the  user is not presented with the drop down list. (which I bring up via the sendkeys command)

Any suggestions?   Thank you.


 
Private Sub cbZip_BeforeUpdate(Cancel As Integer)
    
    'Check for multiple cities with same zip
    If DCount("*", "tzip", "zip = """ & Me.cbZip.Column(0) & """") > 1 Then
        SendKeys "(%{DOWN})", False 'set ALT-DOWN to display zip/city/state list
        Cancel = True
    End If

End Sub

Open in new window

thandelAsked:
Who is Participating?
 
thandelAuthor Commented:
I guess the sendkeys appears to be the best option for me and your abilities. Thanks for tying though.  Perhaps someone else can jump in with a suggestion other than send keys and a few lines of code that I have implemented.  Thank you again for your time.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Just to be clear, the US Post office only cares about the zip code, not the city, town, hamlet, village, ...etc

So you can simply list the street address and the Zipcode and the mail will be delivered just as fast.

In any event there a lot of way to do what you are asking.
One that immediately comes to mind is to (Obviously list each city for a given zipcode in the table) and make the City a combobox as well.
The city will "Cascade" from the zip code.

So selecting a zip code from a combobox will in turn filter the City Combox for the associated cities.

I am sure that based on your experience here, you can get something like this working in your database
(Research: Cascading Lists/comboboxes)

;-)

JeffCoachman
0
 
thandelAuthor Commented:
Thanks I believe I have something like that for the City Combo... however I was hoping to just have the user select a zip code based on a given city all from the zip combo... then after selecting that the city and state and zip are all correctly selected.  

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Jeffrey CoachmanMIS LiasonCommented:
With my suggestion DCount is not really needed because the number of cities can be determined by the relationships in the table.
Then the dropdown could be forced open (to see the one or more cities.)

So to avoid using the unpredictable "SendKeys" command you can use:
me.cboCity.SetFocus
me.cboCity.Dropdown
...after the zipcode is selected...

Make sense?

;-)

Jeff
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I was hoping to just have the user select a zip code based on a given city all from the zip combo>

So when you select "Brooklyn" you want the user to have to select from zipcodes in NY and Ohio?

This is a data entry issue.

This is typically done by first entering the Address, then the Zipcode, then the city will be selected from a combobox.

For example whenever you are in a store,  whenever they ask you for your address info, they will ask for the street address, then the *Zipcode*, (Not the city), then the zipcode will filter the city combobox, then they will ask you to verify the city.

So again, what is typically done is that the zipcode (the important unique value) is requested, then a city name is selected,
...not the other way around)

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
the goal here is to always key off of the Unique Value (Zipcode) then select from the "descriptive" value (the city name)
Notice the term "Cascading" implies a "downward" direction
Planet-->Continent-->Country-->Region-->PostalCode-->Street Address.
(This is what the Zipcode + 4 was created to do, ...to provide more "granularity" with regard to the exact address.

Then fact that in the US we recite the city first is what makes this concept hard to conceptualize,

Ex:
Select the Customer ID (Not the customer name) and see a list of Orders
Select the Emplyess ID (Not the employee name) and see all of their Paychecks

So in the same way as many programs now demand a 4 digit year to avoid ambiguity, many companies now ask for the address then the zip, then select the city (the state should autocomplete)

Jeff
0
 
thandelAuthor Commented:
I didn't know about the .dropdown property... I remove the send keys asap!
0
 
thandelAuthor Commented:
I tried the following in the after update:

        Me.cbZip.SetFocus
        Me.cbZip.Dropdown 'set ALT-DOWN to display zip/city/state list

but no dropdown.  Am I missing something?
0
 
thandelAuthor Commented:
"So in the same way as many programs now demand a 4 digit year to avoid ambiguity, many companies now ask for the address then the zip, then select the city (the state should autocomplete)"

But I'm working from the user entering the zip and the my DB will populate the city and state if a unique or zip code.. .if not then the zip will provide a drop down to list the various cities... the user select  the zip with city matching then the city and state will auto populate.
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I tried the following in the after update:>
On the after update of what?

This works fine for me.

If you create a simple form with a button and a combobox, and you put that code on the click event of the button, does it work?
Yes or No

<But I'm working from the user entering the zip and the my DB will populate the city and state if a unique or zip code.. .if not then the zip will provide a drop down to list the various cities... the user select  the zip with city matching then the city and state will auto populate.>
This is the same thing I suggested..
Are you reading my posts?
I stated:
<So selecting a zip code from a combobox will in turn filter the City Combox for the associated cities.>
...and
<what is typically done is that the zipcode (the important unique value) is requested, then a city name is selected, >


boag2000-509625.flv
0
 
Jeffrey CoachmanMIS LiasonCommented:

<Sme zip codes belong to multiple cities>
Not really, remember what I mentioned...
One zipcode no longer encompasses more than one City because of the Plus 4 extension.

Zipcodes:
11221-4527
11221-2188
Are *not* the same, they are unique when you include the Plus4
So they will represent two different geographic areas (down to the street, building or Floor level)

The fact that most people do not know their Plus 4 code is why you will select the Zip, then select the city.

I am working on a sample now...
0
 
thandelAuthor Commented:
I am reading your posts but you are assuming that we support the zip + 4 which we do not... I mean who really knows or uses that when filling out a form...   Yes I created a separate event and it does work, however it DOES NOT work if you use the code in the after update routing.  Did it work for you in the after update?
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I am reading your posts but you are assuming that we support the zip + 4 which we do not... I mean who really knows or uses that when filling out a form...  >
OK, I was just mentioning this as many companies are moving to this format.

Just FYI, it *IS* being used more and more, ..
For example, as I stated, the Plus 4 may indicate the Street, Building, Floor, ..etc
So in highly populated places like Manhattan, NYC, knowing your +4 is crucial.
Without it, your Mail may be delayed by a day or so...
;-)

<Yes I created a separate event >
You created an "Event"?
Please explain clearly what you mean by this?

<however it DOES NOT work if you use the code in the after update routing.>
Can you explain what "After Update Routing" means?, ...and clearly state what control you are referring to...
Remember, without a sample DB to look at, I am flying blind here...

Finishing up the sample now......

0
 
Jeffrey CoachmanMIS LiasonCommented:
0
 
thandelAuthor Commented:
Thank you let me look at your sample DB.
0
 
thandelAuthor Commented:
OK so here is the issue... I am changing the Rowsource value of the drop down... I have a table that has cities and zip.... as I mentioned some cities share a zip code.  So when the user enters a zip... if its unique than we are good... if its not unique then I am changing the rowsource value of the drop down so they can see the cities associated with the zip so they can select the correct city.

So my code executes on the afterupdate... I am not able to use gotfocus as I need to wait until the user updates the zip.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
So what are you saying here...?

That is what my sample does...
0
 
thandelAuthor Commented:
Correct me if I am wrong but your code just automatically displays the drop down when the user enters the field.... I need first determine the value and then based on that value display the drop down or not display the drop down.
0
 
Jeffrey CoachmanMIS LiasonCommented:
First lets be clear.

One zip code can contain more than one City.
*And* on City can contain more than one zipcode.

So how is your system dealing with this scenario?

Perhaps you should state your question in terms of a real world user scenario.
The user opens the form.
They enter the person's Name, DOB, Street Address, then________
If________, then__________
...etc
0
 
thandelAuthor Commented:
Sure, the user opens the form... enters a name and then zip code.  If the zip code only cooresponds to one city then the city and state fields are populated autoamtically.  If the zip code entered by the user is for more than one city then I would like the zip code's drop down value get populated with the cities for the zip code  and then drop down to activate thus showing the user which cities they can select from for the desired zip code.

0
 
Jeffrey CoachmanMIS LiasonCommented:
So how many comboboxes are we dealing with here, ...one, or two?
0
 
thandelAuthor Commented:
Just one, for the zip code.
0
 
Jeffrey CoachmanMIS LiasonCommented:
While what you are asking for is certainly possible, I just hate doing things like changing the rowsource of a combobox based on a selection made in that combobox, ...and having to use DCount to check for more than one city, ...etc

In other words, my system does the same thing you are requesting, just not with the exact same interface.
The way I have always seen this is similar to what I posted.
What is your design reference for the specific functionality you are requesting here?

JeffCoachman
0
 
thandelAuthor Commented:
The design reference is from the office's high level requirements.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Then this is something I feel very uncomfortable about doing.
Only because it involves a lot more complexity for very little in the way of interface improvement.
I really don't see how my interface would be deemed "unacceptable" from a data entry standpoint.

By this I do not mean that you should be forced to accept my post as the solution to this question, ..but rather I can't see how "office's high level" would say that you *must* only have one combo-box, or that you *must* change the rowsource dynamically.

Their main concern is Data entry, correct?
So you are saying that you demonstrated my technique, and it was deemed "unacceptable"?

Again, what you are requesting is certainly possible, but I hate designing an entire complex system, that works virtually the same as a simpler system.
I think that when issues like this are explained to upper management in a clear way, with supporting evidence, they will typically understand.

Finally, note that there are many ways that this can be done, but the "specifics" of what you are presenting here make this much more complex than it needs to be... (IMHO)

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
No Problem,

Hey you know me, I have no issue with you re-posting this Q.
Perhaps another Expert will step up.

;-)

Jeff
0
 
thandelAuthor Commented:
No solution provided
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.