Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Autopopulate in Form

Discussion in 'Information Technology' started by Jennifer Cali, Jul 28, 2006.

  1. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    I have an address database that lists all our suppliers (tblSuppliers). I'm
    tired of entering all the cities AND zipcodes, so I created a second table
    (tblZipCode) that lists all the zip codes and what city it is associated
    with. I then joined these tables together.

    I have a form on which all the suppliers are tracked (frmSuppliers) that is
    tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
    have the city automatically populate based on the zipcode entered. Is this
    possible? If so, how?

    --
    Thank you! - Jennifer
     
  2. Loading...


  3. Sam

    Sam
    Expand Collapse
    Guest

    Try this: http://www.mvps.org/access/forms/frm0009.htm

    Not an expert, but incidentally I just read the article before I read
    you post. Hope it helps.

    -Sam

    Jennifer Cali wrote:
    > I have an address database that lists all our suppliers (tblSuppliers). I'm
    > tired of entering all the cities AND zipcodes, so I created a second table
    > (tblZipCode) that lists all the zip codes and what city it is associated
    > with. I then joined these tables together.
    >
    > I have a form on which all the suppliers are tracked (frmSuppliers) that is
    > tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
    > have the city automatically populate based on the zipcode entered. Is this
    > possible? If so, how?
    >
    > --
    > Thank you! - Jennifer
     
  4. JimBadHair

    JimBadHair
    Expand Collapse
    Guest

    Jennifer, if you create a simple query compiling the zip table(and associated
    auto fill information - City, State, County...) with the supplier table, and
    then use the query as the data source for your form, the City, State,
    County... should auto fill in the form.


    "Jennifer Cali" wrote:

    > I have an address database that lists all our suppliers (tblSuppliers). I'm
    > tired of entering all the cities AND zipcodes, so I created a second table
    > (tblZipCode) that lists all the zip codes and what city it is associated
    > with. I then joined these tables together.
    >
    > I have a form on which all the suppliers are tracked (frmSuppliers) that is
    > tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
    > have the city automatically populate based on the zipcode entered. Is this
    > possible? If so, how?
    >
    > --
    > Thank you! - Jennifer
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Jennifer,

    Probably the "purest" approach would be to remove the chrZipCode field
    from the tblSuppliers table. If any given zipcode is always assiciated
    with a particular city, then it is redundant to have both fields in both
    tables. Then, you can make a query, that includes tblSuppliers table
    and tblZipCode table, joined on chrZipCode field, and include the City
    field from the tblZipCodes table in the query. Use this query as the
    Record Source of your frmSuppliers form (and any other form or report
    where a supplier's city is required. I think that will give the
    functionality you require. The proviso I would make here is that on
    your form, you will probably want to set the Locked property of the City
    control to Yes - this is not the place to allow editing of your
    city/zipcode lookup master table.

    --
    Steve Schapel, Microsoft Access MVP

    Jennifer Cali wrote:
    > I have an address database that lists all our suppliers (tblSuppliers). I'm
    > tired of entering all the cities AND zipcodes, so I created a second table
    > (tblZipCode) that lists all the zip codes and what city it is associated
    > with. I then joined these tables together.
    >
    > I have a form on which all the suppliers are tracked (frmSuppliers) that is
    > tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
    > have the city automatically populate based on the zipcode entered. Is this
    > possible? If so, how?
    >
     
  6. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    Hi Steve,
    That all seems to make sense except for the very first sentence. If I remove
    the chrZipCode from tblSuppliers, how would I join together tblSuppliers and
    tblZipCodes? Wouldn't I want to removve the chrCity from tblSuppliers instead?

    --
    Thank you! - Jennifer


    "Steve Schapel" wrote:

    > Jennifer,
    >
    > Probably the "purest" approach would be to remove the chrZipCode field
    > from the tblSuppliers table. If any given zipcode is always assiciated
    > with a particular city, then it is redundant to have both fields in both
    > tables. Then, you can make a query, that includes tblSuppliers table
    > and tblZipCode table, joined on chrZipCode field, and include the City
    > field from the tblZipCodes table in the query. Use this query as the
    > Record Source of your frmSuppliers form (and any other form or report
    > where a supplier's city is required. I think that will give the
    > functionality you require. The proviso I would make here is that on
    > your form, you will probably want to set the Locked property of the City
    > control to Yes - this is not the place to allow editing of your
    > city/zipcode lookup master table.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Jennifer Cali wrote:
    > > I have an address database that lists all our suppliers (tblSuppliers). I'm
    > > tired of entering all the cities AND zipcodes, so I created a second table
    > > (tblZipCode) that lists all the zip codes and what city it is associated
    > > with. I then joined these tables together.
    > >
    > > I have a form on which all the suppliers are tracked (frmSuppliers) that is
    > > tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
    > > have the city automatically populate based on the zipcode entered. Is this
    > > possible? If so, how?
    > >

    >
     
  7. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Jennifer,

    Just checking to make sure you were paying attention <g>.

    Apologies for the slip - thank you, you are exactly right! :)

    --
    Steve Schapel, Microsoft Access MVP

    Jennifer Cali wrote:
    > Hi Steve,
    > That all seems to make sense except for the very first sentence. If I remove
    > the chrZipCode from tblSuppliers, how would I join together tblSuppliers and
    > tblZipCodes? Wouldn't I want to removve the chrCity from tblSuppliers instead?
    >
     
  8. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Jennifer,

    The other "slip" I made was that your query should have a Left Join
    between the tables, just in case someone enters a zipcode that does not
    exist in the tblZipCodes table.

    --
    Steve Schapel, Microsoft Access MVP

    Steve Schapel wrote:
    > Jennifer,
    >
    > Just checking to make sure you were paying attention <g>.
    >
    > Apologies for the slip - thank you, you are exactly right! :)
    >
     
  9. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    Haha - I'm always paying attention (yea, right!). Steve, thank you so much
    for your help - this solution worked perfectly.
    --
    Thank you! - Jennifer


    "Steve Schapel" wrote:

    > Jennifer,
    >
    > Just checking to make sure you were paying attention <g>.
    >
    > Apologies for the slip - thank you, you are exactly right! :)
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Jennifer Cali wrote:
    > > Hi Steve,
    > > That all seems to make sense except for the very first sentence. If I remove
    > > the chrZipCode from tblSuppliers, how would I join together tblSuppliers and
    > > tblZipCodes? Wouldn't I want to removve the chrCity from tblSuppliers instead?
    > >

    >
     
  10. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    Ooh...I don't know how to do this. Can you tell me where the left join goes?
    --
    Thank you! - Jennifer


    "Steve Schapel" wrote:

    > Jennifer,
    >
    > The other "slip" I made was that your query should have a Left Join
    > between the tables, just in case someone enters a zipcode that does not
    > exist in the tblZipCodes table.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Steve Schapel wrote:
    > > Jennifer,
    > >
    > > Just checking to make sure you were paying attention <g>.
    > >
    > > Apologies for the slip - thank you, you are exactly right! :)
    > >

    >
     
  11. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Jennifer,

    In the design view of the query, using exquisite manual dexterity,
    double-click on the line joining the tables. This should open the Join
    Properties dialog. Select the 2nd option, which should say something
    like "all records from tblSuppliers and only matching records from
    tblZipCodes".

    --
    Steve Schapel, Microsoft Access MVP

    Jennifer Cali wrote:
    > Ooh...I don't know how to do this. Can you tell me where the left join goes?
     

Share This Page