Data Entry (Zip Code Lookup)

[tjabaut]

I am having a lot of difficulty getting something I thought would be basic to work. I am fiarly new to database design so bare with me.

I have created a customer support database for my clientele. They are located all throughout New York State, but I also have clients in other states as well.

Since the bulk of my clients reside in NY I have created a Zip Code table with the ZipCode as the primary key. It contains the Zip, City and State.

I want to be able to go to the client info form for a new client and simply enter in the zip code and have the city and state autopopulate. I have gotten that to work; however; if the zip does not currently exist I get the following error message, "The Microsoft Jet database engine could not find a record in the table 'ZipCode' with key matching field(s) 'Zip'."

What does this mean.

I am using Access XP (2002) The tables (customer and zipcode) are linked on the zipcode as it is unique and the form is pulling data from both tables.

Any help would be appreciated. I am sure that I can write code to accomplish this; however; I would have thought the ability to add records through a Data Entry Form should be part of the basic functionality of this product.

 

[chewy]

you would have to post your code

 

[tjabaut]

Thats just it, there is not currently any code whatsoever.

I am able to pull the state and city based on entering in the zip code. The tables are linked on the zip code field, so if there exist a relationship the resulting fields (city, state) populate the text boxes on the form.

My problem reisde in the fact that if I enter a client in Florida, that does not exist in my New York State database of zipcodes, the program fails based on an equality relationship and gives me an error.

What I am looking to do, is upon having the lookup fail for equality, then have it APPEND the new data (i.e. city=Tampa, state=FL, zipcode=33635) to the zipcode table.

 

[FoFa]

"The Microsoft Jet database engine could not find a record in the table 'ZipCode' with key matching field(s) 'Zip'."

Is telling you don't have this one in the table.
You could check to see if it exist first, and if not then append the new data to your table. One way to do this is with a dcount to see if returns zero or > zero.

 

[tjabaut]

That sounds exactly like what I want. My next question is HOW. Is there an easy way to integrate an append query into the database, and if so how.

Thankyou.