ZipCode: Address Field Completion

[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...

 

[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