Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form automaticlly Filled (Access 97)

    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    This is probably answered here, but had no luck finding anything through search. How do I get a form to fill automatically with data from one field entry? Good example is typing in zip code and the city and state are filled in the form automatically. I will be using other types of information though.
    Thanks.

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

    Re: Form automaticlly Filled (Access 97)

    The basic idea is to have a lookup table. In this case, a table with Zip code as primary key, and City and State as other fields.
    You can create a query based on your table and on the lookup table, joined on the Zip code field. Double click the join and specify that you want to display all records from the main table.
    Use this query as record source for the form. I would put a combo box on the form for the Zip code (with the lookup table as row source), and text boxes for City and State. When the user selects a Zip code in the combo box, City and State will be filled in automatically.

  3. #3
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form automaticlly Filled (Access 97)

    Thanks Hans but I want to go to a field, ZIP, type in the code, press, tab and all relevant information about the code come up in the appropriate fields. Do not want to work with drop down lists.

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

    Re: Form automaticlly Filled (Access 97)

    OK, so use a text box instead of a combo box for the zip field.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form automaticlly Filled (Access 97)

    I live in New York and some zips can refer to more than on city, Hans suggestion of using a Combo Box would allow you to enter zip, if there were more than 1 match, build sql that would populate combo box and allow you to select correct city.

    HTH

    John

  6. #6
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form automaticlly Filled (Access 97)

    John,
    I am using the ZIP codes as an example. What I am dealing with is information on water well systems. Each system has its own ID code. Creating a form to have all information uniform as it is input or updated in the system.

  7. #7
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form automaticlly Filled (Access 97)

    OK Guys, I am not getting this. I have a table with information dealing with Water Wells. The Key field is the Well ID Number. I am creating a form that contains inspection information that is put into a database. I want to go to this form and type in the Well ID number and have certain information prefill the form before manually filling in the rest of the form.
    My problem is I cannot get the auto fill to work properly. Do I need to create a separate table with the well information along with the other table of information I have? Honest I am not dumb. I just need some guidance, as they say in the military. I love working with Access. Most of what I know is self taught. No classes that teach above beginners.
    Thanks for the assistance.

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

    Re: Form automaticlly Filled (Access 97)

    You should indeed have two tables:

    - A table with data about wells; WellID is the primary key.
    - A table with data about inspections. WellID is also in this table as a foreign key.

    Create a query based on the two tables, joined on WellID.
    Double click the join and select the option to return all records from the inspection table.
    Add all fields from the inspection table to the query, including WellID.
    Add the fields from the well table you need, but not WellID.
    Save the query, and use it as record source for your form.
    As soon as the user enters a valid WellID, the controls bound to fields from the well table will automatically be filled in (you will probably want to lock these controls).

  9. #9
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form automaticlly Filled (Access 97)

    I am trying to use this concept on a form, but the record source for the form is already set to tblFamilies (this table includes fields like LastName, StreetAddress, ZipCode, etc.). How can I use this to enter Zip Code in a text box and have the City and State text boxes fill in automatically? I have a tblZipCodes with ZipCode, City and State fields. I also have a qryFindCity that I believe is set up the way you recommended, joining ZipCode from the tblFamilies with the ZipCode field in the tblZipCodes. I am not planning on storing the City and State, just want them to display on the form.
    Happy Aruban Flag Day!

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

    Re: Form automaticlly Filled (Access 97)

    Change the Record Source property of the form to qryFindCity.
    Add text boxes bound to City and State to the form.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form automaticlly Filled (Access 97)

    If I do that, do I need to add to the query those fields that are currently bound to the tblFamilies like the name, address, etc?

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

    Re: Form automaticlly Filled (Access 97)

    I'd add tblFamilies.* to the query grid - that gives you all fields from tblFamilies without having to specify each of them individually - plus the City and State fields from tblZipcodes. Don't add the ZipCode field from tblZipCodes to the query grid, since you already include the ZipCode field from tblFamilies.

Posting Permissions

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