Zip Codes

[Guest]

I have a table "zip codes" (Iowa city name and zip code) and a table "Worker"
which includes a zip code field. When I type the city name, I want the zip
code to automatically fill in and be stored in the Therapists table. For big
cities, the zip code only has 3 digits and I fill in the other two based on
the address. DLookup doesn't work because you can't edit the value.
Filemaker does this easily, so I am sure it can be done. Anyone have any
ideas?

 

[Rick B]

I would look at the Northwinds database order form. You select the
customer, and it fills in the customer address data. I believe it does it
with a multi-column combo box and an after-update routine.

BUT, I would say you are doing it backwards. You are saving the user from
typing five digits, and making them type the name exactly as it is in the
table. Big cities, like mine, can have twenty ZIP codes.

If I were you, I'd copy the general workings of the Customer field in the
Northwinds database, but I'd enter the ZIP and have the city and state fill
in, not the other way around.

Hope that gets you on track.

 

[Guest]

Sorry--I need to correct how I stated the question below. I should have
written ...When I type the city name, I want the zip code to automatically
fill in and be stored in the Worker table....

 

[Joseph Meehan]

Sorry--I need to correct how I stated the question below. I should
have written ...When I type the city name, I want the zip code to
automatically fill in and be stored in the Worker table....

I agree with Rick.

You want the user to type in the zip and let the program add the name.
You will far less bothered by mis-spelled names and the user will need to
type in less. You can also have full zip codes rather than just thee digits.

I also think looking at Northwind is a good idea.

 

[Guest]

Rick--Without going into the reasons why I want to do this, is there any way
you know of to have Access look up the value from a related table, in such a
way that the looked up value is editable once it is inserted into the field?
Thanks!

Margaret

 

[Douglas J. Steele]

Sure. Put code into the AfterUpdate event of the field where you typed in
the city name to populate the zip code field.

Private Sub txtCityName_AfterUpdate()

Me.txtZipCode = DLookup("ZipCode", "LookupTable", _
"CityName = '" & Me.txtCityName & "'")

End Sub

It just doesn't make sense, though!

 

[jwm]

You might want to evaluate Chris St. Valentine's csvZIP Lookup tool at
http://www.csvtools.com/