Solved

Combo Boxes for Zip Code to autofill in City & State

Posted on 2010-09-05
18
1,243 Views
Last Modified: 2012-05-10
I have reviewed other problems/answers similar to mine and can not make those answers work in my db.  I am creating a DB using Access 2007 for employees to complete an IRS reporting form to collect info to ultimately send to the IRS.  The forms do NOT go directly to the IRS.  It is only for gathering info that the accountant will accumulate into his system to confirm and transmit.

I am simply wanting the Employee to key the Form's Zip code and it autopopulate the Zip Code City and State.  I keep getting a "Syntax error in From clause" error when I use code below in the "Row Source" field in PostalCity

Here is what I have.

I have 4 tables:
1) IRS Form Info;
2) City;
3) State Abbreviations;
4) PostalCode

I have populated the PostalCodes table with all zip codes within my region
I have linked (via Lookup Wizard) the PostalCode table to my State Abbreviations table
I have linked (via Lookup Wizard) the PostalCode table to my City table
I have linked (via Lookup Wizard) the City to my State Abbreviations table (since City names can be duplicated by many states each name will be unique by a CityID)

Table:
PostalCode
Fields:
1) PostalCodeID
2) PostalCode - The unique Zip Code
3) StateID - Lookup field to "State Abbreviations" Table
4) CityCode - Lookup field to "City" Table

Table:
City
Fields;
1) CityID
2) CityName
3) StateID - This is a lookup to the State Abbreviations table

Table:
State Abbreviations
Fields:
1) StateID
2) State - This is the full state name
3) PostalState - This is the 2 letter Postal State Abbreviation

Table:
IRS Form Info
Fields;
1) TransID
2) CustomerLastName
3) CustomerFirstName
4) CustomerAddress
5) CustomerPostalCode
6) CustomerCity
7) CustomerState
8) CustomerAmount
9) TransDate



Thanks in Advance
I tried code similar to the below

RowSource;
SELECT City.CityID, City.CityName FROM City JOIN PostalCode ON PostalCode.CityCode = City.CityID

I get a "From" Error

Open in new window

0
Comment
Question by:wlwebb
  • 10
  • 6
  • 2
18 Comments
 
LVL 4

Expert Comment

by:tabish
ID: 33608708
Hi,

You are missing INNER in join statement.

it should be
SELECT City.CityID, City.CityName FROM City Inner JOIN PostalCode ON PostalCode.CityCode = City.CityID
 
Cheers!
0
 

Author Comment

by:wlwebb
ID: 33608737
Thanks tabish.  Worked instantly to let the list populate all Cities.  However, I thought, probably mistakingly, that when I picked the zip code, say 45999, that City would automatically populate with the name Cincinnati.  What I'm getting is the list of ALL cities.  If I have already defined in my PostalCode table that zip code #45999 relates to CityID 1500 which is Cincinnati am I doing something incorrectly such that it lists all cities.
0
 
LVL 4

Expert Comment

by:tabish
ID: 33608765
Try
SELECT City.CityID, City.CityName FROM City Inner JOIN PostalCode ON PostalCode.CityCode = City.CityID
Where PostalCode.PostalCode = [forms]![Name of the Form Where you have your Combo box]![Name of your combo box that selects the PostalCode]

0
 

Author Comment

by:wlwebb
ID: 33608778
Tried the following code.  Now the combo box list "City" is blank.  Previously it listed every city in the drop down combo box.

The Form name is: "Info Reporting"
The Combo Box on the form is: "InfoPostalCity"


Seems like we're close but no cigar.   And I'm in desperate need of a cigar.
SELECT City.CityID, City.CityName FROM City INNER JOIN PostalCode ON PostalCode.CityCode=City.CityID WHERE PostalCode.PostalCode=forms![Info Reporting]!InfoPostalCity; 

Open in new window

0
 
LVL 4

Expert Comment

by:tabish
ID: 33608810
What is the name of the combobox that select the POSTCODE (InfoPostalCode????) ? You should use that.

InfoPostalCity sounds like the combobox for city that populates when you select a postcode. Am I right?
0
 
LVL 4

Expert Comment

by:tabish
ID: 33608814
Sorry for the bad English
Correction:

InfoPostalCity sounds like the combobox for the city that is populated when you select a postcode. Am I right?
0
 

Author Comment

by:wlwebb
ID: 33608827
Actually I drag and dropped the "field" InfoPostalCity from the "Tables" onto my form.
0
 
LVL 4

Expert Comment

by:tabish
ID: 33608856
Please post your DB If possible for you. Remember, if it has private data you should not post it unless you fill it with dummy data or delete all the other tables except City, PostalCode and State Abbreviations.

See if I can fix it for you.

Cheers!
0
 

Author Comment

by:wlwebb
ID: 33608857
Oops, "IRS From Info" Table field should be InfoPostalCity instead of CustomerCity
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:wlwebb
ID: 33608924
Here it is.  Thanking you in advance.  Had to change some things first is why it took a little time
Info-Reporting.accdb
0
 
LVL 4

Expert Comment

by:tabish
ID: 33609037
Hi wlwebb,
sorry I got a bit busy will be back soon.
0
 

Author Comment

by:wlwebb
ID: 33609152
Thanks for taking a look
0
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 500 total points
ID: 33609815
wlwebb,

I am not sure if this is what you want, yet it might be of help to you.

You need to add after update event code in your postal code combo box. You may copy the code below:
Me.WinnersPostalCity = Me.WinnersPostalCode.Column(2)
Me.WinnersPostalState = Me.WinnersPostalCode.Column(3)

Then, you need to change the row source of postal city into:
SELECT City.CityID, City.CityName FROM City INNER JOIN PostalCode ON PostalCode.CityCode=City.CityID;

Sincerely,

Ed
0
 

Author Comment

by:wlwebb
ID: 33610084
Works sort of.....

At least I'm getting info in the City & State boxes but the info isn't correct.

I made the changes you suggested.. However, I selected a zip code of 43001 and the Form populated the City and State fields {This is a decided improvement on where I was 4 1/2 hours ago and more than I've been able to get on my own trying all sort of combination(s) of code}
 
However, the City Name and State it is selecting are incorrect.  It is returning "Ashville" as the City for 43001 when it should be "Alexandria".   What it is doing is returning the info for CityCode 35 instead of CityCode 9.  The code 35 is the State Code.

0
 

Author Comment

by:wlwebb
ID: 33610098
Figured it out.  

Me.WinnersPostalCity = Me.WinnersPostalCode.Column(2)
Me.WinnersPostalState = Me.WinnersPostalCode.Column(3)


Should have been
Me.WinnersPostalCity = Me.WinnersPostalCode.Column(3)
Me.WinnersPostalState = Me.WinnersPostalCode.Column(2)


THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0
 

Author Comment

by:wlwebb
ID: 33610221
MINDSUPERB
Have a problem.  City works but State doesn't

Changed the Row Source on  WinnersPostalState to the following code:

City name works fine, but the State doesn't work.   Any suggestions?

SELECT State Abbreviations.StateID, State Abbreviations.PostalState FROM State Abbreviations INNER JOIN PostalCode ON PostalCode.StateID=State Abbreviations.StateID;

Open in new window

0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33610312
wlwebb,

You may try to choose this:
SELECT [State Abbreviations].StateID, [State Abbreviations].State, [State Abbreviations].PostalState FROM [State Abbreviations];

Ed
0
 

Author Comment

by:wlwebb
ID: 33610339
Worked like a charm.  Bless you!!!!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now