combo box help

G

Guest

I have a customer form with a drop down combo box for "city". It displays
city, state and zipcode. How can I select the city and have the state and
zip fields fill in at the same time?
Thanks for any help
 
J

John Vinson

I have a customer form with a drop down combo box for "city". It displays
city, state and zipcode. How can I select the city and have the state and
zip fields fill in at the same time?
Thanks for any help
Click to expand...

Use the combo box's AfterUpdate event:

Private Sub cboCity_AfterUpdate()
Me!txtState = cboCity.Column(1)
Me!txtZip = cboCity.Column(2)
End Sub

A couple of things to note: the Column property is zero based, so this
assumes that the city name is in the first column, the state the
second and the zip the third. More of concern, most substantial cities
have more than one zipcode; the Combo Box will jump to the first
zipcode in that city.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
F

fredg

I have a customer form with a drop down combo box for "city". It displays
city, state and zipcode. How can I select the city and have the state and
zip fields fill in at the same time?
Thanks for any help
Click to expand...

Here is one way.

Include the City, State, and Zipcode in the Combo Rowsource.
Something like:

Select TableName.City, TableName.State, TableName.Zipcode From
TableName Order By TableName.City;

Make the Bound Column column 1 (the City).
Column Count 3
Column Widths 1";0.5";1:
Then code the Combo's AfterUpdate event:
[StateControl] = Me!ComboName.Column(1)
[ZipCodeControl] = Me!ComboName.Column(2)
 
G

Guest

is there a way to have a to select the correct zip if there are multiable zip
codes? thanks for your help
 
R

Randy

Mr said:
is there a way to have a to select the correct zip if there are multiable
zip
codes? thanks for your help
Click to expand...

As you are implying there are US cities with multiple zip codes. There is no
way for you to auto-pick a zip code based solely on the City and State in
the case that the city has multiple zip codes. However if you have the
Street Address and the Street Database from USPS (postal service) you can
find out the correct zip code assigned to that area of that city. However
the USPS database uses regular updates, requires subscription and cannot
guarantee 100% accuracy/coverage.

However that is not the right approach. The recommended (and perhaps the way
must used commercially) is to load the City and the State automatically
based on a given Zip Code. That is why every time you call a big company or
corporation they first ask you for your Zip Code, because with that, the
City and State will come up. So the agent on the phone just has to confirm
with the customer the information that came up, and then just ask for the
street address to complete the address. This creates correct addresses all
the time.

Of course, there will be customers who won't know or remember their zip
code. That's why you should create your system flexible and have a form with
comboboxes for City, State, and Zip that lookup and synchronize themselves
whenever any of them changes. Also these comboboxes should not force
selection and allow the user for manual input as well.

-Randy
 
J

John Vinson

is there a way to have a to select the correct zip if there are multiable zip
codes? thanks for your help
Click to expand...

As Randy suggests, it's probably easier to select the Zip first.
Here's some code which I use in my applications - it coordinates the
City, State and Zip combo boxes so that when you select any one of
them, the remaining ones are either set to a single value or limited
to the list of valid values. This code would all be connected to the
AfterUpdate events of the respective combos. My table CONtblZip
contains fields Zip, City and State.

