Form with Auto Fill City, State/Prov. from existing table

[TomPollock]

I am looking to make a data base to register contestants for a prize contest. We register from 400 to 10,000 contestants over a couple of months and then start with a new contest and new restrations.

I have a table with all the US postal codes called zipcodes (5 didget numbers (59401)but will be adding the Canadian they are seven didgets and look like this (R0E 2A0.) Between the two there are over 810,000

On my registration form after entering the Contestants Names and mailing address I would like to be able to type in the zipcode (postalcode)and have the city and State/Province fields auto fill.


My question is this.

With a table relationship would this work or will I need to use a subform to retieve City, State/Province?

 

[Starman]

Make the Zip Code field a combo box with two hidden columns for city and state/province. Then you make the record source for the city field and the state/province field the appropriate columns from the Zip combo box. Make sure you put an after update even in the Zip field to requery the city and state/province fields.

 

[donbettis]

You should be able to use DLookup...

Add something like this to the BeforeUpdate event in the zipcode field...

City = DLookup("[City]", "zipcodetblname", "[Zip]= '" & Me!Zip & "'")
State = DLookup("[State]", "zipcodetblname", "[Zip]= '" & Me!Zip & "'")

* Adjust the fields to match your needs.

City = field name for the city in your form
[City] = field name for the City in your table
"zipcodetblname" = the name of the zipcode table
[Zip] = field name for the zipcode in your table
Me!Zip = field name for the zipcode in your form

 

[TomPollock]

Thanks I will work with that and let you know how I made out.

 

[TomPollock]

Hi I have tryed both methods and seem to get the closest to what I want with donbettis method useing DLooup.

This is what I entered in the before up date event.

Private Sub ZIP_BeforeUpdate(Cancel As Integer)
City = DLookup("[City]", "ZipCode", "[Zip]= '" & Me!ZIP & "'")
State = DLookup("[State]", "ZipCode", "[Zip]= '" & Me!ZIP & "'")
End Sub

when I fill in the Zip field the zip number remains correct.
The City field fills but with the wrong City.

The State fills but with the wrong State.
I thought I would be able to the attached file but I seem to be doing some thing wrong I'll read more on how to do that and try again.
Thanks

 

[missinglinq]

Is the Zip field defined in the table as a Text field (the syntax you're currently using is correct for a Text field) or as a Numeric field? Zip codes, like any "number" that isn't used for math calculations, should be defined as Text not Numeric.

 

[TomPollock]

I had changed both to a numeric field when it first gave this problem.
I just changed changed Zip in both tables back to Text and tryed again and still get the worng City and State.

I feel I'm so close!!!!!

Thanks Tom

 

[wazz]

so how are your tables set up? i think i would go with post #2 if they are set up to do that.

if your tables are already set up state-city-postal/zip code, or something like that then you can use a combobox (post #2) or even a list box to see them all at the same time. if you can't get a query to work on its own that shows everything you need then there might be something up with the design.

isn't it true that more than one place can share a zip code? a dlookup might not give you exactly what you need (i.e. all possible cites or variations on cities/places). i'm not too familiar with this so just a thought.

 

[TomPollock]

I can see what is happening.

When I fill in the zip field and click enter.

The zip field retains the zipcode I entered but fills in the City and State from the first row of the Zipcode Table.

Yes I do need the zip fields to be in Text format as in the practice table I have only six citys listed to see what is going on.

In the Table that I plan to use there are over 810,00 citys and the Canadian codes look like this. R0E 2A0

Thanks to all that are helping me with this.
Tom

 

[TomPollock]

OK I got it working great!!!!
When I typed in Beforeupdate I miss named the zipcode field Zip it should have been ZIPCODE as that is what it is in the ZipCode table.

Thanks every one for your help to this stage.

Many Thanks Tom

 

[donbettis]

Tom

Good to see you got it working...

I would have answered you yesterday but I have just been covered up...

At least you are working now...

Glad we could help...