ZipCode AutoComplete

[Guest]

I know this has been asked before, however, I can not locate the answer.

I am in need of the a form with a ZipCode and State field. The State field
needs to read the Table: ZipCode and populate the State based on the ZipCode
entered in the ZipCode field.

ZipCode in Table: ZipCode is Text, 5 char long, indexed. State in Table:
ZipCode is Text, 2 char long.

There used to be Free databases easily found on the Internet when it was
Access 2.0. i just can not find them anymore and was just given a project
that needs this.

Thanks.

 

[BruceM]

One approach is to make a combo box with a row source based on the table
that contains ZipCode and State, and an unbound text box ) named txtState in
my example). When setting up the row source, make ZipCode the first column,
and State the second. One way to do this is to make a query, save it, and
use that named query as the combo box row source. In combo box properties,
set the column widths to 0";1", and set the column count to 2. In the combo
box After update event, something like this:

Me.txtState = Me.cboZipComboBox.Column(1)

You will need the same code in the form's Current event.

Note that columns are counted starting from 0, so Column(1) is actually the
second column.

This is a short answer that does not take into account such things as the
combo box Bound Column, or anything that may have to do with relationships
between tables.

 

[Guest]

Private Sub ZipCode_AfterUpdate()
State = DLookup("[State]","[ZipCode]","[ZipCode] = " & [ZipCode])
End Sub

This assumes two things:

1. That the ZipCode table includes the ZipCode you have entered with a
matching state, so you may want to make the ZipCode field a combo box bound
to the ZipCode field in the ZipCode table.
2. That the ZipCode is numeric. If you are using Zip +4 or Canadian codes,
it will be text, so you will have to use this instead:

State = DLookup("[State]","[ZipCode]","[ZipCode] = '" & [ZipCode] & "'")

 

[Guest]

Thank you. When I added the City to mix, it did not update the update the
City box.
I used Me.State = Me.Zip.Colum(1) and Me.City = Me.Zip.Colum(2).

Any suggestions?