How to autofill a zipcode from a town name??

[Darkat]

Hello

I have a table with contact names and addresses - Tel no ect, i want to set it up so that when i enter a town it automatically looks up from another table what zip code it belongs to and updates the record to include the zip code and even the state ?

Is this possible ? if so how do i do it please.

Thankyou so much in advance for any help

Darkat

 

[Rich]

Search here for cascading combo boxes, many examples have been posted

 

[ThreeCrow]

Do you currently have a table in your dataBase that has the ZipCodes, CityName, and StateName.

You'll have to think this out as how you want to do this. This will have to be done using a Form.

If you Enter the City and want ZipCode and State, you'll need to know what State to get the ZipCode. As like here in the USA there are sometime many Cities with the same name but in a differant State and a differant ZipCode, and with in the same City and State there may be a number of ZipCodes.

If you Enter a ZipCode you can in the AfterUpdate Property of the textBox do a "Dlookup" to populate the City and State. This is the easiest method.

If you Enter just the City, you can Create another Form with a ListBox on it to select your ZipCode and State. As there may be many.

Hope this helps, post back if you need more help

 

[ScottGem]

Hello

I have a table with contact names and addresses - Tel no ect, i want to set it up so that when i enter a town it automatically looks up from another table what zip code it belongs to and updates the record to include the zip code and even the state ?

Is this possible ? if so how do i do it please.

Thankyou so much in advance for any help

Darkat

The problem here is you are going in the wrong direction. Its possible for a town to have multiple Zip Codes so you can't just automatiucally assign the zip code. Also, what you are proposing violates normalization rules.

If you want to select a ZipCode based on a Town entry, you would need to do us cascading combos to filter the list of towns. You could put in code that detects if there is only one entry and sets the value accordingly

But the better way is to select the ZipCode from a list and store the ZIPCODEID as the foreign key in your table.

 

[ck01]

I have a solution for you. I'll post an example tomorrow. The way you should do this is have the user type in a zip code. If the zip code is in your list, have it grab the city and state information to fill in the form. If the zip isn't in the list, you want a message box asking the user if they want to add the new information. Is that right? If your answer is yes, I can upload a demo tomorrow. There might already be something in the sample code section too.

 

[ck01]

Well, I was going to upload an example, but this should be just as good and you should be able to follow the code with no problem. Please note that I use a function and two subs. One sub is for the afterupdate event and the other is for the beforeupdate event. You will still need a table containing your zips, city, state, country etc.

This code works. Just plug and chug.

Pete Detlef

Public Function IsNothing(ByVal varValueToTest) As Integer
'-----------------------------------------------------------
' Does a "nothing" test based on data type.
' Null = nothing
' Empty = nothing
' Number = 0 is nothing
' String = "" is nothing
' Date/Time is never nothing
' Inputs: A value to test for logical "nothing"
' Outputs: True = value passed is a logical "nothing", False = it ain't
' Created By: JLV 01/31/95
' Last Revised: JLV 01/31/95
'-----------------------------------------------------------
Dim intSuccess As Integer

On Error GoTo IsNothing_Err
IsNothing = True

Select Case VarType(varValueToTest)
Case 0 ' Empty
GoTo IsNothing_Exit
Case 1 ' Null
GoTo IsNothing_Exit
Case 2, 3, 4, 5, 6 ' Integer, Long, Single, Double, Currency
If varValueToTest <> 0 Then IsNothing = False
Case 7 ' Date / Time
IsNothing = False
Case 8 ' String
If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
End Select


IsNothing_Exit:
On Error GoTo 0
Exit Function

IsNothing_Err:
IsNothing = True
Resume IsNothing_Exit

End Function















Private Sub PostalCode_AfterUpdate()
Dim strPostal As String
If Not IsNothing(Me.PostalCode) Then
' If the length is > 5, then have to do DLookups
If Len(Me.PostalCode) > 5 Then
' Grab the first 5 characters
strPostal = Left(Me.PostalCode, 5)
' Lookup the three values based on what they entered
Me.City = DLookup("City", "tlkpZips", "ZipCode = '" & strPostal & "'")
Me.StateOrProvince = DLookup("State", "tlkpZips", "ZipCode = '" & strPostal & "'")
Else
' Assume we have a match - grab the values from the other zip columns
Me.City = Me.PostalCode.Column(1)
Me.StateOrProvince = Me.PostalCode.Column(2)
End If
End If
End Sub












Private Sub PostalCode_BeforeUpdate(Cancel As Integer)
Dim strPostal As String
' Input mask forces the user to enter 5 digits, but
' limit to list is No because the RowSource contains only 5-digit zips
' This code checks to see that the first 5 they entered are actually
' in the table and warns them if not.
' Do nothing if the PostalCode is empty
If Not IsNothing(Me.PostalCode) Then
strPostal = Left(Me.PostalCode, 5)
If IsNothing(DLookup("ZipCode", "tlkpZips", "ZipCode = '" & strPostal & "'")) Then
' Warn that what they entered isn't in the table
If vbYes = MsgBox("You entered a 5-digit code that isn't in our master " & _
"ZipCode table. Are you sure you want to use this?", _
vbQuestion + vbYesNo, gstrAppTitle) Then
Exit Sub
Else
' Changed their mind - Cancel the update
Cancel = True
End If
End If
End If
End Sub

 

[ScottGem]

Nice Code CK, but it would be easier to have the Zipcode selected from a combo and use the NotInList event to add non-listed codes. This elimiates 2 of your three modules and simplifies the code.

Also, you don't mention that City and State/Province should be UNBOUND controls.

 

[ck01]

it works and it does what he wants

 

[ck01]

I'm not sure of Darkat's goal, but on my form both the city and state/province are BOUND controls. If his form is for data entry, he would definately want theset bebound controls.

 

[ScottGem]

it works and it does what he wants

My suggestion would also work and be simpler to implement.

I'm not sure of Darkat's goal, but on my form both the city and state/province are BOUND controls. If his form is for data entry, he would definately want the set be bound controls.

In your example, it appears you are entering values in one table that already exist in another table (tlkpZips). If that is the case, then you are violating normalization rules by having redundant data. Since the data already exists in tlkpZips, then all you need is use the PK of that record as a foreign key. Storing the corresponding city and state name in another table is redundant data and denormalizes the database. That's why those controls NEED to be unbound.

 

[ck01]

OK, I see what you are saying. Still, for this example I am using bound keys.

 

[ScottGem]

If you see what I'm saying then you should know that using bound controls violates normalization and therefore is not advice we should be giving to OPs. Its one thing if we knowingly violate those rules in our own usage for whatever reason. But when we offer advice to others, that advice should adhere to best practices.