[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Selecting values from a combo box

Posted on 2011-10-05
27
Medium Priority
?
271 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:thandel
  • 14
  • 13
27 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36918757
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
 

Author Comment

by:thandel
ID: 36918853
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36918876
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36918966
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36919182
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
 

Author Comment

by:thandel
ID: 36919844
I didn't know about the .dropdown property... I remove the send keys asap!
0
 

Author Comment

by:thandel
ID: 36919929
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
 

Author Comment

by:thandel
ID: 36919944
"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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36920334
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36920624

<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
 

Author Comment

by:thandel
ID: 36920996
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36922078
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36922160
0
 

Author Comment

by:thandel
ID: 36962227
Thank you let me look at your sample DB.
0
 

Author Comment

by:thandel
ID: 36962393
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36964826
So what are you saying here...?

That is what my sample does...
0
 

Author Comment

by:thandel
ID: 36987692
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36990651
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
 

Author Comment

by:thandel
ID: 36992937
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36993324
So how many comboboxes are we dealing with here, ...one, or two?
0
 

Author Comment

by:thandel
ID: 36993587
Just one, for the zip code.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36994787
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
 

Author Comment

by:thandel
ID: 36996033
The design reference is from the office's high level requirements.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36996230
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
 

Accepted Solution

by:
thandel earned 0 total points
ID: 37002756
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37002817
No Problem,

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

;-)

Jeff
0
 

Author Closing Comment

by:thandel
ID: 37105869
No solution provided
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question