ZipCode: Address Field Completion (1 Viewer)

[Randomblink]

Ok.
I have a table with three fields...
[fld_City]
[fld_State]
[fld_ZipCode]

I have a combo-box on my Entry Form IN the ZipCode field.
The User types in a ZipCode.

At this point...
I want the City and State fields to auto-fill...

I tried a couple functions similar to:

Public Function getCITY(ZipCode As String) As String

Using a DLookip and assigning the [fld_City] to the getCITY string.
I used another one for getSTATE.
Then I just set the textboxes equal to getCITY and so on whenever the ZipCode Textbox.LostFocus...

Ok...
That didn't work, cause when the user enters a ZipCode NOT IN THE LIST I get weird errors... Apparently, DLookup, which is SUPPOSED to return Null, INSTEAD fires an error message... ugh!

So, can someone help me?
I created a ZipCode data entry form with the thought of something like this:

The User hits the ZipCode field (in any of my forms).
They type in their ZipCode and tab OUT of the ZipCode Textbox.
A Function gets called.
This Function checks the ZipCode table.
Is the ZipCode entered already in the ZipCode table?
If NO, then open the ZipCode form and allow the to add the ZipCode, City, and State into the Table so it WILL BE THERE now.

I keep hitting errors, could be cause I am having problems thinking right now... But I just am stuck... hoping someone else had this problem and they have fixed it by now...

 

[Pat Hartman]

Create a query that joins the address table to the zip code table. Select the columns you need from both tables. When the zip code is filled in, the related data from the zip table will auto-populate. Change the recordsource of your form to the new query. No coding is required

 

[Randomblink]

???

Hmmmm...

Ok...
Trying to put your plan in motion...
Having problems...

I am seeing it tho...
Thanks Pat...
You da greatest...

 

[HJAMES]

This is the code I'm using to accomplish basically the same thing:

Private Sub ZIP_Exit(Cancel As Integer)
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblZipCityCounty")
rst.MoveLast
rst.MoveFirst

Do Until rst.EOF

If Me.ZIP = rst!ZIP Then
Me.CITY = rst![CITY]

End If

rst.MoveNext

Loop

rst.Close
End Sub

My table is called tblZipCityCounty - I check to see if the zip code exists in the table and it it does, I populate the City field.

Hope this helps.

Helen

 

[Pat Hartman]

HJAMES,
A zip code table isn't that large as far as files go but it is still thousands of records and your code reads it 1.5 times on average. MoveLast causes each row to be "passed", then you return to the beginning and start looking for what you really want. At a minimum, if you insist on doing this the hard way with code, you should change your code so that it uses a query with a zipcode parameter. At least that would return only the requested row to your code.

Believe me, my way is the easy way and is the way intended by the conceivers of relational database systems. The first part of designing a database is normalizing the tables. The second part is learning how to put them back together again with queries. Why write code to do something a query is ment to do much more efficiently.

To prove to yourself how painless this is, create a query to see how it works.

Select tblA.YourRecordKey, tblA.Zip, tblB.City, tblB.State
From tblA Inner Join tblB on tblA.Zip = tblB.Zip;

Obviously you'll need to change the names. Run the query on test data. Overtype the zip code in the first record with a different code. When you tab out of the zip field, the city and state will populate with the new values. Press the escape key to cancel the update.