Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    jgalloway is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    21

    Zipcode Lookup Autofill

    Hello,

    I know similar questions has been asked before in the form but I cannot find a really good solution.

    I worked with FileMaker a lot more from my previous job. My current job they use MS Access. I am trying to get familiar with Access but I am having troubles. In FM I was able to type a zip code and it would automatically populate the City and State. I have a data base with all of the city and states in access. How can I do the same thing in access like I was able to do in FM?

    When I type the zip the city and state are automatically populated? Please I am VERY new at this and willing to learn.

    Thanks for the help.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jgalloway is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    21
    I've seen this before but its really not making much since to me. Can you kinda spell it out in noobie terms? sorry.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have a combo box that includes all 3 fields in its row source (it can be a query or a table). You use either of those methods to display/save the other columns (typically the combo would be bound to the "main" field, in your case the zip code).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Another approach is to join tables. The RecordSource for the form would include a join to the Zip/City/State table. This will make the City and State fields available. Bind textboxes to these fields, Locked Yes, TabStop No.

    Have to get the JoinType correct "Show all from maintablename ..."

    This does not save the City/State info, just displays it, which is principle of relational database.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    jgalloway is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    21
    I was going to upload the database but its a little to large (12,668kb). Can I email this to somebody to help by chance? Sorry don't mean to be a pain. If I can get this figured out this will solve about 4 other problems.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you run Compact & Repair first? How big is zip file? Up to 2mb allowed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    The rather clunky way would be to use the DLookup() function on the textboxes you want filled in. Say you have:
    txtZip
    txtCity
    txtState

    in the AfterUpdate event of txtZip enter:

    Code:
    Me.txtCity = DLookup("cityField","tableName","zipCode = " & Me.txtZip)
    Me.txtState  = DLookup("stateField","tableName","zipCode = " & Me.txtZip)
    Me.txtCity.Requery
    Me.txtState.Requery
    note: the Me.Requeries might not be necessary if it updates for you.

  9. #9
    jgalloway is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    21
    Here is the zip

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see offhand where you've tried to implement any of the proposed solutions. Where will I find that?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    jgalloway is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    21
    Looks like I sent the one without the changes.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I looked at the SalesReps form. You already had some of my suggestion in use for the Account Managers. Modify the form's RecordSource to include the zipCodes and States tables - note the jointypes and double join to ZipCodes and States:
    SELECT SalesReps.SRID, SalesReps.Territory, SalesReps.Fname, SalesReps.Lname, SalesReps.Company, SalesReps.Address1, SalesReps.Address2, [ZIP Codes].City, States.[State Abbreviation], SalesReps.Zip, SalesReps.Phone, SalesReps.Mobile, SalesReps.Email, SalesReps.StartDate, SalesReps.Active, SalesReps.AMID, AccountManagers.Fname AS Fname_AccountManagers, AccountManagers.Fname AS Fname_AccountManagers1, AccountManagers.Lname AS Lname_AccountManagers, AccountManagers.Phone AS Phone_AccountManagers, AccountManagers.Mobile AS Mobile_AccountManagers, AccountManagers.Email AS Email_AccountManagers, AccountManagers.[Address 1], AccountManagers.[Address 2], [ZIP Codes_1].City, States_1.[State Abbreviation], AccountManagers.Zip AS Zip_AccountManagers, SalesReps.Notes, SalesReps.County
    FROM States AS States_1 RIGHT JOIN ([ZIP Codes] AS [ZIP Codes_1] RIGHT JOIN ((AccountManagers RIGHT JOIN SalesReps ON AccountManagers.[AM ID] = SalesReps.AMID) LEFT JOIN (States RIGHT JOIN [ZIP Codes] ON States.[State Code] = [ZIP Codes].[State Code]) ON SalesReps.Zip = [ZIP Codes].[ZIP Code]) ON [ZIP Codes_1].[ZIP Code] = AccountManagers.Zip) ON States_1.[State Code] = [ZIP Codes_1].[State Code];

    Change the ControlSource property for the City and State fields for both Reps and Managers.

    Now do similar for the AccountManagers form.

    Why don't you have County in the ZipCodes table?

    Eliminate the City and State and County fields from AccountManagers and SalesReps.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    jgalloway is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    21
    County was a last minute thing last night that I was going to need for Sales Reps. I was just trying to get the state city zip to work first. Thank you for the help. I'll go and try this.

  14. #14
    jgalloway is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    21
    I still tried this and it doesn't work completely. It doesn't change anything in the Tables for the account managers. The city and State stay blank. What am I doing wrong?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you modify the SalesReps form RecordSource as I suggested? If so, then is a matter of binding the city, state textboxes for manager and rep to the appropriate fields. There will be two joins each to the States and ZipCodes tables. Reps use fields from the first, managers use the second (with 1 suffix). This all works for me.

    Same will apply to County when you include it in the ZipCodes table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Having trouble with autofill
    By Kerrydunk in forum Forms
    Replies: 1
    Last Post: 04-25-2011, 11:26 PM
  2. Yet Another autofill question
    By srcacuser in forum Database Design
    Replies: 1
    Last Post: 01-29-2011, 11:05 AM
  3. Autofill
    By kdcooper88 in forum Access
    Replies: 1
    Last Post: 09-18-2010, 05:52 AM
  4. ComboBox Autofill
    By t_dot in forum Forms
    Replies: 2
    Last Post: 08-19-2010, 06:18 AM
  5. Looking up a zipcode
    By rbw95662 in forum Forms
    Replies: 1
    Last Post: 03-24-2010, 05:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums