Add Zip Code From City

[Arvin Meyer [MVP]]

First build a query like:

SELECT tblMyData.City, First(tblMyData.Zip) AS FirstOfZip
FROM tblMyData
GROUP BY tblMyData.City;

Then use that query as the rowsource for a combobox.

In the AfterUpdate event of the combobox add the following code (air code):

Sub MyCombo_AfterUpdate()
Me.txtZip = Me.MyCombo.Column(1)
End Sub

Column 1 refers to the second column in the combo box.

 

[Guest]

In a database, I have a drop-down list of cities in the City Field. All but
a couple of these cities have only one Zip Code.

I would like for the city's Zip Code to automatically be entered in the Zip
Code Field when I select the city from the Drop-Down list in the City Field.

Hope this question makes sense.

Thanks,
Musette

 

[John W. Vinson]

In a database, I have a drop-down list of cities in the City Field. All but
a couple of these cities have only one Zip Code.

I would like for the city's Zip Code to automatically be entered in the Zip
Code Field when I select the city from the Drop-Down list in the City Field.

Hope this question makes sense.

How about a step better: have the City combo box set the zipcode if it's
unique, and change the Zip code combo's row source to a list of zips for that
city if it's not.

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)
If rs.RecordCount > 0 Then
rs.MoveLast
iCount = rs.RecordCount
Select Case iCount
Case 1
' If there's just one city/zip, set zip and state to
selected one
Me.cboZip = rs!Zip
Me.cboState = rs!State
Case Else
' If more than one zipcode, limit the combo to the
selected one
Me.cboZip.RowSource = strSQL
End Select
' set the RowSource of the Streets combo to this city's streets
strSQL = "SELECT Distinct Street FROM Streets" _
& " WHERE City = " & Chr(34) & Me.cboCity & Chr(34) _
& " AND State = '" & Me.cboState & "' ORDER BY Street;"
Me.cboStreet.RowSource = strSQL

' save the record to disk
Me.Dirty = False
End If
End If

Proc_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing

Exit Sub

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


End Sub

John W. Vinson [MVP]

 

[Guest]

Arvin,

Thank you for repsponding so quickly to my inquiry. Unfortunately, I do not
speak "Access 2003" so I need step-by-step instructions.

For instance, what do I click on to "build a query"?
Can you substitute my field/column names which are "City" and "Zip Code"?

?? "Then use that query as the rowsource for a combobox."
Rowsource?
Is a "combobox" the same as a drop-down list?

?? "In the AfterUpdate event of the combobox add the following code (air
code):"
What is an "AfterUpdate event"?
What is an "air code"?

?? "Sub MyCombo_AfterUpdate()

Me.txtZip = Me.MyCombo.Column(1)
End Sub"

Where do I enter this? Is is all on one line or on 3 lines like
you have?

?? "Column 1 refers to the second column in the combo box."
How did I get a "second column" in the "combo box"?

I don't know if you want to go to all the trouble to try to "teach" me how
to do this, but I would really appreciate it.

Thanks,
Marsha

 

[Guest]

Hi, John

Thank you so much for you quick response to my inquiry! I am sure it took
you some time to do all of that.

I like your idea of having the City Drop-down box set the zipcode. That's
exactly what I am wanting to do.

However, you are talking to a real novice here. I know how to create a basic
Table, Database, and Form. That's it!

Can you walk me through it step by step even more than you have?

1. Where do I start? With "Dim rs As DAO.Recordset"?

2. Where do I go to enter this information?

3. Do I type it in exactly as you have it, line for line?

4. What am I actually doing?

The more you can take me through it step-by-step, the more likely I am to be
successful.

I realize this will be even more work for you, but I follow the gist of it.
If you can break it down to kindergarten language, I think I do it.

Thanks.

Musette

 

[John W. Vinson]

Hi, John

Thank you so much for you quick response to my inquiry! I am sure it took
you some time to do all of that.

Well, it did back in 2002 when I first did it.... just copy & paste from an
open database today.

I like your idea of having the City Drop-down box set the zipcode. That's
exactly what I am wanting to do.

You can actually have it go both ways - fill in the city from the zipcode as
well.

However, you are talking to a real novice here. I know how to create a basic
Table, Database, and Form. That's it!

Can you walk me through it step by step even more than you have?

1. Where do I start? With "Dim rs As DAO.Recordset"?

2. Where do I go to enter this information?

3. Do I type it in exactly as you have it, line for line?

4. What am I actually doing?

You're creating a Subroutine which will be called every time a selection is
made in the City combo box. The subroutine will look up the zipcode (or codes)
for that city and take appropriate action.

