A rookie's first effort - I'm so proud :o)

Good question, I don't really know. As most people, particularly
residentail, will not likely know the +4 for their code, I don't see that
working.

I like your idea. It would be a bit advanced for a newbie, but
conceptually, you could test to see how many city names have the zip code,
then offer the user the choices.
--
Dave Hargis, Microsoft Access MVP


"Hunter57" wrote:

Hi,

Wow, thanks for the info, I never heard that before. Are the last four
digits in the extended zipcode the same or are they different?

Hunter57

"Klatuu" wrote:

That will not always work. There are situations (I live in one) where one
zip code has more than one town. I don't know about where you are, but here,
the Post Offce gets snarly if you use the incorrect town name even when the
zip code is correct.
--
Dave Hargis, Microsoft Access MVP


"Hunter57" wrote:

Hi James,

I am not an expert, but I think you did a great job for a newbie!

Here is a tip you might want to use as your database grows:

If you forsee that you will have many records, there are some things you can
do to help your database run faster by not storing the same data many times.

For example, think of how many times the same state, zipcode, or country
will be stored in your DB. To avoid needless repetitive data storage you can
create a PostalCode table that holds the ZipCodes, Cities, States (if you
plan to do mailings you might want to use the two letter state abbreviation),
and countries.

I use the Postal Code as the Primary Key Field since the Postal Code numbers
are not likely to change. (For almost everything else I use an ID Autonumber
field as the Primary Key)

In your Main table keep the Postal_Code field. Create a relationship
between the two tables based on the Postal Code.

You can use a query as your form Record Source or you can use the following
method to add the information to your Form:

Change your Postal Code text box to a combo box. Then you can use the combo
box on your form to add the Postal Code and the Citiy, State, and Country
information automatically.

You will have to create a query for your Postal Code combo box that includes
the City, State, and Country information. Use that query as the Row Source
for the combo box. Open your properties sheet for your combo box. Click the
"Format" tab. Set the following properties:

Column Count: 4 --Be absolutly sure that you have the total
number of columns here.
Column Heads: Either Yes or No
Column Widths: 0.7";1.5";1";1.5" --Change these to however it suits you
List Rows: 25
List Width: 5" --Be sure to set this wide enough if you want to see the
data when you click on the combo box.

After this, you will need to make your City, State and Country text boxes
Unbound. Then add to your Record Source for the City, for example, the
following expression: =cboPostal_Code.Column(1)

The Column is the Column in your Query. Access assigns the first column in
a Query the number 0, the second column number 1, and so on. You will use
the column number for "City", whatever that may be. Do the same with the
State and Country text boxes.

You will need to add the following code to the Forms On Current Event so the
Combo Box and text boxes will display the information. Your code should look
like this:

Private Sub Form_Current()

Me.cboPostal_Code.Requery

End Sub

Using this method you will store the City, State, and Country only once.
This can save a lot of memory and help your database to run faster.

Once you learn how to do this, you can use this method for your Membership
Status, Type, etc.

Of course, you can use a Query as the Form's record source which includes
the Postal Code Table Fields. Most of the time that will work very well.
However, sometimes when you use a query for the Form Record Source the query
will not allow you to add data to your tables. This can happen when your
Query uses data from more than one table. If you prefer to use queries, and
have that problem, you may be able to use the method I described to solve it.

I hope that is not too much info at one time. You can alway try it later if
you prefer.

Hunter57
http://accesstips.wordpress.com/
http://www.churchmanagesoftware.com/

"James Ivey" wrote:

Its really no big shakes... its just a form

But I'm proud of it ))


"Kirstie Adam" (nospam) wrote in message
...
Hi James, i am no way an expert, still a beginner myself, but i still was
interested to have a look! (i am overly excited by other people's
databases. my friends worry!)

For some reason the link doesn't work for me though, can i get to it by
clicking any links on the website - i can get to that?

Kirstie

"James Ivey" wrote in message
...
http://www.iampeth.com/stuff/IAMPETH.mdb

Total Access Newbie here.

I would be very appreciative if a few experts would take a
look at my first efforts.

Its a membership database for an organization with approximately
450 members.

Its only got 5 records in it... filesize is 316 Kb

I'm wide open to critique and suggestions.

Thanks in advance!

James Ivey

Of course you are right about that. Oh, well. For once I thought I would not
have to use the AutoNumbers.

James, here is how to take care of that problem. We will just have to add
an PostalCodeID field as the Primary key with the data type as Autonumber to
the PostalCode Table.

When you create the query for the combo box make sure that the ID field is
the first column. In your combo box's Properties sheet, click the Format tab
and be sure that the Column Count number includes all of the query columns.

