question regarding automatic population of fields

[Lauren B.]

I have a large spreadsheet that contains every zip code in the United
States--three columns follow with the corresponding city, state, and county.

I want to set up my form so that when the user enters a 5-digit number in
the zip code field, the city, state and county boxes automatically populate
based on the spreadsheet information.

How should I write my code?

Thank you for any assistance.

 

[Douglas J. Steele]

The normal approach is to build a table that contains all of the
information, and then write a query that returns it in whatever order you
feel is appropriate. Use that query as the row source for a combo box. If
you want to keep the data in the spreadsheet, you can create a linked table
(through File | Get External Data | Link) that points to the spreadsheet.

In the combo box's AfterUpdate event, put logic to populate the other text
boxes. If the query that's associated with the combo box has zip code, city,
county and state in that order, and the combo box is named cboZipCode, you'd
refer to the city associated with the currently selected zip code as
Me.cboZipCode.Column(1), the county as Me.cboZipCode.Column(2) and the state
as Me.cboZipCode.Column(3) (in other words, the columns start at 0)


How do you intend to handle the case where a particular zip code applies to
more than one community?

 

[Lauren B.]

I wrote my code as follows:

After Update:
CITY = Me.CBOZIPCODE.Column(1)
COUNTY = Me.CBOZIPCODE.Column(2)
STATE = Me.CBOZIPCODE.Column(3)

This worked to populate the City text box- however, the county and state
will not populate. Did I write something incorrectly? My text boxes for
city, county, and state are not one right after the other- does that matter?

The query I created contains the fields Zip Code, City, County, State and is
the row source for CBOZIPCODE

Thank you for your help.

*Lauren

 

[Douglas J. Steele]

Are County and State the names of text boxes on your form?