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