find zip code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

..I have a drop down combo box for zip code that also updates the city and
state when you select it as follows; (SELECT tblZipCode.ZIPCODE,
tblZipCode.CITY, tblZipCode.State FROM tblZipCode ORDER BY tblZipCode.CITY;
)......................also private Sub Zip_AfterUpdate()
Me!State = Zip.Column(2)
End Sub
The problem is if one city has multple zip codes I can only select the
first find. I tried to do the same thing with city instead of zipcode but
there are some towns that share the same zipcode Can anyone help please?
Ive been working on this for a long time. Thanks, KPE
 
I have done this before. On a form selecting the city and state and
have the zip code filled. Now if more than one zip code is found for
the city then having a form open with a list box of all zip codes for
the city. I use this code in a module:

Function ZipCodeFinder(cn As String, sn As String)
On Error Resume Next

Dim db As Database, Rs As Recordset, Total As Long

Set db = CurrentDb
Set Rs = db.OpenRecordset("SELECT [City], [State], [ZipCode] FROM
tblZipCodes WHERE ((([City])='" & cn & "') AND (([State])='" & sn &
"'));")

Rs.MoveLast
Total = Rs.RecordCount

If Total > 1 Then
DoCmd.OpenForm "frmCitySelector"
Forms!frmCitySelector!CitySelect.RowSource = "SELECT
DISTINCTROW City, State, ZipCode FROM tblZipCodes WHERE (((City)='" &
cn & "') AND (([State])='" & sn & "')) ORDER BY ZipCode;"
Forms!frmCitySelector.Tag = 1
ElseIf Total = 1 Then
Forms!frmMain!City = cn
Forms!frmMain!State = sn
Forms!frmMain!ZipCode = Rs!ZipCode
ElseIf Total < 1 Then
Resume Next
End If

Rs.Close
Set Rs = Nothing

End Function

Put this in the AfterUpdate of your city combo box:

=ZipCodeFinder([City],[City].[Column](1))

My combo box shows the city and state. Once I select a city and more
than one zip cod is found then the form "frmCitySelector" opens and a
list box show all the zip codes. The line Forms!frmCitySelector.Tag =
1 is used for calling this code from more than one form. If you only
have one form then you can delete this line.

Hope this helps.
 
Back
Top