Private Sub cboCity_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all zipcodes
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip ORDER BY Zip;"
' If a city is selected, limit the Zip to those in the selected
' city; if the city has only one zip, just set it to that value
If Not IsNull(cboCity) Then
Set db = CurrentDb
strSQL = "SELECT Zip, State FROM CONtblZip WHERE " & _
"ContblZip.City=" & Chr(34) & cboCity & Chr(34) & _
IIf(IsNull(Me.cboState), " ", " AND CONtblZip.State = '" _
& Me.cboState & "') ORDER BY City;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
rs.MoveLast
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing if this city isn't in the ZIP table
Case 1
' If there's just one city/zip, set zip and state
' to the selected one
Me.cboZip = rs!Zip
Me.cboState = rs!State
Case Else
' If more than one zipcode, limit the combo
' to the selected city's zipcodes
Me.cboZip.RowSource = strSQL
End Select
rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboCity_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub cboState_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


If IsNull(cboState) Then
' State has been cleared, set Zip and City combos to all
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip " _
& "ORDER BY Zip;"
Else
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip WHERE CONtblZip.State = '" & cboState & "' " _
& "ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip " _
& "WHERE CONtblZip.State = '" & cboState & "' ORDER BY Zip;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboState_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub cboZip_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all cities
Me.cboCity.RowSource = "SELECT DISTINCT City, State FROM " _
& "CONtblZip ORDER BY City;"
If Not IsNull(cboZip) Then
Set db = CurrentDb
strSQL = "SELECT DISTINCT City, State FROM CONtblZip " _
& "WHERE ContblZip.Zip='" & cboZip & "' ORDER BY City;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing
Case 1
Me.cboCity = rs!City
Me.cboState = rs!State
Case Else
Me.cboCity.RowSource = strSQL
Me.cboState = rs!State
End Select

rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboZip_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub Form_Current()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR

If IsNull(Me.cboState) Then
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip " _
& "FROM CONtblZip ORDER BY Zip;"
Else
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip WHERE [State]='" & cboState & "' ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip " _
& "WHERE [State]='" & cboState & "' ORDER BY Zip;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in Form_Current:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub



John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
G

Guest

Hi John,

I am doing a similar thing with Australian city's, states and postcodes.

I have set up my tables and combo boxes as you described and copied your
code, however i recieve the following error message,
Compile error: Exopected: list separator or )

and the following code is highlighted red,