Open the Form that you'll be using in design. I assume that you have Combo
Boxes on the form for the zipcode and city - if not create them. For
simplicity of editing the code, you may want to change the name of the zipcode
combo box to cboZip and that of the city to cboCity; otherwise you'll need to
edit the code to replace cboZip and cboCity with the actual name of your combo
boxes. My code also assumes that you have a combo box named cboState with the
two-letter state code.

Select the zip code combo box (cboZip let's say) and view its Properties. On
the Events tab, find the AfterUpdate event. Click the ... icon next to it, and
select "Code Builder". Access will give you two lines - Private Sub
cboZip_AfterUpdate() and End Sub. Copy and paste (you don't need to retype)
the code from my message onto these, replacing them.

You'll need to fix a few lines that word-wrapped in the message here (I don't
know what newsreader you're using so I'm not sure whether that will be an
issue).

My code uses a table named CONtblZIp with fields Zip and City. You presumably
have a similar table in your database - edit the code to replace CONtblZip
with the name of this table, and the fieldnames Zip and City with the actual
fieldnames from your table.

When you're done editing these changes, select Debug... Compile <my database>
from the menu. Grumble at me about the (likely) error messages; correct them
all, recompiling each time.

Click the diskette icon on the toolbar to save the project.

Open your form and try out the combo - and let me know how it works!

John W. Vinson [MVP]

 

[noname101 via AccessMonster.com]

Arvin,

Thank you for repsponding so quickly to my inquiry. Unfortunately, I do not
speak "Access 2003" so I need step-by-step instructions.

For instance, what do I click on to "build a query"?
Can you substitute my field/column names which are "City" and "Zip Code"?

?? "Then use that query as the rowsource for a combobox."
Rowsource?
Is a "combobox" the same as a drop-down list?

?? "In the AfterUpdate event of the combobox add the following code (air
code):"
What is an "AfterUpdate event"?
What is an "air code"?

?? "Sub MyCombo_AfterUpdate()

Me.txtZip = Me.MyCombo.Column(1)
End Sub"

Where do I enter this? Is is all on one line or on 3 lines like
you have?

?? "Column 1 refers to the second column in the combo box."
How did I get a "second column" in the "combo box"?

I don't know if you want to go to all the trouble to try to "teach" me how
to do this, but I would really appreciate it.

Thanks,
Marsha

First build a query like:

[quoted text clipped - 24 lines]

Access makes it fairly easy to create forms but as far as doing anything that
resembles a sophisticated application you have to write VBA code that
actually does things in the background. "After Update" is the event that
gets raised *after* you *update* the combo box that has the state, meaning as
soon as you make a choice that code you put in the event is going to do
whatever you tell it to do. You need to have a basic understanding of
programming before you try to develop an application otherwise if you have
questions about something this simple its going to take a month to do what a
person can do in a day. You should probably go out and buy a book that you
can use to get you started. If you're serious about learning this then you
will save yourself much confusion, headache, and time by getting a better
understanding of Access first because you don't even need to know anything
about Access to figure out how to create a new query, theres a button that
says "New".

 

[Arvin Meyer [MVP]]

Arvin,

Thank you for repsponding so quickly to my inquiry. Unfortunately, I do
not
speak "Access 2003" so I need step-by-step instructions.

For instance, what do I click on to "build a query"?
Can you substitute my field/column names which are "City" and "Zip Code"?

?? "Then use that query as the rowsource for a combobox."
Rowsource?
Is a "combobox" the same as a drop-down list?
Yes

?? "In the AfterUpdate event of the combobox add the following code (air
code):"
What is an "AfterUpdate event"?
What is an "air code"?

In the form's design view, open the property sheet, click on events, then
find and click in the After Update event. Use the ellipses (...) button at
the end of the line to open a code window.

Air code is code typed in the air, i.e. right in this posting. In other
words, it is not tested so there could be slight mistakes or misspellings.

?? "Sub MyCombo_AfterUpdate()
Where do I enter this? Is is all on one line or on 3 lines like
you have?

See the above answer. Type into a code window. Then in the Debug menu,
compile the database. Any errors will be highlighted.

?? "Column 1 refers to the second column in the combo box."
How did I get a "second column" in the "combo box"?

On the Format tab of the property sheet, change the Column Count to 2. Then
enter the column width of each, as in (1";0") which will display column 1,
and hide column 2.

I don't know if you want to go to all the trouble to try to "teach" me how
to do this, but I would really appreciate it.

That's what we MVPs do here. We enjoy it.