Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auntomatically inserting info (Access XP)

    I want to be able to type in a zip code number and have the city and state automatically inserted. I can get the city to be entered but can't get the state to be entered automatically. I have a three column zip, city, state table. Used a query and sorted by zip. Did a Lookup Wizard for the table to hold the data. I added this code as an after update event. The St part is ignored.

    Private Sub Zip_AfterUpdate()
    Dim IntI As Integer
    City = Zip.Column(1)
    St = Zip.Column(1)
    End Sub

    In the St textbox I get the City repeated.

    And the simple answer to the problem is...

    Thanks for the help. This has been a problem I have wanted to solve for some time. Fay

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auntomatically inserting info (Access XP)

    Yep - common problem with combo boxes - change your code to read
    <font color=blue>
    St = Zip.Column(2)
    </font color=blue>
    and things should get better. BTW, that assumes you have all three fields in your row source for the combo. Columns in combo boxes start with zero (0), so that would be displaying the zip code, City would be column 1, and state would be column 2.

    A couple of observations - there are more than 40,000 zip codes in the US, and they do change from time to time, so you may be creating a maintenance headache, as well as a slow response issue with that many records as the rowsource for a combo. There are a couple of products out there that do address validation - we've had good success with something called Mailers+4 that does address validation based on street address and 5-digit zip. (It also does lots of other stuff). Using it does require writing some code, but it cut down address errors for one of our clients dramatically.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auntomatically inserting info (Access XP)

    Thanks Wendall but sadly it didn't work. I had tried 2 for the state. I have checked everything to make sure things were properly named etc. I retyped the code. I changed the City to read 2 and it didn't bring up the state it left the space blank, yes there are states listed in the column. I will just place the appropriate state in the default for St.

    I am not to worried about the Zips the database is for a small association so I don't see it as a major issue at this time.

    I do have one other problem with the zip in that when I type 4 it autofills the first 4 zip in the list. Can I turn that off. Because the user then has to go back and retype the number.

    Thanks Fay

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auntomatically inserting info (Access XP)

    If that's the case, something is not right with the combo box row source or the combo box itself. Your combo box has to be set to 3 columns - you can make the width 0 to hide them if you want. Is your row source the zip/city/state table, or is it a query? Also, are entries nearly all in the same state? Or at least in a few neighboring states? We were working with about 100,000 people in one case, all over the world, so it got to be a big deal for us.

    As to your 4 autofill, if you turn off the AutoExpand property of the combo box, and that should stop it from completing to the first 4-zip. But what do you do if someone types in a zip that you don't have an entry for? Are you using the LimitToList property?
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auntomatically inserting info (Access XP)

    Bingo. It was the number of columns. The Auto Expand cleared up that problem and the Limit to list is set to no. Again thank you.

    Fay

Posting Permissions

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