**My original database was built in Access 2007. I am now trying to upgrade and make changes and it is in Access 2019**
Im pretty desperate on figuring this out and have been searching the internet and asking questions on whatever forums I can...
There seems to be a lot of back and forth on typing in a zip code and having it autofill the city and state (and in my case, I'd like county). I'm not sure if there has ever been one final definitive method of getting this done. Here is what I have in place now.
tblAddress
pkAddressID
fkClientID
fkAddressTypeID
StreetAddress
fkCityID
fkStateID
Zip
fkCountyID
Here is how it works now. I have a main client form with an address subform. The city, state, and county are combo boxes. The user can start typing a name and it will autofill or drop down to select a name, or type in complete new name. There is an after update event to check if the name typed in each of these already exists and if not, they are allowed to add it. This is how the city, state and county tables were built. These tables consist of only an autonumber pk and the name of the city, state or county for their respective tables. The zip code property on my form is a text box. It is allowed to be typed in and repeated as needed. I probably should have thought that thru further.
Now I want to make it so that when a user types in the zip code on my address form, it autofills city state and county. I have a list for the whole country of zips, city, state, county, more on that later. I also have about 7,000 records already in the address table.
In researching this, I find two methods to achieve this. Put zip code, city, state, county into one table as text fields and use DLookup or Vlookup to autofill in the other text boxes on my form...not sure how this records them in the linked tblAddress because I stopped researching when I hit upon another method which is to keep the separate tables and either create a linking table or query that fills in the fields. This started going beyond my coding skills and understanding. So I thought I'd start with the basic question... Since I have this set up with those tables and normalization rules (I see now that not the zip code though, I'm unsure why I didn't do that in its own table either other than I'm still a self teaching beginner).
Anyway, my question is what is the best method to follow with in the way I have my database set up now? I have discussed a couple of things as far as putting all these tables (zip, city, state, county) into its own separate linked back end to keep the speed and size of the database optimized. I also saw in another post, that regardless of which method I use, I shouldn't just import the entire list of zip codes etc for the entire country. We wont use them. We will use portions of about six states and then a few miscellaneous ones here and there. That my look up or query should first search the zip codes I already have and then continue what we've been doing...adding them on the fly as needed. I see now that this would have optimally worked if those zip codes were in there own table. If I need them in their own table, I'm going to need to know how to change them from text field to a foreign key for that table.
I also realize that some zip codes span more than one city, state or county. Ultimately I'd like to handle that with letting the user just correct it in the combo or text box and having a message asking them if they are sure...
I think that is the simpliest way to handle that, but suggestions are appreciated.
I made this database many years agolearning as I went along with lots and lots of help from forums like this. I’ve used vba and bit here and there since then but basically starting from scratch. I really need to know first steps from what I have. I imagine there must be lots of people this could help too.
I’m leaning toward keeping separate tables and creating a linking table... is this the best method or just the most difficult?
I'd really appreciate any and all assistance that I can get with this.
Thank you,
Kathy
Im pretty desperate on figuring this out and have been searching the internet and asking questions on whatever forums I can...
There seems to be a lot of back and forth on typing in a zip code and having it autofill the city and state (and in my case, I'd like county). I'm not sure if there has ever been one final definitive method of getting this done. Here is what I have in place now.
tblAddress
pkAddressID
fkClientID
fkAddressTypeID
StreetAddress
fkCityID
fkStateID
Zip
fkCountyID
Here is how it works now. I have a main client form with an address subform. The city, state, and county are combo boxes. The user can start typing a name and it will autofill or drop down to select a name, or type in complete new name. There is an after update event to check if the name typed in each of these already exists and if not, they are allowed to add it. This is how the city, state and county tables were built. These tables consist of only an autonumber pk and the name of the city, state or county for their respective tables. The zip code property on my form is a text box. It is allowed to be typed in and repeated as needed. I probably should have thought that thru further.
Now I want to make it so that when a user types in the zip code on my address form, it autofills city state and county. I have a list for the whole country of zips, city, state, county, more on that later. I also have about 7,000 records already in the address table.
In researching this, I find two methods to achieve this. Put zip code, city, state, county into one table as text fields and use DLookup or Vlookup to autofill in the other text boxes on my form...not sure how this records them in the linked tblAddress because I stopped researching when I hit upon another method which is to keep the separate tables and either create a linking table or query that fills in the fields. This started going beyond my coding skills and understanding. So I thought I'd start with the basic question... Since I have this set up with those tables and normalization rules (I see now that not the zip code though, I'm unsure why I didn't do that in its own table either other than I'm still a self teaching beginner).
Anyway, my question is what is the best method to follow with in the way I have my database set up now? I have discussed a couple of things as far as putting all these tables (zip, city, state, county) into its own separate linked back end to keep the speed and size of the database optimized. I also saw in another post, that regardless of which method I use, I shouldn't just import the entire list of zip codes etc for the entire country. We wont use them. We will use portions of about six states and then a few miscellaneous ones here and there. That my look up or query should first search the zip codes I already have and then continue what we've been doing...adding them on the fly as needed. I see now that this would have optimally worked if those zip codes were in there own table. If I need them in their own table, I'm going to need to know how to change them from text field to a foreign key for that table.
I also realize that some zip codes span more than one city, state or county. Ultimately I'd like to handle that with letting the user just correct it in the combo or text box and having a message asking them if they are sure...
I think that is the simpliest way to handle that, but suggestions are appreciated.
I made this database many years agolearning as I went along with lots and lots of help from forums like this. I’ve used vba and bit here and there since then but basically starting from scratch. I really need to know first steps from what I have. I imagine there must be lots of people this could help too.
I’m leaning toward keeping separate tables and creating a linking table... is this the best method or just the most difficult?
I'd really appreciate any and all assistance that I can get with this.
Thank you,
Kathy