Function CheckZipMain() As Byte
Dim dbs As DAO.Database
Dim rstZip As DAO.Recordset
Dim stCurrent As String
Dim stCorrect As String
Dim stCurrentZip As String
Dim stCurrentState As String
Dim stCurrentCity As String
Dim stMsg As String
Dim stCorrectCity As String
Dim stCorrectState As String
Dim stZipFound As String
Dim varBookmark As Variant
'Gathers the data from the open Form [Employees Main Form]
stCurrentZip = [Forms]![Employees Main Form]![Employee Zip]
stCurrentState = [Forms]![Employees Main Form]![Employee State]
stCurrentCity = [Forms]![Employees Main Form]![Employee City]
stCurrent = stCurrentCity & ", " & stCurrentState & " " & stCurrentZip
Set dbs = CurrentDb
Set rstZip = dbs.OpenRecordset("ZipCodes", dbOpenTable)
'Verifies if the zip code entered on the form is found in the ZipCodes table
stZipFound = Nz(DLookup("[Zip]", "ZipCodes", "[Zip] = '" & stCurrentZip & "'"), False)
If stZipFound = False Then
MsgBox ("Invalid zip. Please correct.")
Else
'Index created on ZipCodes table of Zip, then City, then State
rstZip.Index = "ZCS"
'Checking to see if the City/State/Zip combination is present on the ZipCodes table
rstZip.Seek "=", stCurrentZip, stCurrentCity, stCurrentState
If rstZip.NoMatch Then
Do While rstZip.EOF = False
'Gathers the Zip from [Employees Main Form], to populate the correct City/State
rstZip.Seek "=", stCurrentZip
stCorrectCity = rstZip!City
stCorrectState = rstZip!State
stCorrect = stCorrectCity & ", " & stCorrectState & " " & stCurrentZip
'Asks if this is the City/State/Zip required
stMsg = MsgBox("Invalid city/state/zip combination. Is this the correct combo? " & _
stCorrect, vbYesNoCancel)
'If it is, then it updates the field to match
If stMsg = vbYes Then
[Forms]![Employees Main Form]![Employee City] = stCorrectCity
[Forms]![Employees Main Form]![Employee State] = stCorrectState
Exit Do
ElseIf stMsg = vbCancel Then
Exit Do
End If
rstZip.MoveNext
Loop
Else
End If
End If
rstZip.Close
Set rstZip = Nothing
End Function
