auto fill in second text field 1

[traffas]

Thank you for taking the time to look at this thread......

I have a form where the use enteres data, text field 1 is for the city and text field 2 is for the zip code. I have a table [reftblCityZip] where common city are listed in [city] and corresponding zipcodes are listed in [zipcodes].

I can write in vba code "if [city] = xxxxxx then [zipcodes]= yyyyy". But I would like for the program to look up the city and the zip code in [reftblCityZip] and then enter the zipcode in the second text field. Then all I have to do is merely update the table when new other cities become used frequently. If the city does not exist in the table, the the second text field would be blank or "". I can write the query to find the zipcode but i have been uable to figure out how to get the result of the query into the second text field.....................

Once again, thank you for your time........

Shawn

I thank all of you. What you do for others is very special. What you are is special.

Shawn

 

[FancyPrairie]

I have the user enter the zip code rather than the city because 2 cities could have the same name. I will show you how I do it, but you can turn it around to do it like you want.

First I create a query that includes the following fields: zip code, city, state. Then I create a combobox (cboZipCode) whose RecordSource is set to the query and the bound column is set to the zip code. I also include 2 unbound text boxes on the form. The first text box represents the city and its ControlSource property is set to =cboZipCode.column(1). The ControlSource of the 2nd text box is set to =cboZipCode.column(2). Now, when the user enters a zip code, the city and state are automatically populated.

If the user enters a zip code that is not in the list, the NotInList event of cboZipCode asks the user if he/she wants to add it to the list. If so, a form is diplayed with a new record displayed. The form contains 3 fields: Zip Code (which is already filled in since the user has already told you what it was via the first form's combobox), City and State. Once the user enters this info., the combobox is automatically updated with the new info. Note that you will need a Cancel button on this form in case the user changes his/her mind about adding the new zip code.


However, there are files on the net that contain all of the zip codes listed in the US (assuming this app is to be run in the US). If you use one of these files you won't have to worry about the user entering a zip code that does not exist in your table.

 

[traffas]

Thank you, this is exactly what I was looking for......
I ought to give you two stars..........

Once again ...... thanks........
shawn I thank all of you. What you do for others is very special. What you are is special.

Shawn