How to confirm more than one result from a table

Posted on 2011-05-01
Last Modified: 2012-05-11
I am using the solution from 8287

I added a combo box for the city so if someone enteres the city it will populate the zip code.

A couple of issues.... some cities have more than one zip code so

1. if the user enters a city and a zip has been previously entered zip (i.e. existing customer moved to a new city) for a multi zip code city.  I need to look through the entire table for more than one zip code and confirm it matches at least one city to the zip code or inform the user

2. if the user enteres a city that has more than one zip do not populate the zip code field but that that the system is not able due to multipul zip codes for that city.

Is this possible?
Question by:thandel
    LVL 1

    Accepted Solution

    For your first question; you could use a DLookup against the table to confirm at least one zip exists for the selected city.  This could be handled in the after update event for the combo box.  You will need to do a count on the results too, and if only one is returned, then set the zip code combo box equal to that value.

    For the second part, you can just expand the Dlookup portion in the after update event to be an If statement.  Just create a parameter for the count of zip codes returned from the Dlookup, then perform the If logic against the result.  If count is equal to one set the combo box equal to that value, else if greater than 1 display a MsgBox stating more than one zip exists and one cannot be set for this city.
    LVL 42

    Assisted Solution

    The simplest solution is to lock the city field so that the only way to change it is by changing the zip code.  While simple, and oddly elegant, that unfortunately is not what people are generally used to.

    I suggest using a second dropdown for the city.  In that dropdown, display two columns: the city first and the zip code second.   When the user types in "Chigago", all Chicago zip codes will appear and the user can choose the correct one.  Then, much like 8287, move the zip code value from the selected item in the city dropdown list to the zip code field.


    Author Comment

    Well, I can't implement a lock on the city as I only have zip codes for one state!  Man that was hard enough to find nevermind for the whole country......

    Let me look into a DLookup.....

    LVL 42

    Expert Comment

    I want to allow a city/state combination that is not in your zip code table.   If you don't have all  city/zip combinations in your table, there is only so much you can do.  

    When the zip is selected from the list, you know the city
    When the city/zip is selected from the list, you know the zip

    When either selection is NOT in the list, then you already know that DLOOKUP will not return a match.  

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Excel as MS Access front end 11 77
    SQL Query 18 66
    Running Sum Help 31 23
    Get rid of ID prompt upon form open - MS Access 3 6
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now