I am trying to link two different fields in a table called 'Customers'. The first field is a zip code and the second is a city name. I have a list of cities and corresponding zip codes in another table which I have linked to each of the two fields in the 'Customers' table. What I would like to do is click down on my combo box and when I choose a city the corresponding zip code automatically is entered into the next field. Or if I enter the zip code the city is automatically entered. I would appreciate any help. Thank you.
Linking fields in a table (1 Viewer)
- Thread starter tjr222
- Start date
David R
I know a few things...
- Local time
- Today, 00:10
- Joined
- Oct 23, 2001
- Messages
- 2,633
Don't try to do this in the table..
There are two ways to approach what you want to do, depending on whether the ZIP code uniquely determines the city, or if some cities share ZIP codes. Kansas City and Raytown both share 64131, for example.
In your table are there any entries where a single ZIP code is entered twice for two different cities, or do you ever have to manually change the default city to a specific one?
If the answer is yes then you need to store the city value in your table. If the answer is no you can store the ZIP Code and pull up the corresponding city at any time from your other table. See this thread for how to do this either way: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=21620
There are two ways to approach what you want to do, depending on whether the ZIP code uniquely determines the city, or if some cities share ZIP codes. Kansas City and Raytown both share 64131, for example.
In your table are there any entries where a single ZIP code is entered twice for two different cities, or do you ever have to manually change the default city to a specific one?
If the answer is yes then you need to store the city value in your table. If the answer is no you can store the ZIP Code and pull up the corresponding city at any time from your other table. See this thread for how to do this either way: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=21620
The answer to your question is yes, I do have zip codes with more than one city. So I would have to store both the city and the zip code, which I already have done in another table. After looking at thread you suggested it looks like this may be a little more challenging than I thought. I should have noted that I am not very advanced on access. Anyway if you could explain a little more I would appreciate it. Thanks again.
David R
I know a few things...
- Local time
- Today, 00:10
- Joined
- Oct 23, 2001
- Messages
- 2,633
Rereading your initial posting, it sounds like you wanted to be able to enter either the city or the zip code and have the other part fill in automatically. This is possible, but it will be simpler/faster if you can choose one to enter and stick with it. Probably the one that is first in your form's data entry (City?).
If City is a combo box, then include the ZIP Code column of that table as well, but make it a hidden column (Column Width: 0"). Then in the AfterUpdate event of your combo box, go into the Code Builder and add this line:
Me.ZipCodeControl = Me.comboCity.Column(2)
Where ZipCodeControl is the name of the ZIP Code field on your form, and comboCity is the name of your City combobox. You may have to adjust the column number to get it to work right, but I think that's correct, assuming there are no other fields in the combo. If there is a City,State,ZIP in that table, then State might be (2) and ZIP might be (3).
To do it in either direction your best bet is going to be to use DLookup.
If City is a combo box, then include the ZIP Code column of that table as well, but make it a hidden column (Column Width: 0"). Then in the AfterUpdate event of your combo box, go into the Code Builder and add this line:
Me.ZipCodeControl = Me.comboCity.Column(2)
Where ZipCodeControl is the name of the ZIP Code field on your form, and comboCity is the name of your City combobox. You may have to adjust the column number to get it to work right, but I think that's correct, assuming there are no other fields in the combo. If there is a City,State,ZIP in that table, then State might be (2) and ZIP might be (3).
To do it in either direction your best bet is going to be to use DLookup.
