Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Fields (Access 97)

    We have a huge Zip Code database in Excel.

    What we want to do is create a customer form into which we type a Zip Code, Access goes to the Zip Code database (in Excel 97) and fills in the City and State. This data will be passed back to the Table on which the form is based.

    This seems more complex than the combo boxes I've used previously.

    Any simple (non VB) way to do this?

    Thanks,
    Craig.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Fields (Access 97)

    Use File/Get External Data/Link Tables... to create a table in your database that is linked to the Zip Code table in Excel (or import it into Access).
    Create a query based on the Customers table and on the Zip Code table.
    Link the tables on the Zip Code field, then double click the line joining the tables and select the option to display *all* records from the Customers table (this is called an outer join).
    Add the fields you need from the Customers table to the query grid, plus the City and State fields from the Zip Code table.
    Save this query and use it as Record Source of the form. The Zip Code field can be bound to a text box or to a combo box (with the Zip Code table as Row Source).

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Fields (Access 97)

    Hans:
    Thank you for the quick response.

    I can't seem to make your instructions work. I created a query with all fields from Contact table (including city, state, zip) and city & state fields from Zip table, set to outer join.

    On the form, I created a text box field named Zip with the record source the zip field in the Zip table. The form shows the "Name?" error in the text box and won't permit any data entry in the form. I didn't have a RowSource property in the text box property sheet.

    Have I completely missed the idea?

    Thanks, again,
    Craig.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Fields (Access 97)

    Hmm, yes, sorry about that. Access needs a primary key on the Zip Code field in the Zip Code table, but you can't set a primary key in a linked table.
    So you'll need to import the Excel table, and make the Zip Code field into the primary key. After that, the query should be updateable.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Fields (Access 97)

    Hans:
    OK. Here's what I have:

    Table named Contacts, with fields Company, FName, LName, Address, City, State, Zip -- all text, no primary key

    Table named Zips, with fields City, State, Zip -- all text, Zip field primary key

    Query named Contact Query with all fields from above two tables, outer join on Zip fields (all records from Contacts table)

    Form named Contacts Form, with fields from Contacts table in the Contacts Query except Zip. I can't seem to make a text field work so that when I enter a value found in the Zips table, the form enters that Zip code, city, and state into the Contacts table.

    My problem seems to be the control where I type in a zip code on the form.

    Craig.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Fields (Access 97)

    You shouldn't have City and State in the Contacts table - it is derived information. Just the Zip field will do.
    As replacements, add the City and State fields from the Zips table to the query. Now, when you change the Zip code, City and State will change accordingly.

    A combo box is the best control to enter the zip code, since it can AutoComplete the user's entry.
    Set the Control Source of the combo box to the Zip field.
    Set the Row Source to the Zips table.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Fields (Access 97)

    Hans:

    That did it! It never occurred to me not to have the city & state in the Contacts table.

    Thank you so much for your expertise and patience.

    Craig.

Posting Permissions

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