Auto Enter County, State & Zip after entering City

Anonymous

I'm trying to make data entry easier. We have a relatively small client base (3 counties) and I'm wondering if I create a table with all of the cities in the counties grouped together with states and zips if Access will be able to AutoEnter the County, State and Zip after the City is entered?

(I've only got beginner Access 2010 under my belt....hoping they will send me to level 2 at the very least...)

THANKS so very much for your time!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

51 answers

Anonymous

2010-12-29T20:57:53+00:00

I'm searching for an example of the mutiple tables I've created to make sure I have things done correctly before I try to go any further and all I'm finding is samples/examples of addresses the way I had them before..........please tell me I didn't separate information unnecessarily
Was this answer helpful?

0 comments No comments
ScottGem 68,840 Reputation points Volunteer Moderator

2010-12-29T21:10:48+00:00

sadly, i did, and i kept getting errors on the subforms...something about data type not matching.

it's about time to get someone standing over my shoulder :(

Ok, when you compare or translate data, DataTypes have to match. When you create a field in a table you have to choose the data type. You also have to be aware that some datatypes have subtypes. For example a Number datatype may be Long Integer, Integer, Double, Single etc. Also some datatypes have underlying types. For example a Yes/No field is really an Integer and a Autonumber is really a Long Integer.

So when comparing types you may have to delimit them. If you are comparing a text datatype you have to wrap the text in single quotes:

Ex: "[Address] = '" & Me.txtAddress & "'"

Dates need to be wrapped in the # sign

Ex: "[DOB] = #" & Me.txtDOB & "#"

And numbers need no wrapper

Ex: "[Quantity] = " & Me.txtQty

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries
Was this answer helpful?

0 comments No comments
Anonymous

2011-02-08T16:39:39+00:00

I managed to figure out that a Macro will get the State, Zip & County to Auto-Fill after the City is entered. I'm still working on getting it to differentiate between different Cities....

...back to the books...
Was this answer helpful?

0 comments No comments
Anonymous

2011-02-08T16:50:06+00:00

When multiple cities have the same name, you'll need more information, and a unique key. Instead of a single column for city, you'll need to display a state column, and have either a key that shows or is hidden to actually do the lookup.

Arvin Meyer, MCP, MVP

http://www.datastrat.com

http://www.accessmvp.com

http://www.mvps.org/access

Co-author: "Access Solutions", published by Wiley

<REACH.Hawley> wrote in message news:*** Email address is removed for privacy ***...

I managed to figure out that a Macro will get the State, Zip & County to Auto-Fill after the City is entered. I'm still working on getting it to differentiate between different Cities....

...back to the books...

Arvin Meyer MCP, MVP MS-Access http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access Co-author: "Access Solutions", published by Wiley
Was this answer helpful?

0 comments No comments
Anonymous

2011-02-08T17:29:59+00:00

Arvin,

I am intrigued by this hidden key...

I am also confused by the "When multiple cities have the same name," ---our service area is very small. (I have a table with the 27 cities, including the state, and zip and a second table for the county)

do share more :) I'm learning more every day!

Thanks!
Was this answer helpful?

0 comments No comments