Here is a neat trick that works well: for the first number in the Column
Widths: set it to 0" like this: 0";0.7";1.5";1";1.5"
This way your Postal_Code combo box will display the zipcode instead of the
autonumber (which will be hidden).

If you have a zipcode that includes more than one city, you can add the same
zipcode with the different city to your table. That way you can choose the
correct city when you are selecting the zip code.

Thanks Klatuu,
Hunter57

"Klatuu" wrote:

Good question, I don't really know. As most people, particularly
residentail, will not likely know the +4 for their code, I don't see that
working.

I like your idea. It would be a bit advanced for a newbie, but
conceptually, you could test to see how many city names have the zip code,
then offer the user the choices.
--
Dave Hargis, Microsoft Access MVP


"Hunter57" wrote:

Hi,

Wow, thanks for the info, I never heard that before. Are the last four
digits in the extended zipcode the same or are they different?

Hunter57

"Klatuu" wrote:

That will not always work. There are situations (I live in one) where one
zip code has more than one town. I don't know about where you are, but here,
the Post Offce gets snarly if you use the incorrect town name even when the
zip code is correct.
--
Dave Hargis, Microsoft Access MVP


"Hunter57" wrote:

Hi James,

I am not an expert, but I think you did a great job for a newbie!

Here is a tip you might want to use as your database grows:

If you forsee that you will have many records, there are some things you can
do to help your database run faster by not storing the same data many times.

For example, think of how many times the same state, zipcode, or country
will be stored in your DB. To avoid needless repetitive data storage you can
create a PostalCode table that holds the ZipCodes, Cities, States (if you
plan to do mailings you might want to use the two letter state abbreviation),
and countries.

I use the Postal Code as the Primary Key Field since the Postal Code numbers
are not likely to change. (For almost everything else I use an ID Autonumber
field as the Primary Key)

In your Main table keep the Postal_Code field. Create a relationship
between the two tables based on the Postal Code.

You can use a query as your form Record Source or you can use the following
method to add the information to your Form:

Change your Postal Code text box to a combo box. Then you can use the combo
box on your form to add the Postal Code and the Citiy, State, and Country
information automatically.

You will have to create a query for your Postal Code combo box that includes
the City, State, and Country information. Use that query as the Row Source
for the combo box. Open your properties sheet for your combo box. Click the
"Format" tab. Set the following properties:

Column Count: 4 --Be absolutly sure that you have the total
number of columns here.
Column Heads: Either Yes or No
Column Widths: 0.7";1.5";1";1.5" --Change these to however it suits you
List Rows: 25
List Width: 5" --Be sure to set this wide enough if you want to see the
data when you click on the combo box.

After this, you will need to make your City, State and Country text boxes
Unbound. Then add to your Record Source for the City, for example, the
following expression: =cboPostal_Code.Column(1)

The Column is the Column in your Query. Access assigns the first column in
a Query the number 0, the second column number 1, and so on. You will use
the column number for "City", whatever that may be. Do the same with the
State and Country text boxes.

You will need to add the following code to the Forms On Current Event so the
Combo Box and text boxes will display the information. Your code should look
like this:

Private Sub Form_Current()

Me.cboPostal_Code.Requery

End Sub

Using this method you will store the City, State, and Country only once.
This can save a lot of memory and help your database to run faster.

Once you learn how to do this, you can use this method for your Membership
Status, Type, etc.

Of course, you can use a Query as the Form's record source which includes
the Postal Code Table Fields. Most of the time that will work very well.
However, sometimes when you use a query for the Form Record Source the query
will not allow you to add data to your tables. This can happen when your
Query uses data from more than one table. If you prefer to use queries, and
have that problem, you may be able to use the method I described to solve it.

I hope that is not too much info at one time. You can alway try it later if
you prefer.

Hunter57
http://accesstips.wordpress.com/
http://www.churchmanagesoftware.com/

"James Ivey" wrote:

Its really no big shakes... its just a form

But I'm proud of it ))


"Kirstie Adam" (nospam) wrote in message
...
Hi James, i am no way an expert, still a beginner myself, but i still was
interested to have a look! (i am overly excited by other people's
databases. my friends worry!)

For some reason the link doesn't work for me though, can i get to it by
clicking any links on the website - i can get to that?

Kirstie

"James Ivey" wrote in message
...
http://www.iampeth.com/stuff/IAMPETH.mdb

Total Access Newbie here.

I would be very appreciative if a few experts would take a
look at my first efforts.

Its a membership database for an organization with approximately
450 members.

Its only got 5 records in it... filesize is 316 Kb

I'm wide open to critique and suggestions.

Thanks in advance!

James Ivey