Fill-In Tables/Forms (Access 2002 SP1)

[JudyT]

I want to be able to have a "Zip Code" table that has city, state and county information connected to the Zip Code that will be the primary key. I used to use Paradox years ago, and they had a feature where you could use "fill-in" forms and I used that to fill in the city/state/county info by keying in the zip code. How does one do this in access? Look-up tables aren't what I want to do, as it is limited to one column, or so it seems to me.

Any ideas. Thanks in advance.

[charlotte]

Do you realize that zip codes don't apply to just city/state designations? Some buildings have their own, and towns too small for their own post office may share a zip code.

As to your question, you do it wtih code, but there is no built in function to fill in values in a form, you have to create it yourself. If by "lookup tables", you're referring to using DLookup, then yes, it returns a single value. If you explain more about what you need to accomplish, someone should be able to help you find a workable solution.

[WendellB]

There are also a couple of pretty sophisticated products that you can use to create a zip code based on street, city and state, or you can populate city and state based on zip. The one we have used most recently is called Mailers+4 - it will calculate zip+4, delivery codes, carrier routes and other such stuff, and is updated on a regular basis. (There are several zip code changes, either in boundaries or in adding new ones each month when you look at the entire US.)

[JudyT]

Yes, thank you...I am aware that zip codes don't "just" apply to city/state designations, but for my purposes, all I want to accomplish is for the data entry clerk to key in the zip code and have the city, state and county fields automatically filled in with the appropriate items, from a zip code table I have created. This is not a huge database, it will be no more than 2-3K records, it is regional, there will be no "plus 4" zip codes. Just something simple, like it used to be with paradox. Thanks

[charlotte]

Well, unless there are some Paradox programmer lurking in this board who can translate "like it used to be with paradox", you're going to have to explain exactly what you want to have happen. There is almost nothing "just like Paradox" about Access, most especially if you are talking about the DOS version of Paradox rather than one of the later Windows versions.

[MarkD]

I know nothing about Paradox, as noted, Access is not Paradox. Another product you may want to take a look at is FMS Inc -Total ZipCode Database. According to web site, this product allows you to "create forms so your users can simply type in the zip code and automatically fill the corresponding city and state fields. No need to type the names, misspell them, or have zip codes and cities that don't match. You can even have drop down lists for zip codes with multiple city names." The product provdes you with "the official USPS zip code database with the list of every valid zip code and their acceptable city and state names across the entire USA. That

[MarkD]

<P ID="edit" class=small>(Edited by MarkD on 02-Aug-03 04:47. Added 2nd MSKB link.)</P>PS - If not interested in pricey add-ins, there's a technique you can use in Access to do this, termed "Row fix-up" or "AutoLookup" by MS. For an example of how this works refer to following MSKB article (209672):

ACC2000: Example of Row Fix-Up in the Northwind Orders Form

As noted in article, "You can use this feature to enter a single value, and Microsoft Access automatically looks up and displays other values for that record. Row fix-up enables you to update forms and queries without using macros or code." Article notes: "For more information about row fix-up, click Microsoft Access Help on the Help menu, type create an autolookup query that automatically fills in data in the Office Assistant or the Answer Wizard, and then click Search to view the topic."

Another technique is using the Column property of a combo box to update text fields on a form. For example see MSKB Article 209738:

ACC2000: How to Use the Column Property of a Combo Box to Update a Text Box

You may be able to adapt one of these techniques for a zip code lookup feature. They work same way in ACC 2002 as in ACC 2000.

HTH

[JudyT]

Mark, thank you so much for the information. That's exactly what I was looking for. The Link to MS Support is very helpful as well. Thanks!

[patt]

Which one did you go with, the Row-Fixp or the Column property solution?