Updating City, State when zip is entered

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I know this has probably been asked a lot, but I can't find anything on it
doing a search. I want to be able to update the city and state fields when
a zipcode is placed. I know I do it On Update. I also have a query that
pulls the three fields.

Thanks for your help.

Paul
 
If you are going to rely on Zipcode to provide the city and state, there is
no need to store anything but the zipcode. Just join to the zip table to
obtain the city and state names. Make sure that you set the locked property
to Yes for city and state to prevent accidental changes.
 
Great, thank you, that will work. Another question, if I may, I have a
[Region] field that I would like to fill based on the [City], [State],
[Zip]. Well actually the [City] and [State]. I have another query based on
another table. (City Regions) It has [City ID], [City], [DRegion],
[CRegion] and [Region]. [Region] is calculated, combining the D &C Regions.
I would like to fill-in the [Region] on the main form with the value of
[Region] in City Regions. Kind of like IIF([State] = "CT", Find where
Main[City] = City Regions[City] put City Regions[Region] into Main[Region],
"N/A") Is something like possible, if so how?

Thanks again

Paul
 
Okay, I was able to get IIf([State]="CT",DLookUp("Region","Main: Town
Regions Query","Town = " & "City"),"N/A") working on a form. Was not able
to get it working in query. Access is reporting that it can't find "City",
though it is right there in the query.

So my thinking is now, if I can get the value from a hidden field, or even a
variable, How can I write it to the [Region], on the main form, so it
updates the table?

Thanks for your help.

Paul


Paul said:
Great, thank you, that will work. Another question, if I may, I have a
[Region] field that I would like to fill based on the [City], [State],
[Zip]. Well actually the [City] and [State]. I have another query based
on another table. (City Regions) It has [City ID], [City], [DRegion],
[CRegion] and [Region]. [Region] is calculated, combining the D &C
Regions. I would like to fill-in the [Region] on the main form with the
value of [Region] in City Regions. Kind of like IIF([State] = "CT", Find
where Main[City] = City Regions[City] put City Regions[Region] into
Main[Region], "N/A") Is something like possible, if so how?

Thanks again

Paul

Pat Hartman(MVP) said:
If you are going to rely on Zipcode to provide the city and state, there
is no need to store anything but the zipcode. Just join to the zip table
to obtain the city and state names. Make sure that you set the locked
property to Yes for city and state to prevent accidental changes.
Click to expand...
Click to expand...
 
I guess I don't understand why you are not just joining to the table by
using CityID. DLookup()s do not belong in queries. Each domain function
runs a distinct query. So if your recordset contains 100 rows, 100 lookup
queries will be run. This can get very slow, very fast. Also your syntax
is incorrect. You have City enclosed in quotes rather than square brackets.

Paul said:
Okay, I was able to get IIf([State]="CT",DLookUp("Region","Main: Town
Regions Query","Town = " & "City"),"N/A") working on a form. Was not able
to get it working in query. Access is reporting that it can't find
"City", though it is right there in the query.

So my thinking is now, if I can get the value from a hidden field, or even
a variable, How can I write it to the [Region], on the main form, so it
updates the table?

Thanks for your help.

Paul


Paul said:
Great, thank you, that will work. Another question, if I may, I have a
[Region] field that I would like to fill based on the [City], [State],
[Zip]. Well actually the [City] and [State]. I have another query based
on another table. (City Regions) It has [City ID], [City], [DRegion],
[CRegion] and [Region]. [Region] is calculated, combining the D &C
Regions. I would like to fill-in the [Region] on the main form with the
value of [Region] in City Regions. Kind of like IIF([State] = "CT", Find
where Main[City] = City Regions[City] put City Regions[Region] into
Main[Region], "N/A") Is something like possible, if so how?

Thanks again

Paul

Pat Hartman(MVP) said:
If you are going to rely on Zipcode to provide the city and state, there
is no need to store anything but the zipcode. Just join to the zip
table to obtain the city and state names. Make sure that you set the
locked property to Yes for city and state to prevent accidental changes.


I know this has probably been asked a lot, but I can't find anything on
it doing a search. I want to be able to update the city and state
fields when a zipcode is placed. I know I do it On Update. I also have
a query that pulls the three fields.

Thanks for your help.

Paul
Click to expand...
Click to expand...
Click to expand...
 
Back
Top