strSQL = "SELECT Zip, State FROM CONtblZip WHERE " & _
"ContblZip.City=" & Chr(34) & cboCity & Chr(34) & _
IIf(IsNull(Me.cboState), " ", " AND CONtblZip.State = '" _
& Me.cboState & "') ORDER BY City;"

could you please help
regards Dave

John Vinson said:
is there a way to have a to select the correct zip if there are multiable zip
codes? thanks for your help
Click to expand...

As Randy suggests, it's probably easier to select the Zip first.
Here's some code which I use in my applications - it coordinates the
City, State and Zip combo boxes so that when you select any one of
them, the remaining ones are either set to a single value or limited
to the list of valid values. This code would all be connected to the
AfterUpdate events of the respective combos. My table CONtblZip
contains fields Zip, City and State.

Private Sub cboCity_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all zipcodes
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip ORDER BY Zip;"
' If a city is selected, limit the Zip to those in the selected
' city; if the city has only one zip, just set it to that value
If Not IsNull(cboCity) Then
Set db = CurrentDb
strSQL = "SELECT Zip, State FROM CONtblZip WHERE " & _
"ContblZip.City=" & Chr(34) & cboCity & Chr(34) & _
IIf(IsNull(Me.cboState), " ", " AND CONtblZip.State = '" _
& Me.cboState & "') ORDER BY City;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
rs.MoveLast
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing if this city isn't in the ZIP table
Case 1
' If there's just one city/zip, set zip and state
' to the selected one
Me.cboZip = rs!Zip
Me.cboState = rs!State
Case Else
' If more than one zipcode, limit the combo
' to the selected city's zipcodes
Me.cboZip.RowSource = strSQL
End Select
rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboCity_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub cboState_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


If IsNull(cboState) Then
' State has been cleared, set Zip and City combos to all
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip " _
& "ORDER BY Zip;"
Else
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip WHERE CONtblZip.State = '" & cboState & "' " _
& "ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip " _
& "WHERE CONtblZip.State = '" & cboState & "' ORDER BY Zip;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboState_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub cboZip_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all cities
Me.cboCity.RowSource = "SELECT DISTINCT City, State FROM " _
& "CONtblZip ORDER BY City;"
If Not IsNull(cboZip) Then
Set db = CurrentDb
strSQL = "SELECT DISTINCT City, State FROM CONtblZip " _
& "WHERE ContblZip.Zip='" & cboZip & "' ORDER BY City;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing
Case 1
Me.cboCity = rs!City
Me.cboState = rs!State
Case Else
Me.cboCity.RowSource = strSQL
Me.cboState = rs!State
End Select

rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboZip_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub Form_Current()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR

If IsNull(Me.cboState) Then
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip " _
& "FROM CONtblZip ORDER BY Zip;"
Else
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip WHERE [State]='" & cboState & "' ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip " _
& "WHERE [State]='" & cboState & "' ORDER BY Zip;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in Form_Current:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub



John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
S

strive4peace

Hi Dave,

you have:

IIf(IsNull(Me.cboState), " ", " AND CONtblZip.State = '" _
& Me.cboState & "') ORDER BY City;"

since the IIF statement is outside the quotes, the closing parenthesis
needs to also be outside the quotes

IIf(IsNull(Me.cboState), " ", " AND CONtblZip.State = '" _
& Me.cboState & "'") & " ORDER BY City;"


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi John,

I am doing a similar thing with Australian city's, states and postcodes.

I have set up my tables and combo boxes as you described and copied your
code, however i recieve the following error message,
Compile error: Exopected: list separator or )

and the following code is highlighted red,

strSQL = "SELECT Zip, State FROM CONtblZip WHERE " & _
"ContblZip.City=" & Chr(34) & cboCity & Chr(34) & _
IIf(IsNull(Me.cboState), " ", " AND CONtblZip.State = '" _
& Me.cboState & "') ORDER BY City;"

could you please help
regards Dave

John Vinson said:
is there a way to have a to select the correct zip if there are multiable zip
codes? thanks for your help
Click to expand...
As Randy suggests, it's probably easier to select the Zip first.
Here's some code which I use in my applications - it coordinates the
City, State and Zip combo boxes so that when you select any one of
them, the remaining ones are either set to a single value or limited
to the list of valid values. This code would all be connected to the
AfterUpdate events of the respective combos. My table CONtblZip
contains fields Zip, City and State.

Private Sub cboCity_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all zipcodes
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip ORDER BY Zip;"
' If a city is selected, limit the Zip to those in the selected
' city; if the city has only one zip, just set it to that value
If Not IsNull(cboCity) Then
Set db = CurrentDb
strSQL = "SELECT Zip, State FROM CONtblZip WHERE " & _
"ContblZip.City=" & Chr(34) & cboCity & Chr(34) & _
IIf(IsNull(Me.cboState), " ", " AND CONtblZip.State = '" _
& Me.cboState & "') ORDER BY City;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
rs.MoveLast
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing if this city isn't in the ZIP table
Case 1
' If there's just one city/zip, set zip and state
' to the selected one
Me.cboZip = rs!Zip
Me.cboState = rs!State
Case Else
' If more than one zipcode, limit the combo
' to the selected city's zipcodes
Me.cboZip.RowSource = strSQL
End Select
rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboCity_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub cboState_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


If IsNull(cboState) Then
' State has been cleared, set Zip and City combos to all
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip " _
& "ORDER BY Zip;"
Else
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip WHERE CONtblZip.State = '" & cboState & "' " _
& "ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip " _
& "WHERE CONtblZip.State = '" & cboState & "' ORDER BY Zip;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboState_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub cboZip_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all cities
Me.cboCity.RowSource = "SELECT DISTINCT City, State FROM " _
& "CONtblZip ORDER BY City;"
If Not IsNull(cboZip) Then
Set db = CurrentDb
strSQL = "SELECT DISTINCT City, State FROM CONtblZip " _
& "WHERE ContblZip.Zip='" & cboZip & "' ORDER BY City;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing
Case 1
Me.cboCity = rs!City
Me.cboState = rs!State
Case Else
Me.cboCity.RowSource = strSQL
Me.cboState = rs!State
End Select

rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboZip_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub Form_Current()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR

If IsNull(Me.cboState) Then
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip " _
& "FROM CONtblZip ORDER BY Zip;"
Else
Me.cboCity.RowSource = "SELECT DISTINCT City, State " _
& "FROM CONtblZip WHERE [State]='" & cboState & "' ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip " _
& "WHERE [State]='" & cboState & "' ORDER BY Zip;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in Form_Current:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub



John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combo box help 3
CHECK BOX, BILLING ADDRESS = SHIPPING ADDRESS 5
Help with update query and unbound combobox 4
code help needed 1
Code problems 1
Combo box help 2
Drop down boxes 1
Combo box for related records AND non-related records 3

Top