Results 1 to 3 of 3
  1. #1

    Lookup, copy data or what ever you might call it -

    I'm bummed out - I am trying to do what I believe to be a simple database task.

    I have two tables - Customers and Zip.

    Customers contains the following fields:

    Customer Name
    Zip Code

    Zip Table contains:

    Zip Code

    What I am attempting to do is list 100's of records in my Zip file that are all unique (e.g., 500 zip codes and their corresponding City and State.

    In the Customer Table, after entering the Customer name and Address, I would like to enter their Zip Code and have Access go to my Zip file to find the corresponding City and State and automatically fill those two fields in, in my Customer Table. Can this be done simply? Any help is greatly appreciated. And please start from the beginning (well maybe not the beginning - I have successfully installed Access on my PC)


  2. #2

    Re: Lookup, copy data or what ever you might call it -

    This is just to start you off -- to be exactly sure of everything, I'd have to actually do it, but try this:

    In the Zip field on your form put something (like) the following in your afterupdate event:

    me![fState] = dlookup("[State]", "ZipTable", "[Zip Code] = " & chr$(34) & me![fZip Code]& chr$(34))

    Notice I name the fields, prefacing them with an "f" because of earlier frustrations as to when you can refer to the field on the form or the underlying field ....

    You might want to do a refresh on each of the fields for display:
    (me![fState].refresh) <-- I may definitely have gotten this wrong, but you might not even need it)


  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Indianapolis, Indiana, USA
    Thanked 0 Times in 0 Posts

    Re: Lookup, copy data or what ever you might call it -

    Hi Steve,

    First, you don't really need to store the values for City and State in both tables. It sounds like a good idea to store the City and State in the ZIP tables (along with the Zip Code). That way, all you'll have to enter is the Zip code and it will look up the City and State. It's also more efficient as far as size goes - only storing the City and State once rather than twice; and not to mention the fact that it prevents human error of mistyping a city name...

    With that said (and after backing up the information), I would delete the City and State fields from your Customers table, keeping the Zip Code field. You'll want to set a one to many relationship between the Zip Code fields in both tables (ZIP being one and Customers being the many). This can be done in the relationship window (Tools|Relationships) - add both tables, drag the Zip Code from one table to the other, select "Enforce Referential Integrity", then create the relationship.

    Keep the City and State fields on the Customers form, but rather than binding them to a field, assign the Lookup statement in the Control Source property for each field. Something like the following should do the trick:



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts