Update a field based on another value entered on a form

Posted on 2011-05-01
Last Modified: 2012-05-11
I have a form where the user enters a customer's name and address.... all that goes into one table: tPatient

I also have another table (tZip) that contains two fields, zip and city.

I would like to set the main form so when the user enters the zip the City will automatcially update correctly on the form based on the users entered zip code for a particular customer.

How can this be done via VBA?

Thank you.
Question by:thandel
    LVL 42

    Accepted Solution

    On the patient form, use a combo box for the zip code and a text box for the city.  Set the properties as follows:
       rowsource: "Select Zip, city from tZip"
       bound column = 0
       column count = 2

    In the after update event of the combo box:
      me.CityTextboxName = me.ZipComboFieldName.column(1)

    If you want to get fancy, you could make the city a combo box, as well and work it the other way.  Then, the user can choose the city/zip from either combo.


    Author Comment

    Thanks, when you say set the properities is that for the combo box and just leave the text box alone?

    I tires this but after update the zip code the city text box remains null.

    Author Comment

    Ah sorry got it working had the rowselect incorect.

    Author Comment

    Thanks!  Can't really go the other way as some cityies have more than one zip code.
    LVL 42

    Expert Comment

    I believe my solution had a typo:  bound column should = 1, not 0

    >Can't really go the other way as some cityies have more than one zip code.

    Not a both the city and the zip in the dropdown.  User can pick from among them:

    RowSource:  Select city, zip from yourtable
    Column Count = 2
    Bound Column = 1


    Author Comment

    Thanks I've started another thread as new issues came up. 26988400  (Which looks like you jumped on too)  :)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now