• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

How to confirm more than one result from a table

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?
  • 2
2 Solutions
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.
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.

thandelAuthor Commented:
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.....

I see...you 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now