inputting zip code and having it autofill city and state

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the table of zips with city and state, but can't figure out how to reference it so that when I'm in my order form, and tab off the completed zip code field, that it will autofill the city and state. I'm sure this is a basic question, but will someone please help me? Thanks in advance for any advice.

Mark
 
I would take the following approach:

- Define the city and state controls as combo boxes as a table/query type.
For the row source, the wizard will direct you through the generation of the
SELECT code to access the city and/or state values as the row source.

- Modify the generated SELECT code in both city and state controls by adding
a WHERE clause referencing the value of the zip control.

- In the AfterUpdate event of the zip control, specify a requery of the city
and state controls.

The only option value that will be available in city and state will be those
associated with the zip code.
By the way, are you aware that some zip codes cover multiple cities? USPS
has been working on this, but I'm not sure of their status.

-Jim

Mark D said:
I have the table of zips with city and state, but can't figure out how to
Click to expand...
reference it so that when I'm in my order form, and tab off the completed
zip code field, that it will autofill the city and state. I'm sure this is
a basic question, but will someone please help me? Thanks in advance for
any advice.
 
Hi Mark,

Here is what I would do:

1. Make the control on your order form for zip code a combo box.

2. Set the properties of your combo box as follows:

Row Source: Select ZipCode, City, State from tblZipCodes Order by ZipCode
(change the field names and table name to correspond with your zip codes
table)
Column Count: 3
Bound Column: 1
Control Source: name of zipcode field in your Orders table

3. In the After Update event for your combo box, insert the following:

Me!City = Me!MyComboBox.Column(1)
Me!State = Me!MyComboBox.Column(2)



hth and post back if you have any questions,


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Mark D said:
I have the table of zips with city and state, but can't figure out how to
Click to expand...
reference it so that when I'm in my order form, and tab off the completed
zip code field, that it will autofill the city and state. I'm sure this is
a basic question, but will someone please help me? Thanks in advance for
any advice.
 
Hey Cheryl!
Nice solution! I did not know the unbound columns could be referenced like
that.
I learned something.
Thanks
-Jim Shaw
 
I have tried the following in my Zip_AfterUpdate:
If Not IsNull([Zip]) Then
Zip5 = Left([Zip], 5)
[City] = DLookup("ZipCity", "ZipList", "[zipID]='" & Zip5 & "'") 'check
the spacing carefully here for " and '
[ST] = DLookup("ZipST", "ZipList", "[zipID]= '" & Zip5 & "'")

If Trim([City]) = "" Or IsNull([City]) Then
CityFlag = True
City.SetFocus
Else
HPhone.SetFocus
End If
End If

and this in State_Exit:
If Len([Zip]) > 0 Then '9/28/01
If CityFlag Then
Zip5 = Left([Zip], 5)
CityString = [City]
STString = [ST]
DoCmd.OpenForm "ZipListForm", acNormal, , , acFormAdd
Forms![ZipListForm]![ZipID] = Zip5
Forms![ZipListForm]![ZipCity] = CityString
Forms![ZipListForm]![ZipST] = STString
DoCmd.Close
End If
End If

Thus, if the Zip is in the ZipList table, State and City are filled in,
otherwise (if cityflag is set) the new data typed in are added to the table
for the next time. Works for me...


Steve
 
The code might work but relating the zip list and the table you want to keep
the zip code in will do several things.
1. Not require any code to fill.
2. Maintain a proper relationship. (not real important here but a good rule
to follow.)
3. Reduce the code needed to update with a new zip code to four lines in
the not in list event.
a. A message to ask if you want to update.
b. If yes, Open a form with the ZIP filled in.
c. Requery the ZIP table.
The forth line would be End If


Stephen Rockower said:
I have tried the following in my Zip_AfterUpdate:
If Not IsNull([Zip]) Then
Zip5 = Left([Zip], 5)
[City] = DLookup("ZipCity", "ZipList", "[zipID]='" & Zip5 & "'") 'check
the spacing carefully here for " and '
[ST] = DLookup("ZipST", "ZipList", "[zipID]= '" & Zip5 & "'")

If Trim([City]) = "" Or IsNull([City]) Then
CityFlag = True
City.SetFocus
Else
HPhone.SetFocus
End If
End If

and this in State_Exit:
If Len([Zip]) > 0 Then '9/28/01
If CityFlag Then
Zip5 = Left([Zip], 5)
CityString = [City]
STString = [ST]
DoCmd.OpenForm "ZipListForm", acNormal, , , acFormAdd
Forms![ZipListForm]![ZipID] = Zip5
Forms![ZipListForm]![ZipCity] = CityString
Forms![ZipListForm]![ZipST] = STString
DoCmd.Close
End If
End If

Thus, if the Zip is in the ZipList table, State and City are filled in,
otherwise (if cityflag is set) the new data typed in are added to the table
for the next time. Works for me...


Steve

Cheryl Fischer said:
Hi Mark,

Here is what I would do:

1. Make the control on your order form for zip code a combo box.

2. Set the properties of your combo box as follows:

Row Source: Select ZipCode, City, State from tblZipCodes Order by ZipCode
(change the field names and table name to correspond with your zip codes
table)
Column Count: 3
Bound Column: 1
Control Source: name of zipcode field in your Orders table

3. In the After Update event for your combo box, insert the following:

Me!City = Me!MyComboBox.Column(1)
Me!State = Me!MyComboBox.Column(2)



hth and post back if you have any questions,


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

to
reference it so that when I'm in my order form, and tab off the completed
zip code field, that it will autofill the city and state. I'm sure this is
a basic question, but will someone please help me? Thanks in advance for
any advice.
Click to expand...
Click to expand...
 
Back
Top