Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    ZIP code combo box (XP)

    My form displays donors for a college fund-raiser; a subform displays the items donated by each donor. In the main form, I have fields for all the usual items to identify the donors: Name, address, phone, etc. In the form header is a search list box--the user enters a donor name to select it from a list. When the user presses <Enter>, the form displays all the stuff about the donor and his or her donation(s).

    One text box on the main form displays ZIP codes, and I want to change it to a combo box--the idea is for the user to enter the appropriate ZIP code. On Tab, I want to automatically populate the fields for City and State. So I made a copy of the whole DB to do my experimenting. I changed the text box to a combo box bound to the CityZip field in the underlying query. What happened next took me by surprise.

    The combo box works OK, but now my search box--indeed the form itself--displays only four of the 131 records in the table! Those four records happen to be the only ones that have all three items from the CityZip table (City, State, ZIP code) filled in on the Donors table. The other records are missing one, two, or all three pieces of that address information. The idea is for the user to use this form to fill in all that information. I want this combo box to work as described so as to speed up data entry. We can't do that if the form won't even display the records that need updating! What have I missed here?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: ZIP code combo box (XP)

    I'm really confused as to what you are doing. You mention a CityZip table. Do you have a separate table that has the Cities and zip codes in it? And what is the query behind the form? Is it based on the donor's table? Or joined with another table? You said your combo box was bound to the CityZip field in the underlying query. Which table has this CityZip field? And why do you have a CityZip field?

    Assuming you have some sort of donor table, and that table has City, State, and Zip fields. I would have a ZipCode combo box that draws from the donor table. The rowsource would be something like this:
    Select Distrinct ZipCode, City, State from Donors ORDER BY ZipCode

    As soon as the user starts to enter a zip code, have the combo box dropdown the list. They can see which cities are associated with a given zip code (frequently a zip code will have more than one city). In the combo box's AfterUpdate event, you'd want some code like this (I'm making assumptions about control names):
    txtCity = cboZipCode.column(1)
    txtState = cboZipCode.column(2)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: ZIP code combo box (XP)

    Hello, Mark, and thanks for responding. My apologies for the misinformation. Yes, I have a separate table that I call CityZip--it stores City, State, and ZIP code fields. (This is a very local affair--fewer than 50 localities, so it was pretty easy to build the table. I'm using strictly 5-digit ZIP codes, and they're all in Virginia.)

    The form is based on a query (qry_Donors2003), which draws from the table of the same name. The Donors table has separate fields for City, State and ZIP code. (Come to think of it, since I have another table for CityZIP, I should need just the ZIP code field in the table...right?)

    The combo box you describe is exactly what I was trying to create. The form started life with text boxes for ZIP code/City/State. The user had to key in each item separately. I wanted to enable the user to key in the ZIP, and then have the other two fields populate themselves automatically, just as you describe. I've seen instructions for doing that in many other posts as well. But when I changed the ZIP field from text box to combo box, all of a sudden my search box quit working. All it would display were four records--those four records happen to already have city/state/ZIP information filled in on the source table and query. The other 151 records were invisible, so I could no longer use the form to update their information. I just don't understand why that happened.

    I dumped the copy of the DB that had the misbehaving form. But I'll attach a zipped copy of the DB--I'll be grateful for any comments and suggestions you might want to add. Thanks for the help!
    Attached Files Attached Files

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

    Re: ZIP code combo box (XP)

    In the attached database, I have removed the city and state fields from the donors table. In a relational structure, they don't belong in the donors table. They are picked up in the query. In the form, the city and state are filled in automatically of a zip code is selected from the zop combo box. I have locked the city and state text boxes.

    Note: the zip code was numeric in some tables and text in others. I have changed it to numeric throughout, added some relationships, and removed most indexes - you had several double indexes on your tables. Acces by defautl will add indexes for fields with certain names. You can see these in Tools/Options..., Tables/Queries tab, AutoIndex. I prefer to clear the AutoIndex box, and set all indexes myself.
    Attached Files Attached Files

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ZIP code combo box (XP)

    <P ID="edit" class=small>(Edited by patt on 03-Mar-03 10:46. Changed to show the delete of requery)</P>Hi Lucas

    I changed the AfterUpdate event of the cboSelectDonor to include the statement instead of the Me.Requery:
    Me.Filter = ""

    I also changed the ZipCode field from a text box to a ComboBox and in the AfterUpdate event of this ComboBox filled the State and City fields.

    I have included your amended DB.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: ZIP code combo box (XP)

    Wow! Pat, thank you very much! You've gone above and beyond! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: ZIP code combo box (XP)

    Hans, Many thanks for your assistance! I suspect that mix of numeric and text ZIP code fields was behind some of the problems I was having.

Posting Permissions

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