Simple Query Question

[mchris]

Simple Query Question (I'm admittedly a newbie)

I have been looking for an answer to what I think is a simple question, but since I'm knew to this I don't think I've found an answer.

I have a table (tblZipCode) that I imported that contains some 74,000 entries. This table has five fields.

lngZipID (primary key)
chrZipCode
chrCity
chrState
chrAreaCode

My use for this table is as follows. When a user enters a zip code into the zip field on my ford, I want to autopopulate the state, city, and are code fields on my form.

Currently the table has some duplicate values in the zip code field and the city field. Example of two different situations.

lngzipID.......chrZipCode.......ChrCity.....................State.............AreaCode
...1................11111..........Bloomfield...................MI...................517
....2...............11111.........East Bloomfield.............MI....................343
....3...............11111..........North Bloomfield...........MI.....................517

I've having a problem with my dlookup statement to populate these fields on my form:

chrCity
chrState
chrAreaCode

I'm trying to accomplish two things.

1) Query my tblZipCodes to make sure all duplicate entries are necessary and not typos.

2) Determine how to make these values autopopulate. Is there a way to have a message box that would allow the user to choose the appropriate city or area code depending on the situation?

Thanks for any help...I'm learning quickly, but not quickly enough.....

Matt

 

[WayneRyan]

Matt,

I'd use a multi-column combo-box or list-box for the users to select from.

To get multi-fields with a DLookUp function call you will need to issue it
once for each field.

Wayne

 

[mchris]

Hmmmm

I was originally going to try that, but had trouble with the cascading combo boxes.

Upon entering the zip code, the combo box for the city would need to change. I couldn't get this to work either.

Any suggestions or resources?

Matt

 

[WayneRyan]

Matt,

On your main form (for whatever table), you can make a 4-column combo box:

Select lngZipID, chrZipCode, chrState, chrCity
From   tblZipCode
Order By chrState, chrCity

The bound column is 0 (lngZipID).

The display widths can be 0";1";1";1"

Base your form on a query that joins your main table with tblZipCode. Use that
table for forms/reports.

That's it.

btw, 74,000 is a lot of records. You could use Cascading combos to limit
a first combo to one of the Distinct states. In its AfterUpdate event, you
could select which city from the second combo. But, I'd go for functional
first.

Wayne

 

[mchris]

Maybe this is because I'm completely new to this, but I'm not entirely sure what you mean.

I understand the idea of the combo box with four fields. First you choose zip code and then it limits the choices in state and city respectively.

I don't understand the bit about basing my form on a query that joins my main table w/ zip code.

If it helps....table structure, at least what's pertinent her is listed below.

tblCallInfo
lngCallID.........primary key
agentID........agent who took call
lngContactID.....foreign key


tblContactInfo
lngContactID.......primary key
chrFirstName
chrLastName
chrAddr1
chrAddr2
chrCity
chrState
chrZipCode
chrPhoneNumber

tblZipCode
lngZipID
chrZipCode
chrState
chrCity
chrAreaCode

The from is has multiple bound controls. All the contact is entered on the form and bound to the contact table.

Does this help?

 

[raskew]

I've attached a stripped-down (20 zips per state) application
you might want to play with. It may provide some ideas on
using multiple combo boxes.

HTH - Bob